Fixing MySQL master-slave replication upon query error

When you run a MySQL master/slave replication, it can happen that the Slave I/O thread is still running but the Slave SQL thread is reporting errors. It could be something like this.

mysql> show slave status \G;
*************************** 1. row ***************************
...
Slave_IO_Running: Yes
Slave_SQL_Running: No
...
Last_Errno: 1050
Last_Error: Error 'Table 'tbl_name' already exists' on query. Default database: 'db_name'. Query: 'CREATE TABLE tbl_name ...'
...

If the master had a query executed that could not be run on the slave, you’ll encounter this situation. If you are certain that this query can be skipped safely on the slave server (because the query doesn’t violate the data integrity), you can skip this and have the Slave SQL thread resume work from the next query.

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.01 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 global “SQL_SLAVE_SKIP_COUNTER” determines how many queries to skip. Doing a “START SLAVE” will make the SQL thread startup again and process the queries in queue.

Or as a one-liner.

mysql> STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; 

Afterwards, you can check the status of the slave synchronization.

mysql> show slave status \G;
*************************** 1. row ***************************
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...

If both the Slave I/O and SQL thread are reporting “Yes”, everything is back in sync (according to the binlogs on master/slave).