[CentOS] How To Repair MySQL Replication

Created: August 27, 2018 13:29 -
Last Updated: August 29, 2018 12:08

How To Repair MySQL Replication

If you have set up MySQL replication, you probably know this problem: sometimes there are invalid MySQL queries which cause the replication to not work anymore. In this short guide I explain how you can repair the replication on the MySQL slave without the need to set it up from scratch again.

Identify The Problem

To find out whether replication is/is not working and what has caused to stop it, you can take a look at the logs. On Debian, for example, MySQL logs to /var/log/syslog:

Fixing the problem is actually quite easy. We tell the slave to simply skip the invalid SQL query:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

This tells the slave to skip one query (which is the invalid one that caused the replication to stop). If you'd like to skip two queries, you'd use SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2; instead and so on.

Now we can start the slave again

START SLAVE;

Now check if replication is working again:

SHOW SLAVE STATUS \G

The output should look something like shit if this has solved the problem