Convert MySQL Slave to Master

Found this interesting the other day. Our production master SQL server was having connection issues.  So we needed to cut over from the master to the slave to try to resolve the issues.

What we did

# Shutdown the IP address for the master

# Shut off the mysql service on the master

# Bring up the IP address on the slave

This was all that was REQUIRED to get MySQL connections pointing to the slave.

What was WRONG with this was what happens when we want to cut back over once the issues with the master have been resolved. Turned out to be a ethernet cable. Once the cable was replaced we were able to verify the issue no longer existed.

What we SHOULD have done

# Log into the MySQL slave and run STOP SLAVE; RESET SLAVE;

# Add log-bin to the slave’s /etc/my.cnf file

# Stop the IP interface on the master (optionally the MySQL service)

# Add the IP address to the slave system

# Restart the MySQL service on the slave server. It should now be running as the master.

The reason this is a better solution is that now when the broken system is fixed, we can set it up as the slave and have the binary log replicate and catch up the slave to the new master and then cut over to the fixed system as the master in the same steps as above.

  1. No trackbacks yet.

You must be logged in to post a comment.

Get every new post delivered to your Inbox.

%d bloggers like this: