MySQL High Availability- P6: A lot of research has been done on replication, but most of the resulting concepts are never put into production. In contrast, MySQL replication is widely deployed but has never been adequately explained. This book changes that. Things are explained here that were previously limited to people willing to read a lot of source code and spend a lot of time debugging it in production, including a few late-night sessions. | Master Common Master Figure 6-6. True multisource and a diamond configuration data from different sources for reporting purposes. In these cases you can separate data naturally by storing the writes from each master in its own database table or partition. There is no risk of conflict so it should be possible to use multisource replication. Figure 6-7 shows a slave that replicates from three masters in a round-robin fashion running a client dedicated to handling the switches between the masters. The process for round-robin multisource replication is 1. Set the slave up to replicate from one master. We ll call this the current master. 2. Let the slave replicate for a fixed period of time. The slave will the read changes from the current master and apply them while the client responsible for handling the switching just sleeps. 3. Stop the I O thread of the slave using STOP SLAVE IO_THREAD. 4. Wait until the relay log is empty. 5. Stop the SQL thread using STOP SLAVE SQL_THREAD. CHANGE MASTER requires that you stop both threads. 6. Save the slave position for the current master by saving the values of the Exec_Master_Log_Pos and Relay_Master_Log_File columns from the SHOW SLAVE STATUS output. 7. Change the slave to replicate from the next master in sequence by taking the previously saved positions and using CHANGE MASTER to set up replication. Multisource Replication 227 Please purchase PDF Split-Merge on to remove this watermark. Figure 6-7. Round-robin multisource replication using a client to switch 8. Restart the slave threads using START SLAVE. 9. Repeat the sequence starting from step 2. Note that in steps 3 through 5 we stop first the I O thread and then the SQL thread. The reason for doing this and not just stopping replication on the slave is that the SQL thread can be lagging behind and usually is so if we just stop both threads there will be a bunch of outstanding events in the relay log that will just be thrown away. If you are more .
