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.
Hi Josh,
I am Mr Hee, would like to ask you some question.
my db1 and db2 are working as master and master and both are collecting user logs.
the logs is insert db1 and db1 as record.
id flush tables with read lock statement is apply on both db1 and db2.
will some user logs will be missing since the not insert ,select, update allowed.
Please advise.
Rgds
KH
This is exactly What I was looking for from hours.
Thanks allot