MySQL Replication :: Add a new slave server

replication-1This post explains how to add a new slave to the existing MySQL replication. New slave server can be added to the MySQL replication without stopping master or shutting down existing slave server. You will have to temporarily stop replication on the slave server in order to take the current data snapshot. On the other hand, if you need to set up a new MySQL replication, please read HERE

1. Stop replication on the existing slave

To clone a new slave server, it is necessary to temporary stop replication on the existing slave. Why? Because you will have to take a data snapshot. Connect to the slave using MySQL client and run the following command:

# (existing slave server) pause replication
mysql> show slave status\G
mysql> STOP SLAVE;
mysql> show slave status\G

Existing slave is only paused and every change from the master will wait until you start replication with slave startcommand. Slave server will normally execute SQL requests and if your data changes are not so frequent, no one will notice that something is happening in the background. With show slave status you will see the status report before and after slave is stopped.

2. Export databases from the existing slave
After existing slave is paused, you can take a data snapshot. With mysqldump, database (or a collection of databases) can be exported to the file.

# (existing slave server) export database to the file
bash> mysqldump -p -uroot my_database > /tmp/my_database.sql

Before running mysqldump, be sure you have enough disk space. Move my_database.sql to the new slave server using scpftpwget …

3. Copy and from existing to the new slave server
A slave replication server creates two additional small files in the data directory. These status files are named and by default. contains relay_log file name, relay_log position, corresponding master log name and corresponding master log position, while contains parameters to connect to the master server: server address, user to connect to the master server (with plain text password), port … Here is example how you can copy and to the new slave server:

# (existing slave server) copy and files
bash> scp   /var/lib/mysql/   new_slave_server:/var/lib/mysql
bash> scp   /var/lib/mysql/      new_slave_server:/var/lib/mysql

Don’t forget to change owner and group for and to “mysql” user. If files have been copied as “root” user then mysqld server will not have write permission and replication will not work. Here is output from/var/log/mysqld.log file in that case:

[ERROR] /usr/libexec/mysqld: File '/var/lib/mysql/' not found (Errcode: 13)
[ERROR] Failed to open the existing master info file (file '/var/lib/mysql/', errno 13)
[ERROR] Failed to initialize the master info structure

So the trick is to set file owner and group to the “mysql” user and restart mysqld server.

bash> chown mysql.mysql
bash> chown mysql.mysql

4. Start replication on the existing slave
Data snapshot, and are copied to the new slave server. Now you can start replication on the existing slave.

# (existing slave server) start replication
mysql> show slave status\G
mysql> show slave status\G

New slave server can wait because it will retrieve changes from the point written in its file.

5. Import MySQL dump to the new slave server and stop mysqld
You have already move MySQL dump to the new slave server. To import data to the MySQL database, use mysqlclient.

# (new slave server) import database from the file
bash> mysql -p -u root my_database < /tmp/my_database.sql

After data import is finished, stop mysqld because you will need to change and create /etc/my.cnf file. I will just mention that you can run data import from the existing slave directly – without coping first, but you will have to grant access from the existing slave server. The only difference is a -h switch.

# (existing slave server) import database from the file
bash> mysql -p -u root -h new_slave_server my_database < /tmp/my_database.sql

6. Edit file
As said before, contains relay_log file name. By default, relay log file names have the form host_name-relay-bin.nnnnnn, where host_name is the name of the slave server host and nnnnnn is a sequence number. The new slave server will have a new name and that change should be included. Open file and edit the first line.

# (new slave server) change relay_log file name in

# first line before change

# first line after change

You can reset sequence number because mysqld will recreate relay_log after starting up. If you get the following errors after starting the new slave server:

  • Failed to open the relay log
  • Could not find target log during relay log initialization

you are probably entered a wrong host_name.

7. Add grant on the master server
Each slave must connect to the master using a standard MySQL user name and password, so there must be a user account on the master that the slave can use to connect. In this scenario with existing slave, user for replication already exist on the master server. You will only have to set additional grant. Let’s assume that user name for replication is replica:

# (master server) add grant to replica user to connect from the new slave server
mysql> grant replication slave on *.* to 'replica'@'new_slave_name_or_ip' identified by 'password';

With this grant, replica user could only request updates that have been made to databases on the master server – nothing more. Without applying grant for newly added slave server, the following error message will be displayed in /var/log/mysqld.log file:

[Warning] Slave I/O: Master command COM_REGISTER_SLAVE failed: failed registering on master, reconnecting to try again, log 'mysql-bin.001042' at position 468289276, Error_code: 1597
[ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user 'replica'@'' (using password: YES) (Errno: 1045), Error_code: 1597
[ERROR] Slave I/O thread couldn't register on master

8. Copy my.cnf and increment server-id
On the master and each slave, you must use the server-id option to establish a unique replication ID. For each server, you should pick a unique positive integer, and each ID must be different from every other ID. Copy/etc/my.cnf from the existing slave to the new slave and edit server-id line. In my example, master is server-id=1and the first slave is server-id=2, so I set server-id=3 to the new slave server.

9. Start a new slave server

# (new slave server) start replication
bash> /etc/init.d/mysqld start
# or
bash> service mysqld start

If all went according to this cookbook, in /var/log/mysqld.log, you should find:

  • Slave SQL thread initialized, starting replication in log …
  • Slave I/O thread: connected to master ‘replica@your_master:3306’, replication started in log …

… means that you have successfully add a new slave server.

About NhocConan

A super lazy guy who tries to write tech blog entries in English.He is lazy, so he can only write when he is in a good mood or when he is tired of coding.

Leave a comment

Your email address will not be published. Required fields are marked *