Fixing MySQL master-slave replication upon query error

Mattias Geniar, Saturday, November 19, 2011 - last modified: Tuesday, November 18, 2014

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).



Hi! My name is Mattias Geniar. I'm a Support Manager at Nucleus Hosting in Belgium, a general web geek, public speaker and podcaster. Currently working on DNS Spy. Follow me on Twitter as @mattiasgeniar.

I respect your privacy and you won't get spam. Ever.
Just a weekly newsletter about Linux and open source.

SysCast podcast

In the SysCast podcast I talk about Linux & open source projects, interview sysadmins or developers and discuss web-related technologies. A show by and for geeks!

cron.weekly newsletter

A weekly newsletter - delivered every Sunday - for Linux sysadmins and open source users. It helps keeps you informed about open source projects, Linux guides & tutorials and the latest news.

Share this post

Did you like this post? Will you help me share it on social media? Thanks!

Comments

eRadical Friday, November 25, 2016 at 12:36 (permalink)

An extra note:
If you find yourself doing this too often [ that is more than 1 time :) ] you might wanna check pt-table-checksum [https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html] to verify that the data is still ok on the slaves!

Reply


Leave a Reply

Your email address will not be published. Required fields are marked *