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).
A note for anyone landing here on a modern MySQL (8.0+): the commands above still work, but they’re now aliases. MySQL renamed the terminology, so STOP REPLICA, START REPLICA, SHOW REPLICA STATUS and SET GLOBAL sql_replica_skip_counter are the current spellings.
More importantly: SQL_SLAVE_SKIP_COUNTER does not work when you’re running GTID-based replication (gtid_mode=ON), which is the default on most modern setups. There, you skip the offending transaction by injecting an empty one with the same GTID instead.
mysql> SET GTID_NEXT='aaaa-bbbb-cccc-dddd:N';
mysql> BEGIN; COMMIT;
mysql> SET GTID_NEXT='AUTOMATIC';
mysql> START REPLICA;
Use the GTID of the failing transaction (you’ll find it in SHOW REPLICA STATUS). Once the replica sees that GTID has “already been applied”, it skips it and carries on.