Restoring a MySQL replication slave (linux)
If you find the slave you are using is out of sync too much to skip and ignore you really need to do a rebuild. Sometimes this isn’t possible as the database is a live system and cannot be stopped.
Best to open two windows as the next couple of steps need to be done quickly.
On the master server login to mysql and type; show master status;
Make a note of the file and position (masterserver-bin.00000001, 123321123)
Stop the mysql service on the slave server, service mysqld stop
Now you need to copy the live database (MYI, etc.) files from the master server to the slave:
cd /var/lib/mysql/databasename
scp -pr * 192.168.0.1:/var/lib/mysql/databasename/
(p is to copy permissions and r is to copy recursive just in case)
When the copy finishes restart the service on the slave; service mysqld restart
stop slave;
change master to master_log_file=’servername-bin.0000001′, master_log_pos=101010101010;
Now start the slave (start slave;) and run `show slave status;` you may have to skip a couple of files (depending on your db size and how quick you were in the first couple of steps)
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; slave start; show slave status;
Repeat the last step until the database reports no duplicate entries (we also had to repair a table or two - easy `repair table tablename;`)