Restarting mysql master-master replication
These are the steps for restarting master-master replication where replication has stopped due to a slave IO issue. You can tell if this solution applies to you by typing “show slave status\G;” at the mysql prompt. If “Slave_IO_Running: No”, on one or both databases, try this:
on db1:
mysql
STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
on db2:
mysql
STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
on db1:
# get the values for log_file & log_pos from db2's master status in the previous step
CHANGE MASTER TO MASTER_LOG_FILE ='mysql-bin.000...',MASTER_LOG_POS=123456;
SHOW MASTER STATUS;
CHANGE MASTER TO MASTER_LOG_FILE ='mysql-bin.000...',MASTER_LOG_POS=123456;
SHOW MASTER STATUS;
on db2:
# get the values for log_file & log_pos from db1's master status in the previous step
CHANGE MASTER TO MASTER_LOG_FILE ='mysql-bin.000...',MASTER_LOG_POS=123456;
START SLAVE;
UNLOCK TABLES;
CHANGE MASTER TO MASTER_LOG_FILE ='mysql-bin.000...',MASTER_LOG_POS=123456;
START SLAVE;
UNLOCK TABLES;
on db1:
START SLAVE;
UNLOCK TABLES;
UNLOCK TABLES;
I’m posted this mainly for personal reference. Keep in mind I’m a sysadmin by necessity, not preference. Attempt this at your own risk. Don’t expect to see many posts like this from me.
