Before you begin, identify the “good” server. This will be the most up-to-date master server.
Now, follow the steps below.
Good Server:
Note: Tables will be locked when running this command.
|
1 |
mysqldump --add-drop-table --master-data --quick -u root -p --all-databases > dump.sql |
Copy this file (found in the MySQL bin directory) to the same directory on the bad server.
Bad Server:
|
1 |
stop slave; |
|
1 |
mysql -u root -p < dump.sql |
Check that both the Slave_IO_Running and Slave_SQL_Running processes are running.
|
1 |
show slave status\G |
They should automatically restart after the dump.sql file is imported. If this does not happen then run the following command.
|
1 |
start slave; |
Flush and lock tables… and then check the master status.
|
1 2 |
flush tables with read lock; show master status; |
Make a note of the File and Position values.
Good Server:
Replace the master_log_file and master_log_pos values with those recorded in the previous step and then run these commands.
|
1 2 3 4 |
stop slave; change master to master_log_file = 'mysql-bin.000437', master_log_pos = 497028273; start slave; show slave status\G |
Check the status of the slave to ensure that replication is working.
Bad Server:
|
1 |
unlock tables; |
The Master-Master replication has now been restarted!
To gain a general overview of the process, I found this article useful.
My method focuses on fixing Master-Master replication for an entire server (instead of just one database) running within a Windows environment.
Depending on how often replication breaks, I may write a quick PowerShell script to automate things a little.