MySQL: chained replication M1>S1>S2

In most case simple master-slave replication provides enough level of high-avalability (about 99.9%), read ops balancing and allows non-blocking backups (if you use MyISAM tables since InnoDB tables can be backed up without blocking). But sometimes you may want to setup chained replication with two slaves: first slave replicates from the master, and the second slave replicates from the first one. Often such technique is used for smooth MySQL server migration. In this this case first slave is switched to master mode, and second slave remains the slave. Hereby we will refer master as M1, first slave as S1, and the second one as S2, so S1 replicates from M1, and S2 replicates from S1.

M1

Enable binary logging:

server-id=1
log-bin=mysql-bin
binlog_do_db=sakila

Provide privileges for slave:

GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'slave1.example.com' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Dump database:

mysqldump -uroot --opt --master-data -p sakila > /tmp/sakila.sql

M1 is ready. Going to S1.

S1

server-id=2
log-bin=mysql-bin
replicate-do-db=sakila
log-slave-updates
skip-slave-start

Missing log-slave-updates parameter is the most common mistake while setting up chained replication. So make sure you have it.
Granting privileges:

GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'slave2.example.com' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Drop replicated database sakila if exists:

DROP DATABASE sakila;

Create empty database:

CREATE DATABASE sakila;

Reset old slave:

RESET SLAVE;

Import dump from M1 to S1:

mysql -uroot -p sakila < /tmp/sakila.sql

Obtaining M1 binary log coordinates. Thet can be found at the bginning of dump file:

CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000002', MASTER_LOG_POS=107;

Providing information about replication user:

CHANGE MASTER TO MASTER_HOST='master1.example.com', MASTER_USER='replica_user', MASTER_PASSWORD='password';

Checking slave status:

SHOW SLAVE STATUSG

Let’s continue with S2.

S2

Adding replication parameters to S2:

server-id=3
log-bin=mysql-bin
binlog_do_db=sakila
replicate-do-db=sakila

Drop database sakila if exists:

DROP DATABASE sakila;

Create empty database:

CREATE DATABASE sakila;

Reset old slave:

RESET SLAVE;

Import dump from M1 to S2:

mysql -uroot -p sakila < /tmp/sakila.sql

At this moment we need to obtain S1 binary log coordinates in order to start replicating from it.

Run ‘SHOW MASTER STATUS’ on S1 to get them:

mysql> SHOW MASTER STATUS;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000033 | 743915402 |              |                  |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec) 
mysql>

Provide master information to S2:

CHANGE MASTER TO MASTER_HOST='slave1.example.com', MASTER_USER='replica_user', MASTER_PASSWORD='password';

Providing S1 binary log infromation to S2:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000033', MASTER_LOG_POS=743915402;

Starting replication on S2:

START SLAVE;
SHOW SLAVE STATUSG;
mysql> SHOW SLAVE STATUSG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: slave1.example.com
Master_User: replica_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000033
Read_Master_Log_Pos: 747231154
Relay_Log_File: relay-bin.000097
Relay_Log_Pos: 747231299
Relay_Master_Log_File: mysql-bin.000033
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: sakila
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 747231154
Relay_Log_Space: 747231494
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

Now we can start slave threads on S1 as well:

START SLAVE;
SHOW SLAVE STATUSG;

What to monitor with replication

The most important variables are Slave_IO_Running, Slave_SQL_Running and Seconds_Behind_Master. Two first variables should be equal to ‘Yes’, and Seconds_Behind_Master should be 0 (not NULL!) or near to it. These variables indicate that both replication threads are running and slave is up to date with master.
If all variables are correct on both slaves, you can start using your replication.

Future reading

MySQL Recipes: Promoting a Slave to Master or Changing Masters

Switching Masters During Failover