OneFreeVoice

A few weeks ago, I discussed how to keep binlogs in sync in a tree or pyramid replication scheme. That thread discussed how to re-distribute load in case of failure in one of the intermediate slaves. Today we will look at how to create those intermediate replication slaves. We start with a traditional flat replication scheme and promote several servers into the intermediate layer.

In this example, we assume that our starting point is a currently running production environment where we have a single master with 18 slaves. We’ve been warned that all the slave I/O threads are going to start loading down the master server, and we are really starting to see it. We’ve purchased 3 additional servers for our intermediate replication layer and the task is to insert them into the middle of the replication process. We can afford to have stale data for a short time period, but we don’t actually want to take any of the databases offline during the process.

The first step is to add the 3 new servers into the current replication schema. We set these up exactly the same way we create a regular slave. In our case, we take a snapshot of an existing slave and copy it over. Generally, we can copy the my.cnf from one of the other slaves, and edit all the lines with hostname specific data. There are a couple of additional changes which are critical:

1. change server_id
2. add log_bin
3. optional: set expire_log_days=#
4. optional: add binlog_do_db=database_name
5. add log_slave_updates
6. add required grants for replication user

With slaves A, B, and C now running we still have the standard flat schema. Now we need to create our middle tier by inserting them in between the master and Slaves 1 to 18. Our strategy is going to be centered around the START SLAVE UNTIL syntax.

The first thing we need to do is decide our target bin-log from our master. All of our changes are going to occur when this binlog starts. To find our current bin-log, we issue SHOW MASTER LOGS on the master. Normally, you can just increment the number by one; however, you need to keep an eye on how fast your bin-logs rotate on their own. We need some time to execute scripts on each of our 21 slaves. To be safe, we add 2 to the current log number to get our target bin-log (we could always manually issue another FLUSH LOGS if we get tired of waiting). For this example, our master is currently on ‘bin-logs.000023′, so we set our target as ‘bin-logs.000025′.

Once we know our target bin-log, we can issue the following set of commands on slaves A,B, and C:

STOP SLAVE;
START SLAVE UNTIL MASTER_LOG_FILE = 'bin-logs.000025',
                  MASTER_LOG_POS = 106;
SELECT MASTER_POS_WAIT('bin-logs.000025', 106);
RESET MASTER;
START SLAVE;

In layman’s terms: the slave stops when we hit our target bin-log. We immediately issue a reset master on the intermediate slave – this resets all 3 intermediate slaves to the same bin-log number and position. Once that is done, we go ahead and start the slave again.

We issue similar scripts on the remaining slaves:

STOP SLAVE;
START SLAVE UNTIL MASTER_LOG_FILE = 'bin-logs.000025',
                  MASTER_LOG_POS = 106;
SELECT MASTER_POS_WAIT('bin-logs.000025', 106);
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO
    MASTER_HOST = 'slavea',
    MASTER_USER = 'replication_user',
    MASTER_PASSWORD = 'replication_pass',
    MASTER_PORT = 3306,
    MASTER_LOG_FILE = 'bin-logs.000001',
    MASTER_LOG_POS = 4;
START SLAVE;

We break the slaves into groups of 6 and run a different script on each group. The only difference in the scripts is the MASTER_HOST directive. Slaves 1 to 6 get MASTER_HOST = ‘slavea’, Slaves 7 to 12 get MASTER_HOST = ‘slaveb’, and Slaves 13-18 get MASTER_HOST = ‘slavec’.

The key differences between these scripts and our first script is that we RESET SLAVE instead of RESET MASTER. The change master script points the slaves to the new intermediate servers. Finally, we start the slave again.

If all goes well, as soon as the master rotates to our target binlog, slaves A,B and C become intermediate slaves and slaves 1 to 18 connect and replicate from one of the intermediate slaves. None of the databases have gone offline, and our data is only stale for a minimal amount of time.