I have two MySQL servers configured with master-slave replication.

Before we deploy a new application version we:


2) Take a MySQL dump of the slave.

However, if a mistake is done during the deployment of the new schema version (a table is dropped by mistake, for example), having the slave intact doesn't help. Our service is write-intensive, so we can't turn it back up until we have a master working.

If we now load the mysql dump back into the master, it will take a long time during which our service remains down.

What is the best-practice to recover from such a mistake? How can I setup the system so I can easily promote the slave, turn on our service and only then tend to the broken database? Mainly, I'm worried with re-syncing the slave and the master after changes are done on the slave.

  • This belongs on ServerFault, I think. – ceejayoz Apr 12 '10 at 15:29
  • Could be. I'm not sure the DB is the domain of the sysadmin. I asked it here because there's more MySQL activity on StackOverflow. – OmerGertel Apr 12 '10 at 15:36
  • @OmerGertel: Well it's definitely not programming related , so I agree, should be on SF. – lexu Apr 12 '10 at 16:08

The method you're using is certainly the safest but there are ways you can cheat to make the process more transparent.

  • Keep the synchronisation turned off while you perform the next few steps.
  • Make the slave the master (easier if you have master/master replication instead of master/slave) and switch your application over to it.
  • Restore the old master from the dump file.
  • Re-enable synchronisation.
  • Ensure the the old master is synced from the former slave.
  • Switch the application back if so desired.
  • I had the hunch the answer will be something like this. I need the log-position of the slave, before I turn the application back on, so I can correctly configure the old-master as a slave on the new-master. Anything other gotchas I need to be careful about? – OmerGertel Apr 13 '10 at 7:23
  • Nothing I can think of, although it's been a while since I've been through those steps, so don't take my word for it. ;) – John Gardeniers Apr 13 '10 at 8:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.