« 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
Comments
Hi Mike,
This occurs most commonly if you don't have sync-binlog=1 set on the master.
What can happen is that the database writes to the binary log and the slave reads it, but the binary log never actually makes it to disk due to disk caching in the OS/hdd. So when the master is restarted, the slave is reading from a future location in a log that doesn't have the information.
Normally you can do what you mentioned above, however in some cases, the master may have had more data that was lost in the binary log but the slave never received. So it could be a good idea to use something like MySQL Table Sync to ensure that they are really the same, or else you might have problems somewhere down the line.
Posted by: Harrison Fisk at December 7, 2007 1:58 PM
@Harrison:
The caveat to doing so is that it can definitely decrease your write performance on your database in general.
@Mike:
I find that when that happens, I could simply stop slave; start slave; and the slave will read the new binlog index, and move right on to the new file.
Michael
Posted by: Michael S. Moody at December 7, 2007 5:21 PM