How do I switch master with slave and vice versa on MySQL?

I have two instances of MySQL, one master and one slave.

How can I switch them to make the slave the master and vice-versa while avoiding downtime?

If you use a DBVIP, you could remove the DBVIP from the Master and bring it up on the Slave.

EXAMPLE

Let's make up a DBVIP, like 10.1.2.30.

Put this up on the Master in the OS

ip addr addr 10.1.2.30/24 dev eth1

Replace your IP in your application with 10.1.2.30, Let the application continue to run for a while, making sure the DBVIP is being used by the application.

Here is something aggressive:

• service httpd stop on all Apache Servers

• service mysql stop on the Master

• ip addr del 10.1.2.30/24 dev eth1 on the Master

• ip addr add 10.1.2.30/24 dev eth1 on the Slave

• service httpd start on all Apache Servers

• SHOW PROCESSLIST; on the Slave to make sure there are incoming DB Connections

If you see DB Connections coming in from the Apache Servers, CONGRATULATIONS you have manually performed a failover.

UPDATE 2012-09-19 14:28 EDT

If you cannot use a DBVIP, you must do more work instead

STEP 01) Activate Binary Logging on the Slave

Add this to /etc/my.cnf on the Slave

[mysqld]
log-bin=mysql-bin

STEP02) service mysql restart on the Slave

Binary Logging should be enabled in the Slave

STEP03) Run the CHANGE MASTER TO command on the Master using the Slave as its Master

• Use mysql-bin.000001 as the Master_Log_File

• Use the following number as the Master_Log_Pos

• 107 for MySQL 5.5

• 106 for MySQL 5.1

• 98 for MySQL 5.0

STEP04) Run START SLAVE; on the Master

At this point

• the Master is the Slave's Slave

• the Slave is the Master Slave

This configuration is better known as

• Master/Master

• Circular Replication

STEP05) service httpd stop on all Apache Servers

STEP06) Change the IP address in the App to Connect to the Slave

STEP07) service httpd start on all Apache Servers

STEP08) SHOW PROCESSLIST; on the Slave to make sure there are incoming DB Connections

If you see DB Connections coming in from the Apache Servers, CONGRATULATIONS you have manually performed a failover without MySQL Downtime. The only downtime there is comes from the Window of time STEP05 - STEP07.

StackExchange Database Administrators Q#24549, answer score: 5