Mysql – Safely promoting master to slave

failoverMySQLmysql-5.5replication

Previously I've asked the similar question MySql Switching Masters During Failover with log-slave-updates.

Let's say this is our current replication

enter image description here

In addition, I have few application servers. Every app server connects to Master 1 directly in order to INSERT and UPDATE and to Slave[1-3] in order to SELECT.

My question is how exactly can I promote Master 2 to be a real and the only master. Importance notice that Master 2 and all slaves below it are mysql 5.5 and Master 1 is mysql 5.2

When I changed IP of Master 1 to IP of Master 2 in application settings and made deployment, there was a moment when I had UPDATE and INSERTS both on Master 1 and Master 2, which caused "Duplicate entry …" on Master 2. After that I rebuild the tree and run STOP SLAVE on Master 2 few seconds before the deployment. Of course, I lost some data, that was inserted to Master 1 and not replicated to Master 2.

So, what is the right way to do so without losting data.

In addition, I still don't understand what to run on Master 2 after STOP SLAVE in order to reset "slave settings", it looks like I don't really have to do this, just STOP SLAVE and everything should be working, but just to keep the order I want to reset "slave settings".

Best Answer

When you shifted the IP within your application, any DB Connections that were open at the moment were totally unaware of the move. A quick netstat | grep -i mysql would reveal that on Master1.

You would be better off doing the cutover of the IP as follows:

  • On Master1
    • FLUSH HOSTS;
    • service mysql stop
  • Change the IP in the App
  • On all web servers, service httpd restart

In order to safeguard the app from having to edit it for the sake of assigning a new IP, try using a DB VIP instead.

For example:

  • Master1 is 10.1.2.30
  • Master2 is 10.1.2.40
  • Use 10.1.2.70 as the DBVIP

Here is what you can setup

  • On Master1, run ip addr add 10.1.2.70/24 dev eth1
  • Use 10.1.2.70 in your app

Then, when it is time to cutover, do the following

  • On Master1
    • FLUSH HOSTS;
    • service mysql stop
    • ip addr del 10.1.2.70/24 dev eth1
  • On Master2
    • ip addr add 10.1.2.70/24 dev eth1
    • SHOW SLAVE STATUS\G and make sure all final SQL statements from Master1 executed
  • On all web servers, service httpd restart

That way, a cutover would not involve editing any part of the app. Please notice that I did not mention running STOP SLAVE anywhere because this would allow any final SQL statements to flow over from Master1 to Master2 once mysql is stopped on Master1.

Related Question