If the mysql-replication is broken, there are two ways to fix the problem:
- skip the error
- 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: YesSlave_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? 😉
Ein kleiner Fehler ist drin: Es muss beim Wiederherstellen des Dumps natürlich “mysql -uUSER -p PASSWORD < dump.sql" heißen, !
Hi,
schöner Eintrag, aber:
“ehe der Dump durch
mysql -uUSER -p PASSWORD > dump.sql
importiert werden kann.”
müsste doch sicher
mysql -uUSER -p PASSWORD < dump.sql
heißen, oder?
LG.
Genau das habe ich gerade auch geschrieben. Sollte hier wirklich geändert werden!
could you please specify which steps are done on master server & which on slave server – thank you
This post describes the restart of a master-master, and not a master-slave setup.
To restart a master-slave-replication see http://blog.schaal-24.de/?p=671&lang=en
Pingback: mysql-Replikation mit Nagios überwachen | florian @it