Fix MySQL Replication by Skipping The Problematic Query

MySQL replication can be a bit fragile.  Most of the time replication fails when the slave tries to run an SQL statement that causes an error.   I’ve most frequently seen this when the slave is missing some table that I forgot to import when setting replication up.   Usually it is a simple enough job to create the table, then start the slave SQL thread again.

However, sometimes replication breaks for no particular reason at all. This morning, I had a slave stop because an invalid query somehow got written to the binary log.  I’m not quite sure how that happened, but fortunately I found a way to just skip the bad query.

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

The ‘SQL_SLAVE_SKIP_COUNTER setting tells the slave SQL thread to skip that many queries when starting up. Note that you should really know what your database is doing and why it stopped before you just go running this command. It may cause your slave to get seriously out-of-sync with the master server.