Converting 3 Master + 1 Slave to 4 Master - Any advice?

Question

so we have a beautiful 3 master 1 slave cluster running.

we would like to convert this to a 4 master cluster.

here is how the topology currently looks

s1

enslaved by s3

s2

enslaved by s1

s3

enslaved by s2

As it stands now, all servers are fully in sync and used by a live running application.

Question Part 1: What mysql commands need to be executed on each of the servers to reset the current binlog position to 0 and sync this action to whatever server is enslaved by the one where the commands are executed. Just to clean things up prior to making the switch.

Question Part 2: We can easily change the source code running to ignore one of the servers. Each of the nodes can support the whole application running live, so we can take each of the servers down one by one. What commands need to be executed to basically do the following:

Pause updating the node from a master. But make it so when switching to the new master, the syncing of the database will continue as left off. So I am assuming that ultimately 2 servers will need to go down at this point. One of which being the slave that's switching from slave to master/slave. I should also mention, we have the ability, and use it often, to basically pause the inserting / updating of 'the main table'. Most of the other data is non-critical items such as logging, etc. So even if a few rows (or even some full tables) are lost due to the servers being out of sync its OK.

So that pretty much sumarizes it. We want to convert a running and live 3 + 1 combo to a 4 combo. All 4 servers are in sync, we have a few tools available on the code side to make taking 1 to 2 servers down at a time safe.

We should be able to do this without any downtime.

Any and all advice appreciated.

Thank you kindly.

Answer 1

Here is the topology you just described

+-------------+
|             ^
|             |
V             |
M1 --> M2 --> M3
|
|
+----> S1

You would like to slip S1 as a Master into the Replication Ring so that it looks like this:

+---------------------+
|                     ^
|                     |
V                     |
M1 --> S1 --> M2 --> M3

Essentially, you only have to prep S1 to a Master and Point M2 to receive binary log entries from S1.

OK Here we go

STEP 01) Point your application at M3

STEP 02) Prep S1 to be a Master

• set the server_id as a different number from M1, M2, M3
• set log-slave-updates in my.cnf like you did for M1, M2, M3
• enable binary logging on S1 the same way you enabled it on M1, M2, M3
• restart mysql on S1

STEP 03) run STOP SLAVE; on M1

STEP 04) run SHOW SLAVE STATUS\G on S1 and M2

Make sure Seconds_Behind_Master is 0 on S1 and M2

STEP 05) run SHOW MASTER STATUS; on S1 (Record the binary log and position)

STEP 06) run this on M2

STOP SLAVE;
CHANGE MASTER TO master_host='IP Address of S1',
master_post=3306,
master_user='repluser',
master_password='replpass',
master_log_file='XXXX'
master_log_pos=YYYY;
START SLAVE;
SHOW SLAVE STATUS\G

where XXXX is the binary log from STEP 05 and YYYY is position from STEP 05

If the SHOW SLAVE STATUS\G says Yes for Slave_IO_Running and Slave_SQL_Running then you have achieved this:

+---------------------+
                      ^
                      |
                      |
M1 --> S1 --> M2 --> M3

STEP 07) run START SLAVE\G on M1

STEP 08) run SHOW SLAVE STATUS\G on M1, S1, M2, M3

Where Seconds_Behind_Master is 0 on all the servers...

STEP 09) Point you application to other servers as desired

Any questions ???

If none

Give it a Try !!!

Your mission, should you decide to accept this, is to practice this is a Dev/Staging Environment and make sure you trust this algorithm before doing this in Production.

In the event your data is caught or killed, the DBA StackExchange and I will disavow any knowledge of your actions.