How to actually use mysql slave as soon the master is failover or got burnt
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.
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.
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