4.10.2 Replication Implementation Overview

MySQL replication is based on the server keeping track of all changes to your database (updates, deletes, etc) in the binary log (see section 4.9.4 The Binary Log) and the slave server(s) reading the saved queries from the master server's binary log so that the slave can execute the same queries on its copy of the data.

It is very important to realise that the binary log is simply a record starting from a fixed point in time (the moment you enable binary logging). Any slaves which you set up will need copies of the data from your master as it existed the moment that you enabled binary logging on the master. If you start your slaves with data that doesn't agree with what was on the master when the binary log was started, your slaves may fail.

Please see the following table for an indication of master-slave compatibility between different versions. With regard to version 4.0, we recommend using same version on both sides.

Note: MySQL Version 4.0.2 is not recommended for replication. As a general rule, it's always recommended to use recent MySQL versions for replication.

Note that when you upgrade a master from MySQL 3.23 to MySQL 4.0 (or 4.1) you should not restart replication using old 3.23 binary logs, because this will unfortunately confuse the 4.0 slave. The upgrade can be safely done this way:

• Block all updates on the master (FLUSH TABLES WITH READ LOCK).
• Wait until all the slaves have caught up all changes from the master (use SHOW MASTER STATUS on the master, and SELECT MASTER_POS_WAIT() on the slaves). Then run SLAVE STOP and RESET SLAVE on the slaves.
• Shutdown MySQL on the master and slaves, upgrade the master and slaves to MySQL 4.0. Restart the master and slaves, and issue CHANGE MASTER TO commands on the slaves, unless the slaves already have the identification of the master in their `my.cnf' files.

Starting from 4.0.0, one can use LOAD DATA FROM MASTER to set up a slave. Be aware that LOAD DATA FROM MASTER currently works only if all the tables on the master are MyISAM type, and will acquire a global read lock, so no writes are possible while the tables are being transferred from the master. When we implement hot lock-free table backup (in MySQL 5.0), this global read lock will no longer be necessary.

Due to the above limitation, we recommend that at this point you use LOAD DATA FROM MASTER only if the dataset on the master is relatively small, or if a prolonged read lock on the master is acceptable. While the actual speed of LOAD DATA FROM MASTER may vary from system to system, a good rule for a rough estimate of how long it is going to take is 1 second per 1 MB of the datafile. You will get close to the estimate if both master and slave are equivalent to 700 MHz Pentium, are connected through 100 MBit/s network, and your index file is about half the size of your datafile. Of course, this is only a rough order of magnitude estimate.

Once a slave is properly configured and running, 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 every master-connect-retry seconds until it is able to reconnect and resume listening for updates.

Each slave keeps track of where it left off. The master server has no knowledge of how many slaves there are or which ones are up-to-date at any given time.

User Comments

For MySQL 3.23.xx, setting up replication can be a
pain because you need to generate an initial
(consistent) snapshot of your data to load onto
your slave(s). You also need to make sure the
binary log on the master reflects the exact time at
which you took the snapshot. mysqlsnapshot
automates the process:

http://litterbox.zawodny.com/~jzawodn/mysql/

The link above is wrong. The actual link to mysqlsnapshot is:
http://jeremy.zawodny.com/mysql/mysqlsnapshot/

Add your own comment.