When you are using pyramid replication scheme - it could be useful to have n+1 intermediate servers. The idea is to have extra bandwidth in case one of those intermediate slaves fails. For example, if you have 3 intermediate slaves named A,B and C and you have 18 end slaves named 1-18:

The idea would be that if your slave B fails, you split up slaves 7-12 between slaves A and C. You can point 7-9 to slave A and 10-12 to slave C. Your replication scheme now looks more like this:

The question becomes: What is th easiest way to move these slaves to a new intermediate server?

The brute force method is not hard, it’s just tedious. You need to examine the bin logs on 7-12, look at the last statement that was run, then look at slave A and C and find the bin-log and position for the same statement. You’ll probably want to make sure you are matching the server_id from your intial master server, and also the SET TIMESTAMP line from the binlogs. The book High Performance MySQL, Second Edition covers this task well (see page 384 - Locating the desired log positions). If you are lucky, at least servers 7-12 are in the same place and the amount of work you need to do is minimized. Once you know the bin-log and position on intermediate slave, you can issue CHANGE MASTER commands on slaves 7-12 to move them over.

But what if your intermediate servers are always in sync? If they’re always recording updates from the master in the same bin-log position in the same bin-log file, this would become easy. Simply perform SHOW SLAVE STATUS from each of 7-12, note the RELAY_MASTER_LOG_FILE, EXEC_MASTER_LOG_POSITION and then incorporate them into a CHANGE MASTER command pointing to the new host.

Assuming your servers can actually stay in sync, we need to get them there in the first place. I’m still trying to figure out the best way to do this. This should be a process that can also be followed to get things back in sync if somehow they slip. So far I’ve come up with the following process:

1. Issue a show master logs to find the current bin-log position on the master server
2. Add one to the bin-log number from above - e.g. if the result of step 1 is bin-logs.00024 then your new number is bin-logs.00025
3. On each of the intermediate servers, issue: STOP SLAVE; START SLAVE UNTIL MASTER_LOG_FILE = ‘bin-logs.00025′, MASTER_LOG_POS = 106; SELECT MASTER_POS_WAIT(’bin-logs.00025′,106); RESET MASTER;
4. On the master, issue: FLUSH LOGS;
5. Wait for all the intermediate slaves to catch up to the master and reset
6. On each of the end slaves, issue: RESET SLAVE followed by CHANGE MASTER TO…*

I have a couple of unresolved questions/concerns with this plan.

Is there a chance that a statement could replicate between when MASTER_POS_WAIT returns and when RESET MASTER executes? If that happens, does it happen on all of the intermediate slaves? [UPDATE] Changing this to use the START SLAVE UNTIL syntax suggested by Mark Leith in the comments resolves this issue.

What’s the best way to script this so I’m not manually issuing RESET SLAVE and CHANGE MASTER on 18 different end slaves?

*Interestingly enough, if you have master_host, master_user, etc. set up in your my.cnf file, then you only need to do a reset slave - the reset slave command picks up this information from the configuration file and starts replication on that master server at bin-logs.00001, position 4. You don’t really want to do this though. The option is deprecated and will disappear in upcoming versions. Also, this will work against you if you eventually have to point your slave to a new master.

This entry was posted on Tuesday, June 24th, 2008 at 4:06 PM and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.