« 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