Friday, September 7, 2012

Synchronizing MySQL Replication

Earlier I posted an article on how to setup MySQL Master-Master Replication. I failed to mention how to synchronize the servers if they got screwed up. Why would a server get out of sync? Honestly, it's not too hard. If a server reboots in the middle of a transaction or there's a network interruption then it can get out of sync. MySQL does a better job now than before for handling these types of problems but it's better to know how to deal with it before the issue presents itself.

Resync'ing a server pretty much deals with telling each server where to start reading again. Since your data is being read from Server A's bin log and copied into Server B's relay log you just need to tell Server B what file and position to start reading again. And vice-versa for the other server in a Master-Master configuration.

Let's get started...

In the MySQL command line of each server stop the slave.

mysql > slave stop;

Then request the master status.

mysql > show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | netreports   | manual,mysql     |
+------------------+----------+--------------+------------------+

This will give you the information necessary to manually provide the other server of the position of where to start reading again in the bin-log.

On each server enter the command below of each server.

mysql > change master to
master_log_file='mysql-bin.000003',
master_log_pos=73;

Once you've done this on both servers, start the slave threads on each.

mysql > slave start;

Replication should now be working!

You can double check by running the following command on both servers.

mysql > show slave status;

Both the "slave_io_running" and "slave_sql_running" should be YES. If not, you'll need to reset replication.

Resetting replication is pretty simple. Shut down both MySQL server services. Delete the relay logs (relay.log), which causes the server to re-read from the master. Then synchronize the servers again (as described above).

No comments:

Post a Comment