MySQL Recipes: promoting a slave to master or changing masters

In the corporate world, promoting a slave to a master requires many years hard work, obtaining a suitable business degree, getting an MBA, and having a taste for promotion. The alternative is getting that break as an entrepreneur where you start as the master of your own destiny and end being the master when the money runs out or you take the money and move on.

In MySQL land, failovers for redundancy, disaster recovery or load balancing are performed by master databases and slave databases, the most popular method using binlog replication.

Note: This commands are valid for MySQL 3.23,4.0,4.1,5.0 and 5.1

For older versions:

1. replace RESET MASTER with FLUSH MASTER.
2. replace RESET SLAVE with FLUSH SLAVE.

In a simple configuration you might have one master and one slave.

Recipe to promote a slave to a master for simple replication.

On Master:

1. FLUSH LOGS;

On Slave:

1. STOP SLAVE;
2. RESET MASTER;
3. Optional: CHANGE MASTER TO MASTER_HOST='';

More complex setups:

If you have a chain of replication using a slave as a pseudo master for another slave. Kind of like having a king with a local sheriff taking orders and then telling the serf.

eg: master M1 -> slave S1 -> slave S2

Note: Slave S1 has --log-slave-updates enabled

Recipe to promote a slave to a master for simple chained replication.

On Master M1:

1. FLUSH LOGS;

On Slave S1:

1. STOP SLAVE;
2. RESET MASTER;
3. Optional: CHANGE MASTER TO MASTER_HOST='';

On Slave S2:

1. STOP SLAVE;
2. RESET SLAVE;
3. START SLAVE;

If you have a multiple slave replication with one master and two or more slaves.

eg: slave S2 <- master M1 -> slave S1

Recipe to promote a slave to a master for multiple slave replication.

Note: Slave S2 is going to use the newly promoted slave S1 as its master.

On Master M1:

1. FLUSH LOGS;

On Slave S1:

1. STOP SLAVE;
2. RESET MASTER;
3. Optional: CHANGE MASTER TO MASTER_HOST='';

On Slave S2:

1. STOP SLAVE;
2. CHANGE MASTER TO MASTER_HOST = 'SLAVE S1 hostname';
3. RESET SLAVE;
4. START SLAVE;

Verification:

Once you have used one the recipes, and you are keen to taste the results, run the command

SHOW SLAVE STATUS;

Make sure that the binlog for the master is actually the correct one.

One of the joys of using MySQL is the simple commands required to perform task which can require some fancy footwork on other RDBMS's.

There are a couple more methods of replication which aren't covered here. Also see the MySQL Replication FAQ. The methods are the same, but the formatting of the procedure(s) are less than ideal.

Have Fun

Paul