1

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.

3
  • This belongs on ServerFault, I think.
    – ceejayoz
    Apr 12, 2010 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, 2010 at 15:36
  • @OmerGertel: Well it's definitely not programming related , so I agree, should be on SF.
    – lexu
    Apr 12, 2010 at 16:08

1 Answer 1

1

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.
2
  • 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, 2010 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. ;) Apr 13, 2010 at 8:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .