Sometimes when replaying the binary log, the slave will come across something that stops it cold, and it will go no further. One way to get around this is to start over and completely reinstall. Another way is:
mysql> STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;
The result of the above is to simply skip the problematic instruction in the log, and go on. This might result in a table that is slightly out of sync, which can be dealt with by other means (see later in this post).
If the above does not work, and specifically if you see this error:
Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
the "Could not parse relay log event entry" is suggestive of a solution: wipe out the relay log and re-create it from wherever the slave is currently at in the master log:
mysql> STOP SLAVE; CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000012', MASTER_LOG_POS = 148376500; START SLAVE;
MASTER_LOG_POS is Exec_Master_Log_Pos, and
MASTER_LOG_FILE is Relay_Master_Log_File
in the current output of "SHOW SLAVE STATUS".
Another possibility, especially after the disorderly shutdown of a slave which is reputed to throw position in the relay log out of whack, resulting in a replay of already-run transactions and many inserts then throwing a duplicate key error, is to temporarily ignore replication errors. Ie. add this
slave-skip-errors = all
and let the slave run for a few minutes before turning it off again.
Install the Percona Toolkit:
apt-get install percona-toolkit
in order to gain access to pt-table-sync and pt-table-checksum. Then check for tables that are out of sync with:
/usr/bin/pt-table-checksum --quiet --ignore-databases mysql,performance_schema,information_schema -umaster_user -ppassword
(Note: if databases that are not being replicated are not excluded from pt-table-checksum, it can hang up indefinitely.)
Then on the MySQL master, force a table on the slave into the exact state of that on the master with:
pt-table-sync --execute h=localhost,D=databasename,t=tablename h=slaveIP -umaster_user -ppassword
Or force a database on the slave into the exact state of that on the master with:
pt-table-sync --execute h=localhost h=10.9.93.1 --databases databasename -umaster_user -ppassword