Add a Slave by Cloning an Existing Slave

Background

One MySQL server can be the replication master to lots of slaves.

Having lots of slaves can be useful for scaling up your ability to handle lots of read traffic. The master replicates the same data out to all of the slaves, and you can load balance read traffic across all the slaves.

Note that this kind of replication doesn't help at all with scaling up your write capacity: the slaves shouldn't accept changes, because those changes won't propagate back to the master or to the other slaves. (Remember, replication is one-way.)

To Establish Replication we built our first slave using a backup from an existing master. That's not always practical, especially if the master is under heavy load.

In this procedure, we'll create a second slave using an existing slave. This is typical in very large deployments, especially if the slaves are all reached through a load balancer that can gracefully handle one slave being temporarily taken out of service.

Steps

Test Replication to the Old Slave

The server old_slave is already replicating from the server master.

Connect to master.example.com.

Test replication by logging into MySQL and inserting 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 = "Replicating from master to old_slave";
Query OK, 1 row affected (0.03 sec)

mysql>

Connect to old_slave.example.com.

Verify that the row you inserted has been replicated to old_slave.

old_slave ~ $ mysql -u root
mysql> select * from important.stuff;
+----+--------------------------------------+---------------------+
| id | details                              | happened            |
+----+--------------------------------------+---------------------+
|  1 | Replicating from master to old_slave | 2013-04-15 22:19:07 |
+----+--------------------------------------+---------------------+
2 rows in set (0.00 sec)

mysql>

After this step is completed:

INSERT a new record on master in the table important.stuff
Within 1 second, that record is replicated to old_slave

Stop MySQL on the Existing Slave

Still on the old_slave, stop the MySQL process.

old_slave ~ $ sudo service mysqld stop
Stopping mysqld:                                           [  OK  ]
old_slave ~ $

After this step is completed:

MySQL is not running on old_slave
MySQL is still serving read and write traffic on the master

Back up MySQL's Data Directory

In other procedures, like Establishing Replication, we use mysqldump to take a consistent backup of the master while it's running. That is a good way to minimize downtime on the master, and works well in situations with light utilization, and especially when no slaves are available.

However, on masters with heavy I/O utilization, it may not be practical to ever run a full backup on the master—that's one of many tasks read-slaves are particularly good at offloading.

Here we're doing an off-line backup of the data files on the slave. This is extremely fast, because MySQL doesn't have to serialize all the data into an plaintext file full of SQL statements. It will also capture all the replication settings from this slave and speed up establishing the new slave.

Note that MySQL should always be stopped when you perform this kind of backup. Copying MySQL data files while the server is running is very likely to produce a corrupted backup if not all data had been flushed from memory to file.

old_slave ~ $ sudo tar -czf /tmp/slave.tar.gz -C /var/lib/mysql/ .
old_slave ~ $

After this step is completed:

The backup file exists on old_slave at /tmp/slave.tar.gz

Start MySQL

We still want to minimize downtime for the existing slave, so lets put it back in service before we move on to work on the new slave.

old_slave ~ $ sudo service mysqld start
Starting mysqld:                                           [  OK  ]
old_slave ~ $

After this step is completed:

MySQL process is running on old_slave

Copy the Backup to the New Slave

old_slave ~ $ scp /tmp/slave.tar.gz new_slave.example.com:/tmp
The authenticity of host 'new_slave.example.com (10.119.97.142)' can't be established.
RSA key fingerprint is 5d:37:a8:8c:88:e1:87:76:0c:92:33:c3:9b:6c:2f:ad.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'new_slave.example.com,10.119.97.142' (RSA) to the list of known hosts.
you@new_slave.example.com's password: (input your password)
slave.tar.gz                                100%  579KB 578.7KB/s   00:00
old_slave ~ $

After this step is completed:

The backup file exists on new_slave at /tmp/slave.tar.gz

Log into the New Slave

The server new slave will use the backup from old_slave to start replicating from master.

Configure the Server for Replication

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

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

Note that the master uses server_id of 10 and the existing slave uses 20. This new slave's value needs to be non-zero and unique, we've chosen 30.

new_slave ~ $ 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:

new_slave ~ $ sudo service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
new_slave ~ $

After this step is completed:

On new_slave, /etc/my.cnf sets server_id to 30
On new_slave, /etc/my.cnf sets log_bin to mysql-bin
On new_slave, /etc/my.cnf sets log_slave_updates to 1
On new_slave, /etc/my.cnf sets relay_log to mysql-relay-bin
On new_slave, /etc/my.cnf sets read_only to 1
On new_slave, the MySQL variable server_id has value 30
On new_slave, the MySQL variable log_bin has value ON
On new_slave, the MySQL variable log_slave_updates has value ON
On new_slave, the MySQL variable relay_log has value ON
On new_slave, the MySQL variable read_only has value ON

Stop MySQL

new_slave ~ $ sudo service mysqld stop
Stopping mysqld:                                           [  OK  ]
new_slave ~ $

After this step is completed:

MySQL is not running on old_slave

Apply the Backup and Start MySQL

This will overwrite everything in the new_slave's MySQL data directory with the contents we copied from the old_slave. Because this is a brand new server, that's fine.

new_slave ~ $ sudo tar -zxf /tmp/slave.tar.gz -C /var/lib/mysql/
new_slave ~ $ sudo service mysqld start
Starting mysqld:                                           [  OK  ]
new_slave ~ $

After this step is completed:

MySQL process is running on new_slave
MySQL on new_slave has the database important
MySQL on new_slave has the table important.stuff

Check Replication Status

If all has gone well, the new_slave will start replicating immediately from master. Let's look at how the new_slave reports its replication status.

new_slave ~ $ mysql -u root
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: 1306
               Relay_Log_File: mysql-relay-bin.000004
                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: 1306
              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:

On new_slave, MySQL shows the replication setting MASTER_HOST has value master.example.com
On new_slave, SHOW SLAVE STATUS shows Slave_IO_Running is Yes
On new_slave, SHOW SLAVE STATUS shows Slave_SQL_Running is Yes

Test Replication to the New Server

Let's test that changes on the master are replicated to new_slave.

If you've closed your connection, please SSH to the master, master.example.com

From the master, log in to MySQL and insert a new record.

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 = "Replicating from master to both slaves";
Query OK, 1 row affected (0.03 sec)

mysql>

The row you inserted should show up on both old_slave and new_slave server almost immediately.

mysql> SELECT * FROM important.stuff ORDER BY id DESC LIMIT 1;
+----+----------------------------------------+---------------------+
| id | details                                | happened            |
+----+----------------------------------------+---------------------+
|  5 | Replicating from master to both slaves | 2013-04-15 22:34:39 |
+----+----------------------------------------+---------------------+
1 row in set (0.00 sec)

mysql>

After this step is completed:

On master, INSERT a new record in the table important.stuff
Within 1 second, that record is replicated to old_slave
Within 1 second, that record is replicated to new_slave