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.