restart mysql master-master replication 6


If the mysql-replication is broken, there are two ways to fix the problem:

  1. skip the error
  2. rebuild the replication

In some cases you could easily skip error. If you for example execute the same INSERT on both server.

Skip the error

Login into mysql as root:

STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

START SLAVE;

SHOW SLAVE STATUS \G

If SHOW SLAVE STATUS \G shows no errors anymore, the replication is working again. Otherwise reproduce the steps or switch to rebuild. Also have a look at MySQL Skip Duplicate Replication Errors.

Rebuild

First stop the access for any application on the broken server and make sure, only the other server is used.

Create a dump on the working server and copy the dump to the otherone:

$ mysqldump --add-drop-table --master-data -uUSER -p PASSWORD --all-databases > dump.sql

On the broken server, you must stop the slave

STOP SLAVE;

QUIT;

before you can import the dump with

mysql -uUSER -p PASSWORD > dump.sql

use

SHOW SLAVE STATUS\G

to check, if the server is running. Check out

Slave_IO_Running: Yes

Slave_SQL_Running: Yes.

By now everything works for the working server -> broken server direction. But it must be fixed for the other direction, too.

We are already on the formerly broken server:

flush tables with read lock;

show master status;

*************************** 1. row ***************************

File: mysql-bin.000163

Position: 381821321

Binlog_Do_DB:

Binlog_Ignore_DB: mysql

1 row in set (0.05 sec)

On the other server we correct some values for the replication::

STOP SLAVE;

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000163', MASTER_LOG_POS=381821321;

START SLAVE;

Just remove the locks on the second server and your done:

UNLOCK TABLES;

Well… the replication is working again. But some monitoring is not the worst, isnt´t it? 😉


Leave a comment

Your email address will not be published. Required fields are marked *

6 thoughts on “restart mysql master-master replication