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:
log_slave_updates is set, copying the applied events to the slave's binary logA 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.
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>
important.stuffStill 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 shownBack 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 ~ $
SHOW SLAVE STATUS shows Slave_IO_Running is NoSHOW SLAVE STATUS shows Slave_SQL_Running is No/tmp/backup_for_relay.sqlThe 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 ~ $
/etc/my.cnf sets server_id to 30/etc/my.cnf sets log_bin to mysql-bin/etc/my.cnf sets log_slave_updates to 1/etc/my.cnf sets relay_log to mysql-relay-bin/etc/my.cnf sets read_only to 1server_id has value 30log_bin has value ONlog_slave_updates has value ONrelay_log has value ONread_only has value ONImport 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 shownThe 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>important.stuff exists.important.stuff uses the storage engine blackhole.SHOW SLAVE STATUS shows Master_Host is master.example.comSHOW SLAVE STATUS shows Slave_IO_Running and Slave_SQL_Running are both YesNow 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>SHOW SLAVE STATUS shows Master_Host is relay.example.comSHOW SLAVE STATUS shows Slave_IO_Running and Slave_SQL_Running are both YesTo 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>
important.stuffimportant.stuff will continue to be empty (blackhole).