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.