Fix MySQL Slave Replication Error
August 6, 2015
Occasionally my mysql mirror server will crash or the power will go off on it since it's not mission critical, it's not on a UPS. Typically I'd use this "opportunity" to build a new mysqldump or rsync the live mysql database folder and start a new mirror, but that takes a whack of time that I don't have. So today I found a cool technique which allows you to reset the slave and start where you left off. This technique assumes that you're looking at a server where the master and it's binary log are completely intact.
I was met with this error when my mysql slave came back online:
Error: Last_SQL_Errno: 1594 Last_SQL_Error: Relay log read failure: Could not parse relay log event entry.
Since my mirror is fairly disposable, I wasn't too concerned about backing things up, I just wanted it to resume replication.
So I run "show slave status \G" on the slave and note:
Master_Log_File: mysql-bin.000462 Read_Master_Log_Pos: 136602848 Relay_Log_File: mysqld-relay-bin.000182 Relay_Log_Pos: 3512147 Relay_Master_Log_File: mysql-bin.000462 Exec_Master_Log_Pos: 105523455
So now we can run mysqlbinlog --start-position=3512147 mysqld-relay-bin.000182 to check the file is actually corrupt, but in my case I knew it was shutdown incorrectly so I knew that was definitely corrupt. So to get things going again, we stop the slave, reset the slave and then set our start point to the Exec_Master_Log_Pos
mysql> stop slave; Query OK, 0 rows affected (0.14 sec) mysql> reset slave all; Query OK, 0 rows affected (0.43 sec) mysql> CHANGE MASTER TO MASTER_HOST="master.mysql.yourdomain.tld", MASTER_USER="replicationuser", MASTER_PASSWORD="replicationpassword", MASTER_LOG_FILE="mysql-bin.000462", MASTER_LOG_POS=105523455, MASTER_SSL=1, MASTER_SSL_CA="/var/lib/mysql/certs/ca-cert.pem"; Query OK, 0 rows affected (0.93 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)
Your slave will reconnect to the master and start off where ever it left off before the power was ever so unpleasantly removed from it.