« Where's Mike and MySQL? | Main | Remove Mirror Effect on Leopard Dock (Cleaner Look) »

December 7, 2007

Fixing Broken MySQL Database Replication

And just as I was saying I rarely get to fiddle with databases at work, this morning I resolved an issue with replication.

One of our database machines got rebooted yesterday without properly shutting down replication. The slave server had no idea this was happening. After looking around a bit it seems clear that MySQL on the master has moved to recording changes in a new log file, but the slave is still attempting to read from the old.

Which results in something like:

Slave_IO_Running: No
Slave_SQL_Running: Yes

After verifying the log positions in master and slave binary log files to confirm exactly where things stopped, I shut down the slave processes altogether and issue a command to point the slave to the new log file and reset the log position:

mysql> CHANGE MASTER TO MASTER_LOG_FILE='data15-bin.000030', MASTER_LOG_POS=4;

Start replication back up, the slave scrambles a bit to get caught up, and we're back to another period of not having to do anything to keep MySQL running.

Posted by mike at December 7, 2007 9:42 AM