[轉載] MySQL repair Master Master replication 之步驟

另一個可用技巧是 , 在重建這樣關係的時候 , 在/etc/my.cnf 加上

bind_address=127.0.0.1

不讓其他設備連到此台資料庫 , 這樣就能確保資料唯一性

---------------------------------------------------------------------------------------------------------

Ref:

http://www.thegoldfish.org/2011/06/repairing-mysql-multi-master-replication/

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;