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:

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.

12 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Scott

Depending on the storage engine, you may need to be careful with the auto-increment variable with the first method. Better to manually save & restore that value as well.

0
Suhaib

Why not use INSERT-SELECT query instead of dumping table & then reloading it again. I think INSERT-SELECT query works quite fast compare to this.

0
James Cohen

@Suhaib – The INSERT/SELECT would work on the master but (assuming you’re using statement based replication) would just reload the original (out of sync) data on the slave.

@Peter – any reason why you use DELETE in place of TRUNCATE in the original example?

0
ak47

James:
w/ truncate you would probably run into http://bugs.mysql.com/bug.php?id=20667 which is easily demonstrable.

0
Suhaib

James, so is the dumping & reloading from file the only way to make out-of-sync slave synchronized.

0
Peter Zaitsev

James,

Indeed TRUNCATE does not work with LOCKED table.

Suhaib,

Reloading the table is pretty much only way with logical level replication.

You can also use mysqldump to reload the table. What I described here just gives you a bit more control about data visibility.

0
Sheeri

Don’t forget that you can also use mysqlhotcopy if the table is MyISAM. This option is not given, but many folks still use MyISAM tables.

Also, you have not mentioned how one might determine that the slave is out of sync. There may be some data sync issues you discover through replication errors; or perhaps you use mk-table-sync or mk-table-checksum, or MySQL’s built-in CHECKSUM() function.

At any rate, if it’s a small table, mk-table-sync is actually the best tool anyway, as with one command it will checksum the table, determine differences, and can either write statements to a file to fix the slave, or actually enact the differences. This takes a longer time and will (read) lock the master for a longer time if the table is bigger.

Still, this is a good reference for folks who cannot have ma’atkit on their system for whatever reason.

0
Peter Zaitsev

Sheeri,

You can use MySQLHotCopy however this makes the process much more elaborate if you have traffic flowing as you need to ensure the table is “synced” at same point in time in master and slave which can be hard to guaranty for complex replication topologies.

The mk-table-sync is nice tool as I mentioned though it is not always installed. If you just want to do things once using Stock MySQL tools that is what I mainly wrote about.

Thanks for feedback 🙂

0
rs232

Is there any ways to create a script with this?
I would just like to have a shell script ready to go in case this annoying out of sync is manifesting itself again.

Thanks!

0
honeybee

question:
Does mk-table-sync locks master table while sync slave tables against master?
what about mk-table-checksum, does that lock master table too?

0
Mark White

Firstly, missing slash on LOAD DATA INFILE ‘tmp/tbl.txt’ in the first approach

Secondly, beware of mismatched table and DB charsets. I’ve just done this on a WordPress blog and the DB was set as char set latin1, with the TEXT tables set as utf8. End result was a load of mangled unicode – – and suchlike.

The fix to this is:

alter table wp_posts change post_content post_content LONGTEXT CHARACTER SET latin1;
alter table wp_posts change post_content post_content LONGBLOB;
alter table wp_posts change post_content post_content LONGTEXT CHARACTER SET utf8;

0
Alex

When executing “load data infile” with big tables there may appear errors like “Error reading packet from server: Got packet bigger than ‘max_allowed_packet’ bytes” on the slave and interrupt the replication. This can be fixed by raising max_allowed_packet size temporarily.

0