We have an “A-B” replication set up. The power went down (boss flipped the wrong breaker). When the servers came back up, replication was broken.
The SQL thread was running fine, but the I/O thread would not start.
I found this stackoverflow answer: mysql-slave-i-o-thread-not-running. Luxknight007 seemed to have the answer for my case, as I knew this wasn’t a permissions issue. His answer, voted up several times, indicated the following method of fixing the issue:
# RESET SLAVE; # <-- remove this!
CHANGE MASTER TO MASTER_LOG_FILE='$BINLOG';
CHANGE MASTER TO MASTER_LOG_POS=$ENDLOGPOS;
But which bin log and which position? When I went to the bin log folder on the A (master) server (you can find this in the my.cnf file as the
log-bin configuration), I found one log file that was smaller than all of the others and had a last modified date at about the point where the power had been cut, so that was clearly the correct point.
I used to read
mysqlbinlog program to read the end of the log, and just picked the last end_log_position. I did remove the
RESET SLAVE command as it would only wipe out all of the relay logs and there was no need to do this in my case.