You are here

Resyncing table on MySQL Slave

MySQL Performance Blog - Mon, 30/06/2008 - 4:41am

Sometimes MySQL Replication may run out of sync - because of its own buts or operational limitations or because of application mistake, such as writing to the slave when you should be only writing to the master. In any case you need slave to be synced with Master.

To discover the difference between Master and Slave you can use excellent Maatkit tool though if you you just need to resync small single table it may be easy to do it other way:

Assuming your table schema does not change you can do something like this on the master:

LOCK TABLE tbl WRITE;
SELECT * FROM table INTO OUTFILE '/tmp/tbl.txt';
DELETE FROM tbl;
LOAD DATA INFILE 'tmp/tbl.txt' INTO TABLE tbl;
UNLOCK TABLES;

This will dump master's table content to the file clean the table on master and slave and refresh it on both of them.

This obviously makes table inaccessible on master for duration of operation but for small tables it can take just few seconds.

This trick is of course quite dirty, especially if you're dealing with sensitive data - what if you run out of space or SELECT INFO OUTFILE fails for other reason and you proceed with deletion you may kiss your data goodbye.

Alternative would be not to drop the table but rather raname them. Unfortunately you can't rename locked tables (ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction) - this is also I used DELETE instead of TRUNCATE to clean out the table.

In the applications which are error tolerant (like handle errors properly) you may rename table instead of locking - for many applications this works better anyway as having table locked causes connections to pile up and either MySQL runs out of connections or Apache out of children allowed to process request;

So you can do following instead:

PLAIN TEXT SQL:
  1. mysql> RENAME TABLE rep TO rep_maint;
  2. Query OK, 0 rows affected (0.01 sec)
  3.  
  4. mysql> SELECT * FROM rep_maint INTO OUTFILE '/tmp/rep.txt';
  5. Query OK, 0 rows affected (0.01 sec)
  6.  
  7. mysql> CREATE TABLE rep_new LIKE rep_maint;
  8. Query OK, 0 rows affected (0.00 sec)
  9.  
  10. mysql> LOAD DATA INFILE '/tmp/rep.txt' INTO TABLE rep_new;
  11. Query OK, 0 rows affected (0.01 sec)
  12. Records: 0  Deleted: 0  Skipped: 0  Warnings: 0
  13.  
  14. mysql> RENAME TABLE rep_maint TO rep_old, rep_new TO rep;
  15. Query OK, 0 rows affected (0.00 sec)

This way you never actually delete any data and besides same data loaded to "rep" table you will get the copy of old table in "rep_old" - this can be very handy if you would like to later investigate why data got out of sync and may be do some manual touch ups after you resync the data.

Note - this process works well for small tables when you do not want to shut down the master and slave. If you need quick resync of large amount of data it is much better to use LVM snapshots or similar techniques - these are much faster.

Entry posted by peter | 8 comments

Add to: delicious | digg | reddit | netscape | Google Bookmarks