[+/-]
The basic mechanics of replication is based on the master server
keeping track of all changes to your databases (updates, deletes,
and so on) in its binary logs. The binary log serves as a written
record of each to the database from the moment the database was
started. The binary log contains records of all the statements which
edit or modify either the database structure or the data that the
structure contains. Typically SELECT
statements are not recorded, as they do not modify the database data
or structure.
Each slave that connects to the master receives a copy of the binary log, and executes the events within the binary log. This has the effect of repeating the original statements and changes just as they were made on the master. Tables are created or their structure modified, and data is inserted, deleted and updated according to the statements that were originally executed on the master.
Because each slave is independent, the replaying of the statements in the masters binary log can occur on each slave that is connected to the master. In addition, because each slave only receives a copy of the binary log by requesting it from the master (it pulls the data from the master, rather than the master pushing the data to the slave), the slave is able to read and update the copy of the database at its own pace and rate and can start and stop the replication process at will without affecting the master or the slaves ability to update to the latest database status.
For more information on the specifics of the replication implementation, see Section 16.4.1, “Replication Implementation Details”.
Slaves and masters report their status in respect of the replication process regularly so that you can monitor the situation. For information on slave states, see Section 7.5.6.5, “Replication Slave I/O Thread States”, and Section 7.5.6.6, “Replication Slave SQL Thread States”. For master states, see Section 7.5.6.4, “Replication Master Thread States”.
The master binary log is written to a local relay log on the slave before it is processed. The slave also records information about the current position with the master's binary log and the local relayed log. See Section 16.4.2, “Replication Relay and Status Files”.
Databases and tables are updated on the slave according to a set of rules that are applied according to the various configuration options and variables that control statement evaluation. For details on how these rules are applied, see Section 16.4.3, “How Servers Evaluate Replication Filtering Rules”.
User Comments
When setting up replication from one database to another on the same machine I *strongly* recommend compiling MySQL once as the master server and once as the slave server, both with their separate 'localstatedir' and 'port' given in the "configure" command. Then give each its own my.cnf in its respective 'localstatedir'. Finally, make sure either the master's 'bin' is in your PATH if your want to work with master or the slave's 'bin' is in your path if you want to connect to the slave. Otherwise you *will* make an error sooner or later regarding to which server you are connected.
And passing --port on the command line does not help: unexpectedly, it does NOT override the value found in my.cnf.
Good luck!
"After the slave has been set up with a copy of the master's data, it will simply connect to the master and wait for updates to process. If the master goes away or the slave loses connectivity with your master, it will keep trying to connect periodically until it is able to reconnect and resume listening for updates. The retry interval is controlled by the --master-connect-retry option. The default is 60 seconds."
Note the above is not the case in MySQL 4.0.22 -- documentation might need to be updated to clarify which version it is talking about. (FWIW, just had a master die, come back up, and 10 minutes later the slaves still thought they were connected to the first instance.)
For tables other than MyISAM, a work around is to create the table structure on the slave server with ENGINE=FEDERATED, pointing at the server table.
Once it is properly setup, you can alter the table to the engine of your choice, and a local copy will be made. (e.g. ALTER TABLE my_data ENGINE=InnoDB; )
This have to be done on a per table basis, though.
Add your own comment.