Repairing MySQL multi-master replication

We have 2 servers, server1 is the known good and server2 is the one that is out of synchronisation.

Make sure nothing is actively querying server2. You can do this by turning on full query logging and tailing the log file for any queries.

mysql> SHOW GLOBAL VARIABLES like 'general_log%'  ;
+------------------+-------------------------------+
| Variable_name    | Value                         |
+------------------+-------------------------------+
| general_log      | OFF                           |
| general_log_file | /var/lib/mysql/server2.log |
+------------------+-------------------------------+
2 rows in set (0.00 sec)

mysql> SET GLOBAL general_log = 'ON';
[root@server2 ~]# tail -f /var/lib/mysql/server2.log

Stop the slave replication threads on both servers:

mysql> STOP SLAVE;
mysql> RESET SLAVE;

Leave the slave threads stopped, if you let them run then you may lose your good copy.

Make a mysql dump of you database on server1. I reset the master as well because that cleans up any replication logs that may be laying around, you may not want to do this if you use them for anything other than replication.

mysql> RESET MASTER;
[root@server1 ~]# mysqldump -p  --all-databases --master-data > /var/lib/mysql/dbdump-`hostname -s`-`date +%Y%m%dT%H%M%S`.sql

Copy the dump file to server2:

rsync -a --progress /var/lib/mysql/dbdump-server1-20110616T153454.sql server2:/var/lib/mysql/

On server2 import the database dump and start the slave.

[root@server2 ~]#mysql -p  <  /var/lib/mysql/dbdump-server1-20110616T153454.sql
mysql> START SLAVE;
mysql> SHOW SLAVE STATUSG

Reset the master on server2 so that we don’t replicate any of the import and then get what point the master is up to.

mysql> RESET MASTER;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 |              |                  |
+------------------+----------+--------------+------------------+

Change server1 to be at the same point in replication.

mysql> CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 106;
mysql> SHOW SLAVE STATUS\G
mysql> START SLAVE;

Check that Both are now replicating correctly.

Make sure you have turned off logging on server 2 or you will fill up the disks quite rapidly.

mysql> SET GLOBAL general_log = 'ON';