Build a Replication Relay

Background

MySQL does not impose a limit on how many slaves can replicate from a single master. However, every slave the master serves does consume bandwidth, memory, and disk I/O. Given enough slaves, managing replication will start to impact the master's ability to do its primary job—handling write traffic.

At that time, a replication relay can take load off of the master. The relay replicates changes from the master, then slaves replicate from the relay. This introduces latency in the replication, but for large numbers of slaves it can dramatically improve the master's performance.

Normal replication works by:

  1. copying events from the master's binary log to the slave's relay log
  2. applying events from the slave's relay log to the slave's database
  3. if log_slave_updates is set, copying the applied events to the slave's binary log

A replication relay needs to complete steps 1 and 3 as quickly as possible to minimize latency. Because a relay is not necessary until there are a large number of slaves, typically, a relay does not serve any client SQL traffic itself.

That means that a replication relay can skip step 2, because the state of its own database doesn't matter.

We'll accomplish this with the blackhole storage engine. The relay will accept INSERTs, UPDATEs, and DELETEs from the master, but will not apply them to any local storage. Instead, the relay only copies them to its binary log, where they can be replicated to the slaves. Any SELECT run on the relay will return an empty set, because the updates were put in a black hole.

Steps

Test Replication to the Old Slave

The slave server is already replicating from the master server.

To test, connect to master.example.com and insert a new row.

master ~ $ mysql -u root
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> INSERT INTO important.stuff SET details = 'testing happy replication';
Query OK, 1 row affected (0.02 sec)

mysql>

Now SSH to slave.example.com and verify that the row you just inserted has been replicated.

slave ~ $ mysql -u root
mysql> SELECT * FROM important.stuff ORDER BY id desc LIMIT 1;
+-----+---------------------------+---------------------+
| id  | details                   | happened            |
+-----+---------------------------+---------------------+
| 296 | testing happy replication | 2013-04-12 14:09:26 |
+-----+---------------------------+---------------------+
1 row in set (0.00 sec)

mysql>
After this step is completed:
  1. INSERT a new record on the master in the table important.stuff
  2. Within 1 second, that record is replicated to the slave
Make a Backup of the Slave

Still on the slave, stop Replication, then make a note of the slave's current log file and position on the master.

slave ~ $ mysql -u root
mysql> slave stop;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: master.example.com
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 87738
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
......some content not shown

Back up the mysql database to copy account info (including the replicator user).

mysql> exit
slave ~ $ mysqldump -u root --single-transaction --flush-privileges --databases mysql > /tmp/backup_for_relay.sql 
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
slave ~ $ 

Back up your application database (in this lab, that's named important). We're backing up the table structure (as CREATE statements), but not any of the content, for reasons that will become apparent in a moment. We're using >> to append this backup to the same file we started above.

slave ~ $ mysqldump -u root --databases important --no-data >> /tmp/backup_for_relay.sql
slave ~ $ 

Now copy the backup to the relay server.

slave ~ $ scp /tmp/backup_for_relay.sql relay.example.com:/tmp/
The authenticity of host 'relay.example.com (10.212.77.129)' can't be established.
RSA key fingerprint is ff:93:60:f1:29:e2:cd:da:63:5c:81:4b:52:5a:47:f0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'relay.example.com,10.212.77.129' (RSA) to the list of known hosts.
you@relay.example.com's password: enter your password
backup_for_relay.sql                        100%  503KB 502.8KB/s   00:00
slave ~ $ 
After this step is completed:
  1. On the slave, SHOW SLAVE STATUS shows Slave_IO_Running is No
  2. On the slave, SHOW SLAVE STATUS shows Slave_SQL_Running is No
  3. On the relay, a backup file exists at /tmp/backup_for_relay.sql
Configure the Relay

The relay already has MySQL installed but not configured. This configuration is not part of the backup we pulled from the slave.

These settings, and the process to validate them yourself, are explained in the Establishing Replication procedure.

SSH to relay.example.com and edit /etc/my.cnf

relay ~ $ sudoedit /etc/my.cnf

Add these five lines to directly under [mysqld]

/etc/my.cnf
[mysqld] server_id = 30 log_bin = mysql-bin log_slave_updates = 1 relay_log = mysql-relay-bin read_only = 1 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock ......some content not shown

Now restart MySQL:

relay ~ $ sudo service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
relay ~ $ 
After this step is completed:
  1. On the relay, /etc/my.cnf sets server_id to 30
  2. On the relay, /etc/my.cnf sets log_bin to mysql-bin
  3. On the relay, /etc/my.cnf sets log_slave_updates to 1
  4. On the relay, /etc/my.cnf sets relay_log to mysql-relay-bin
  5. On the relay, /etc/my.cnf sets read_only to 1
  6. On the relay, the MySQL variable server_id has value 30
  7. On the relay, the MySQL variable log_bin has value ON
  8. On the relay, the MySQL variable log_slave_updates has value ON
  9. On the relay, the MySQL variable relay_log has value ON
  10. On the relay, the MySQL variable read_only has value ON
Add Data to the Relay

Import the backup on the relay. You will get dozens of lines of status messages. Scan them briefly to make sure there are no errors.

relay ~ $ mysql -u root
mysql> source /tmp/backup_for_relay.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
......some content not shown

The relay server needs an up-to-date schema for our application's databases and tables, but it does not need to keep a copy of the actual data. In effect, the relay's job is to copy events from the master's binary log (indirectly) into its own binary log. Then the slaves will copy events from relay's binary log into their relay logs, and apply them to their own databases.

It is not part of the relay's responsibility to directly handle any SQL queries. So, to speed up performance on the relay, we "blackhole" all data in the application tables. (In this lab, that's only important.stuff) We will also set the default storage engine for new tables to "blackhole".

mysql> ALTER TABLE important.stuff ENGINE = 'BLACKHOLE';
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> set global STORAGE_ENGINE = 'BLACKHOLE';
Query OK, 0 rows affected (0.00 sec)

The relay is now ready to become a master to slaves, so purge all binary log history before this point. When we swing slave to start replicating from the relay, it will start at binary log 1, position 1.

(This also makes sure that altering tables to use the 'blackhole' engine is not in any binary logs and could not be replicated to a slave accidentally.)

mysql> reset master;
Query OK, 0 rows affected (0.04 sec)

relay can now start receiving new events from the master. The MASTER_LOG_FILE and MASTER_LOG_POS values you collected from the slave in step 2.

mysql> CHANGE MASTER TO
MASTER_HOST='master.example.com',
MASTER_USER='replicator',
MASTER_PASSWORD='BondWhimLiarPull',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=87738;
Query OK, 0 rows affected (0.08 sec)

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master.example.com
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 87738
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          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: 87738
              Relay_Log_Space: 409
              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:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 10
1 row in set (0.00 sec)

mysql>
After this step is completed:
  1. On the relay, the table important.stuff exists.
  2. On the relay, the table important.stuff uses the storage engine blackhole.
  3. On the relay, SHOW SLAVE STATUS shows Master_Host is master.example.com
  4. On the relay, SHOW SLAVE STATUS shows Slave_IO_Running and Slave_SQL_Running are both Yes
Configure Slaves to Replicate from the Relay

Now we'll change slave to replicate from the relay.

slave ~ $ mysql -u root
mysql> CHANGE MASTER TO
MASTER_HOST='relay.example.com',
MASTER_USER='replicator',
MASTER_PASSWORD='BondWhimLiarPull',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1;
Query OK, 0 rows affected (0.08 sec)

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: relay.example.com
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          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: 107
              Relay_Log_Space: 409
              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:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 30
1 row in set (0.00 sec)

mysql>
After this step is completed:
  1. On the slave, SHOW SLAVE STATUS shows Master_Host is relay.example.com
  2. On the slave, SHOW SLAVE STATUS shows Slave_IO_Running and Slave_SQL_Running are both Yes
Test replication

To test, connect to master.example.com and insert a new row.

master ~ $ mysql -u root
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> INSERT INTO important.stuff SET details = 'master, relay, slave';
Query OK, 1 row affected (0.03 sec)

mysql>

We don't expect the data to show up on the relay:

mysql> SELECT * FROM important.stuff;
Empty set (0.00 sec)

The new row will replicate to the slave.

mysql> SELECT * FROM important.stuff ORDER BY id desc LIMIT 1;
+-----+----------------------+---------------------+
| id  | details              | happened            |
+-----+----------------------+---------------------+
| 302 | master, relay, slave | 2013-04-12 04:55:14 |
+-----+----------------------+---------------------+
1 row in set (0.00 sec)

mysql>
After this step is completed:
  1. On the master, INSERT a new record in the table important.stuff
  2. On the relay, important.stuff will continue to be empty (blackhole).
  3. On the slave, the record will be replicated within 1 second.