#chained replication

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//http://godfatherhost.com/cloud/index.html 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 STATUS\G

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 STATUS\G;

mysql> SHOW SLAVE STATUS\G; *************************** 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 STATUS\G;

What to monitor with replication