Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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".

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
Thank you very much for the answer. How exactly can I cause Master 2 to forget it's previous slave role? Because, SHOW SLAVE STATUS on Master 2 still will be showing some slave related info. Unfortunately, my Cloud provider doesn't allow me to change network interfaces. –  com Sep 4 '12 at 15:52
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.