Linux Ask!

Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.

Jan 272011
 

How to repair a broken MySQL replication?

Answer:

Sometimes you might discovered the slave MySQL server is having problem to replicate data from the master, when running the "SHOW SLAVE STATUS" command, it show:

mysql> SHOW SLAVE STATUS \G
..
          Slave_IO_Running: Yes
          Slave_SQL_Running: No
..
          Last_Errno: 1146
          Last_Error: Error 'Table 'db2.table3' doesn't exist' on query. Default database: 'db1'. 
..

The above error message said the table "db2.table3" does not exist in the slave DB. In order to fix this error, we just simply ignore this error and resume the replication. To do so:

1. Stop the slave from replication
mysql> STOP SLAVE;

2. Skip the error
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

3. Resume the replication
mysql> STARTSLAVE;

You will now check again the status using "SHOW SLAVE STATUS", and you will discover "Slave_SQL_Running: Yes" this time. Repeat the above steps if needed.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>