Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

Before we deploy a new application version we:

1) STOP SLAVE

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.

share|improve this question

migrated from stackoverflow.com Apr 13 '10 at 1:07

This question came from our site for professional and enthusiast programmers.

    
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
add comment

1 Answer

up vote 1 down vote accepted

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.
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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