MySQL Recipes: Promoting a Slave to Master or Changing Masters

Dec 8, 2006 / By Paul Moen

Tags: , ,

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

6 Responses to “MySQL Recipes: Promoting a Slave to Master or Changing Masters”

  • links for 2008-07-01 | Zero / Love says:

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

  • Rodolfo says:

    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

  • Gary Smith says:

    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).

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

  • Sam says:

    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.

  • Doug says:

    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?

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>