MySQL Troubleshooting

If you are getting a relay log read failure, you can re-sync the servers this way:

stop slave;
show slave status\G

Note the Relay_Master_Log_File name and Exec_Master_Log_Pos byte position; they will be used shortly.

reset slave;

The above line purges all the relay binary logs, including the corrupted one, and writes a new index and starting relay binlog file. At this point if you did another “show slave status” command you would see the counters have been zeroed out.

change master to 
  MASTER_USER='[username]',
  MASTER_LOG_FILE='[the file name you noted above]',
  MASTER_LOG_POS=[the byte value you noted above];

If you ever want to take a binlog from a master server and run its contents on a slave, you can do it with a command like this:

mysqlbinlog /tmp/mysql-bin.000001 --start-position=[byte-position-to-start] \
  | sed -e 's/SET \/\*\!\*\//SET AUTOCOMMIT=0/g' \
  | mysql -uUSERNAME -pPASSWORD

Replace the “000001” with the number of the log you want to process, and set the start position to the byte position desired. For example, you might use “show slave status\G” to discover where replication halted and use the value on the Exec_Master_Log_Pos line as the starting position.