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.


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s