MySQL Recipes: Promoting a Slave to Master or Changing Masters

Not a dear diary post today — I have a couple up my sleeve though.

In the corporate world, promoting a slave to a master requires many years of 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

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 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 a task that can require some fancy footwork on other RDBMSs.

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