Tested on Ubuntu 14 / MySQL 5.5
After a crash in a mysql master-master replication you can follow these steps.
1.- First identify the failed server, from now we’ll use this nomenclature:
Failed server: FS Working server: WS
2.- Block queries to the FS for example with iptables if this server receive write queries:
~ $ iptables -I INPUT -s-p tcp --dport 3306 -j DROP ~ $ iptables -I INPUT -s -p tcp --dport 3306 -j DROP ...
3.- Stop slave on both servers FS and WS:
mysql> STOP SLAVE;
4.- On FS make a dump with –master-data option to block tables and save master position and binary log:
~ $ mysqldump -h WS-IP -u root -pxxxx --master-data --single-transaction --flush-logs --routines --all-databases > ALL-DBs.sql
5.- And then import:
~ $ mysql -u root -pxxxx < ALL-DBs.sql
6.- Start slave:
mysql> START SLAVE;
7.- Verify the slave status:
mysql> SHOW SLAVE STATUS\G
8.- Lock tables:
mysql> FLUSH TABLES WITH READ LOCK;
9.- Get the master status:
mysql> SHOW MASTER STATUS;
10.- Now on the WS set the replication with the parameters obtained at above step:
mysql> CHANGE MASTER TO MASTER_HOST='FS-IP', MASTER_USER='replication-user', MASTER_PASSWORD='replication-password', MASTER_LOG_FILE='mysql-bin.xxxx', MASTER_LOG_POS=yyyy, MASTER_PORT=3306;
11.- Start slave to synchronize with the master:
mysql> START SLAVE;
12.- Verify the slave status:
mysql> SHOW SLAVE STATUS\G
13.- Unlock tables on FS;
mysql> UNLOCK TABLES;
14.- Replication should be working fine now, if not try again from the step 3.
15.- Finally delete firewall blocking if necessary:
~ $ iptables -D INPUT -s-p tcp --dport 3306 -j DROP ~ $ iptables -D INPUT -s -p tcp --dport 3306 -j DROP ...