04 July 2010

MySQL replication master-change gotcha

If you move the master server in your MySQL replication setup, you might be tempted to simply issue the command CHANGE MASTER TO MASTER_HOST='new.host.example.com'; on the slave to point it to the new master.

Don't.
If you do, the slave will lose its place in the log and you'll get errors from key conflicts as the slave tries to reinsert old rows. (Errors if you're lucky. The slave might just silently insert duplicate rows.)

Instead, issue a SHOW SLAVE STATUS\G; and use the values for Master_Log_File and Read_Master_Log_Pos (I think you want this rather than Exec_Master_Log_Pos, but they were equal in my case -- check the manual) to populate MASTER_LOG_POS and MASTER_LOG_FILE. In other words,

CHANGE MASTER TO MASTER_HOST='new.host.example.com', MASTER_LOG_POS=12345, MASTER_LOG_FILE='mysql-bin.003334';

I screwed this up, but I was lucky enough to have just enlarged the volume, so I had an EBS snapshot of the last known good version of the slave. I just dumped the volume I'd broken and started from the snapshot. EC2+EBS ftw.

No comments:

Post a Comment

About Me

blog at barillari dot org Older posts at http://barillari.org/blog