[+/-]
Replication is based on the master server keeping track of all
changes to its databases (updates, deletes, and so on) in its binary
log. The binary log serves as a written record of all events that
modify database structure or content (data) from the moment the
server was started. Typically, SELECT
statements are not recorded because they modify neither database
structure nor content.
Each slave that connects to the master requests a copy of the binary log. That is, it pulls the data from the master, rather than the master pushing the data to the slave. The slave also executes the events from the binary log that it receives. This has the effect of repeating the original 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 changes that were originally made on the master.
Because each slave is independent, the replaying of the changes from the master's binary log occurs independently on each slave that is connected to the master. In addition, because each slave receives a copy of the binary log only by requesting it from the master, the slave is able to read and update the copy of the database at its own pace and can start and stop the replication process at will without affecting the ability to update to the latest database status on either the master or slave side.
For more information on the specifics of the replication implementation, see Section 16.2.1, “Replication Implementation Details”.
Masters and slaves report their status in respect of the replication process regularly so that you can monitor them. See Section 7.5.6, “Examining Thread Information”, for descriptions of all replicated-related 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 relay log. See Section 16.2.2, “Replication Relay and Status Files”.
Database changes are filtered on the slave according to a set of rules that are applied according to the various configuration options and variables that control event evaluation. For details on how these rules are applied, see Section 16.2.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.