Here's the scenario. You have three hosts, a master (M) and two replicas (R and S).o
M
/ \
R S
You want to move the replica S so that its master is R, like so:
M
|
R
|
S
You don't need to touch M to do this, but you do need to stop replication briefly on both R and S.
On S:
STOP SLAVE;
On R:
STOP SLAVE;
SHOW SLAVE STATUS \G
Capture the output of the slave status - in particlar you need the Relay_Master_Log_File
and Exec_Master_Log_Pos
. Call this POS_A
.
SHOW MASTER STATUS;
You need the File
and Position
- call this POS_B
.
START SLAVE;
You're done interfering with R now, if all goes well. If you are remastering multiple replicas at once you should do all of them at the same time as S above (i.e. treat them as S1, S2 etc).
Back on S:
Using the log file and position from POS_A, run this command on S:
START SLAVE UNTIL MASTER_LOG_FILE = '<POS_A_LOG>', MASTER_LOG_POS = <POS_A_POS>;
SHOW SLAVE STATUS \G
Verify that the Relay_Master_Log_File
and Exec_Master_Log_Pos
on S match POS_A. Once they do:
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = <R_IP>, MASTER_USER = 'replication', MASTER_PASSWORD = '<DBPW>', MASTER_LOG_FILE = '<POS_B_LOG>', MASTER_LOG_POS = <POS_B_POS>;
START SLAVE;
I generally use the internal IP for R. You can get the environment's DB password from ~root/.my.cnf
. S is probably a minute or two behind at this point, but should catch up very quickly. Running SHOW SLAVE STATUS \G
on S should show that it's pointing at R instead of M, and that Exec_Master_Log_Pos
continues increasing.