MySQL Replication: Move slave to be slave of another slave, without any (or least possible) downtime

Question

I have the following setup:

M1 = Master 1
S1 = Slave 1
S2 = Slave 2
S3 = Slave 3

Where all slaves are replicating directly from M1. I would like to move S2 and S3 to be slaves of S1.

I'm not sure about how to do it without any downtime and with replication picking up where it left off. How to I ensure that the slaves continue from the correct point in the logs from their new master without missing any writes or trying to repeat writes that they had already done before the change.

One of the slaves is at an offsite location on a relatively slow connection, and it's quite a large DB, so rebuilding the slaves from a dump or copying data files would take too long, and seems to me as though it should be unnecessary!

Any help or advice greatly appreciated!

Simon

Answer 1

WARNING : Please read this carefully, make sure you understand & trust it

Here we go :

Step 01) Get the Private IP Address of S1 (write down somewhere)

$ ip addr show

Step 02) Get the username and password for replication from S1

cd /var/lib/mysql
cat master.info

The username and password is in plain text in this file

Step 03) Run this on S1

mysql> GRANT REPLICATION SLAVE ON *.* TO '<username from Step 02>'@'%' IDENTIFIED BY '<password from Step 02>';

Step 04) On S1, add this to /etc/my.cnf

[mysqld]
log-slave-updates
log-bin=mysql-bin

Step 05) On S1, run this

service mysql restart

This creates binary logs on S1

Step 06) On M1, run this on the command line

$ mysql -u... -p... -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400);" &
$ sleep 15
$ mysql -u... -p... -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'

This will put the Master in a state of suspended animation. Binary log position on the Master will stop moving. This will give all the Slaves a chance to all stop at the same place in Replication. The last line will echo the MySQL ProcessID that is holding the read lock. DO NOT LOSE THIS NUMBER !!!

Step 07) Run this on S1

mysql> RESET MASTER; SHOW MASTER STATUS;

This will clear all binary logs on S1 and show you the binary log and position of S1. The log file should be called mysql-bin.000001. The position will vary based on the version of MySQL. For MySQL 5.5, it is 107. For MySQL 5.1, it is 106. For any version before MySQL 5.1, it is 98.

Step 08) Run this on S2 and S3

mysql> STOP SLAVE;

Step 09) On M1, run this:

mysql> KILL <ProcessID From Step 06>;

This will release all pending INSERTs, UPDATEs, and DELETEs.

Step 10) Run this on S2 and S3

mysql> CHANGE MASTER TO
MASTER_HOST='<ip address of S1 from Step 01>',
MASTER_PORT=3306,
MASTER_USER='<username from Step 02>',
MASTER_PASSWORD='<password from Step 02>',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;

Remember, user MASTER_LOG_POS=106 for MySQL 5.1 or MASTER_LOG_POS=98 for MySQL 5.0 and back.

This will point replication of S2 and S3 to start from S1.

Step 11) Run this on S2

mysql> START SLAVE; SELECT SLEEP(5); SHOW SLAVE STATUS\G

This will kick off replication, pause 5 seconds, and show the slave status. If Slave_IO_Running=Yes and Slave_SQL_Running=Yes, everything went right.

Step 12) Repeat Step 11 for S3

That's it.

CAVEAT

Downtime Begins with Step 06 and Ends with Step 09