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