12

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.

Mei
  • 4,560
  • 8
  • 44
  • 53
Jawad Al Shaikh
  • 254
  • 1
  • 3
  • 15
  • Anyone finding this should note, that replication is asynchronous between the master and slave. That is, there is no guarantee, when the master dies and the slave is told to become master, that the slave has completely updated state of the database. It's very possible, or even likely, that the database will be stale, depending on the amount of writes you expect to your database. – searchengine27 Apr 04 '17 at 22:57

3 Answers3

12

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.

HampusLi
  • 3,398
  • 15
  • 14
8

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.

lynxman
  • 9,157
  • 3
  • 24
  • 28
  • 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. – Jawad Al Shaikh Aug 22 '11 at 09:43
4

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

quanta
  • 50,327
  • 19
  • 152
  • 213
  • 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. – Jawad Al Shaikh Aug 22 '11 at 09:36
  • If so, just `stop slave;`, wait for a while to process statements, and `reset master;`. – quanta Aug 22 '11 at 09:43