MySQL Recipes: Promoting a Slave to Master or Changing Masters

Posted in: MySQL, Technical Track

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

email

Interested in working with Paul? Schedule a tech call.

About the Author

Database Specialist based in Sydney,Australia

7 Comments. Leave new

links for 2008-07-01 | Zero / Love
July 1, 2008 12:34 am

[…] MySQL Recipes: Promoting a Slave to Master or Changing Masters (tags: howto database) […]

Reply

Here’s a link where I explains M->S1->S2 replication in spanish (more like a cheat sheet).

Replicación Maestro->Esclavo1->Esclavo2 en mySQL

Reply

The promote slave is only pasrtially correct. If the end point slave is using a non-standard port to connect with, the reset slave will indeed reset the slave, but will also reset it back to the default port, which will cause the end point slave to fail to connect. The solution is single, stop slave, reset slave, change master…, start slave. It’s an extra step.

Otherwise, the steps are on the money (good simple workup).

Reply
Daniël van Eeden
May 6, 2011 9:10 am

CHANGE MASTER TO MASTER_HOST=”; won’t work with 5.5 and newer.

Reply

On S2 you show ‘change master’ followed by a ‘reset slave’ which would obliterate the ‘change master’ setting, no? I could be wrong.

There is no mention of making sure binary logging is enabled on the slave that becomes the master.

Reply

Do you need to change the log files? My master has

log-bin=mysql-bin

and my slave has

relay-log=server-relay-bin

Do I comment out the relay-log and add log-bin?

Reply

If we follow this mentioned MySQL recipe to promote slave 1 as a master then slave (master) 1 send all the databases to slave 2 which are not add in my.cnf file.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *