Had a problem with a slave database being a different (newer) version of MySQL than the master. After downgrading to the correct version of MySQL, the replicated data was no longer consistent with the master. ( For a good tool that can check the slave data’s consistency table by table, see here http://www.maatkit.org/doc/mk-table-checksum.html )
To fix this, the following steps were taken:
1) stop mysql on the broken slave.
2) on another good slave, in mysql prompt type ‘stop slave’, then (may want to remove some updatelogs first) tar up the /var/lib/mysql directory like so: ‘tar -cvpf good_slave.tar /var/lib/mysql’, then type ‘start slave’
3) Copy the tar to the broken slave with sftp or what have you
4) on the broken slave, move the /var/lib/mysql folder somewhere else for safekeeping such as ‘mv /var/lib/mysql /var/lib/broken_mysql’
5) untar the good_slave.tar file and make sure it goes in /var/lib/mysql
6) Edit relay-log.info file, change the hostname, see step 6 here: http://www.redips.net/mysql/add-new-slave/
# first line before change
# first line after change
7) (not needed in this case since broken slave has it already, but for a new slave) Copy my.cnf and increment server-id – NOTE: edit the “relay-log” line with the new hostname!
‘8) start mysql on the broken slave. check ‘show slave status’ and hopefully seconds behind decreases to zero quickly!