MySQL – Slave Missing Data from Master and Manual Insertion Issues

MySQLmysql-5.6replication

My transaction logs became corrupted and I had to skip 50 insert records from master. I've exported the data and am planning to update the slaves manually, with the slave stopped. The auto-increment value on the slave stayed in sync with master to my surprise so I think I can just re-populate the missing rows. Are there any potential issues with this plan?

So for example on master I run:

SELECT count(*) FROM `users` WHERE userid between 20 and 69;

and I get back 50. When I run it on slave I get "Empty set". So I did:

SELECT * FROM `users` WHERE userid between 20 and 69;

Then constructed the resulting CSV from PHPMyAdmin into an insert query:

insert ignore into users (userid, name, passwordhash, active)
values
(....,...,...,...)

Prior to insert on the slaves I will run:

stop slave;

and then after

start slave;

I can't think of issues with this may cause but I don't have a testing replication environment so can't test this thoroughly.

Best Answer

My recommendations would be to invest some time in learning how to use the pt-table-checksum and pt-table-sync tools from the Percona toolkit. This takes much of the work out of your hands in a much more reliable way.

https://www.percona.com/downloads/percona-toolkit/LATEST/

Related Question