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 MySQL master-slave replication that works fine; I googled the whole net and MySQL site to find the standard procedure to make use of the replication but found nothing. It is as if admins are happy to have replication on, but when the time comes to perform a disaster recovery no initial plan has been put into practice and publicly shared.

What i want to know is how to make the slave machine replace the master one in case the later got damaged, burnt, etc. I guess the slave machine should take the network address of the master but what else should be done? For instance, changing MySQL user permissions or running some commands! Please include references if any.

share|improve this question
add comment

3 Answers

up vote 5 down vote accepted

For a DR solution you most likely want a semi-manual process. That is, you need to decide the disaster validates a full DR failover and it's not just a small network blipp and you're stuck with days of fail-back work.

To switch a MySQL slave to a master you just issue a few commands in mysql, Mysql details can be found here.

I'd strongly suggest scripting the whole process (and test it!) and add whatever other commands you may want to run on the box to do the DR failover (restart the web tier or whatever else is needed). We use func on a management server to run failover commands all the tiers of our applications.

When referring to disaster recovery I'm talking about site failure. For a cluster failover things should probably be automatic and much simpler.

share|improve this answer
    
very informative and useful, thanks, –  jadook Aug 22 '11 at 9:30
add comment

All that is required to replace a MySQL server with a replica is to switch the IP address of the server, no further commands required on MySQL.

For extra comfort if you want some peace of mind and do this automagically you can do the following:

  • Have both MySQL servers do master-master replication, check it here
  • Create a Virtual IP that'll be used by the initial master server, I user heartbeat for this (part of the LinuxHA project) but you can use your preferred weapon of choice for your OS
  • Configure the parameters for the IP to switch over, this could be the network going down, the other machine dying or simply MySQL being shut down

This setup have lots of advantages, you can easily do maintenance on one node by switching the traffic to the other and have hassle free recovery, you won't even need to get out of bed :) Although one recommendation, be smart about your Virtual IP parameters, you can end up with both nodes trying to claim the Virtual IP which is not the desired result.

share|improve this answer
    
look very good solution but i never heard of virtual ip stuff, and my setup currently is not master-master, i would pick it as answer but already chose other. thanks again. –  jadook Aug 22 '11 at 9:43
add comment

Assuming you have 2 MySQL Slaves: slave 1 and slave 2. In case your master is down, you decide to promote the slave 1 to become a new master. I suggest a procedure to follow:

Make sure that all slaves has processed any statements in their relay log with:

mysql> STOP SLAVE IO_THREAD;
mysql> SHOW PROCESSLIST; to see `Has read all relay log` state.

on slave 1, promote it to become a master with:

mysql> STOP SLAVE;
mysql> RESET MASTER;

on slave 2, point to new master with:

mysql> CHANGE MASTER TO MASTER_HOST='slave 1';
mysql> START SLAVE;

And the finally is instruct each client to direct its statements to Slave 1.

http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-switch.html

share|improve this answer
    
good, but u assumed again something not part of my requirements. i got single slave-master, from where i'll bring the second slave! but still i thank you for the answer. –  jadook Aug 22 '11 at 9:36
    
If so, just stop slave;, wait for a while to process statements, and reset master;. –  quanta Aug 22 '11 at 9:43
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.