Add Slave to MySQL Replication Setup with no downtime

Add Slave to MySQL Replication Setup with no downtime

Adding a slave to an existing MySQL Replication setup can be tricky. Specially if you want to ensure there is no downtime for an abrupt failures or locks.

There can be number of different methods to achieve this, but I found this one working best in most cases that I experimented.

This scenario assume we are trying to setup a replication for fairly large database. Let’s say ~ 300G, this is purely assumption.

Add Slave to MySQL Replication Setup with no downtime – Preparation Work

There certainly would be some basics which I assume are done correctly. These would be mostly related to the version compatibility of MySQL. Difference in the revision numbers of the MySQL (for example 5.5.11 & 5.5.27) shouldn’t be a big of an issue. A difference in the minor version might need verification (for example 5.1.xx & 5.5.xx). A difference in the major version (for example 4.x.xx & 5.x.xx) would certainly indicate incompatibility.

Let’s do some preparation work before we actually begin the process. Copying over the database to the new server using mysqldump would definitely be not a good idea. Considering the fact the time taken to actually dump the database in a file, then transfer it over the network. If the potential slave is in a different physical location, then it would be over the internet. Then reading the dump and executing all the queries in the dump file.

Considering the I/O involved to write the dump, bandwidth speed over the internet and the I/O involved in reading and executing the SQL on the remote server, this process definitely will slow down the process in a big way.

Better option which is also recommended by MySQL Manual would be to copy the data directory for MySQL to the new slave. Once the data is transferred, you will have to adjust a few things and you should be good.

So to sum up the preparation part, I would say transfer the data directory to the new slave from an existing slave. Before beginning the actual setup process, stop the slave on the existing slave (which is used to get the files). And then run the rsync to get the latest data. Since we already have all the data transferred, rsync will copy the difference in the data directory since the last transfer of the data directory. We will discuss the rsync part of the process in more details later in the actual process.

Add Slave to MySQL Replication Setup with no downtime – The Process

1. On the new slave server, stop MySQL (if running)

[[email protected] ~]# /etc/init.d/mysqld stop

2. This step is completely optional. You can refer to read about the parameter sync_binlog here and decide for yourself.

On the existing slave, modify the my.cnf and add the following line under [mysqld] section.


Restart MySQL to enable this change.

[[email protected] ~]# /etc/init.d/mysqld restart

3. On the existing slave, check the slave status and ensure it is caught up with the master. Verify the line which says Seconds_Behind_Master:. It should say 0. Run the following on the MySQL prompt

mysql> show slave status\G;

4. After ensuring the slave on existing slave is caught up with master, stop the replication.

mysql> stop slave;

Verify that it is stopped.

mysql> show slave status\G;

5. Copy the my.cnf from the existing slave to the new slave. It should be the /etc/my.cnf unless you have a custom location set for it.

6. Start the final rsync that we discussed above. This will make sure that the new slave has most recent “replicated” data while the slave on the existing slave is stopped. I would ideally use screen to ensure an abrupt connection loss of my session doesn’t affect the transfer.

7. Once the data directory is sync’ed completely start slave on the existing slave.

mysql> stop slave;

Verify that the slave has started.

mysql> show slave status\G;

8. On the MySQL master server in the replication setup, create and assign the access for the replication user from the new slave.

If you are creating a new user for the new slave you can execute

mysql> CREATE USER 'new_slave_user'@'new_slave_host' IDENTIFIED BY 'password';

Ensure the user has replication privileges.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'new_slave_user'@'new_slave_host' IDENTIFIED BY 'password';

9. Back on the new slave host, We will have to ensure we rename the and You should find it under the data directory which you copied over from the existing slave. In my case I renamed it to and

10. Modify the my.cnf which was copied over from the existing slave. Increment the number provided in the server-id. You will however have to ensure that no other slave is using that identifier.

11. Add the following line in my.cnf under [mysqld] section., if not present.


This will ensure that when we start MySQL for the final setup, it does not start the slave along.

12. Start MySQL on the new slave.

[[email protected] ~]# /etc/init.d/mysqld start

13. Note the values of the old relay-log positions. We will read the file which we renamed above.

We should see some like below:


Note the first line and the second line in the output. The first line if the path to the binlog file which was being processed on the existing slave when we stopped it. You should have this file along with the data directory which we copied and rsync’ed. The second line is the postion in the binlog file which the slave was at.

14. Using the above binlog details we will set the binlog on the new slave. Run the following:

[[email protected] ~]# mysqlbinlog –start-position=123456 /path/to/relay-bin.145 | mysql

Make sure you replace the position value and the binlog file name with the one you find in your case.

15. We will get similar information for the master from You should see something like below.


Note the second and third line in this file. The second line should be the master binlog file which the existing slave was processing when we stopped it. You should have that file transferred as well. The third line is the position in the master binlog file which the slave was at.

16. Time to update the slave details on the new slave. On MySQL prompt, run the following:

mysql> CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='new_slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='bin.0487', MASTER_LOG_POS=789101;

Make sure you replace master_host with your master hostname or IP address, new_slave_user with the slave user you setup above on the master, password with the password assigned to slave user on master server, bin.0487 with your master bin log file found in and set the right master log position from the

That should setup the new slave. It will start replicating from the master starting from the position mentioned in the above SQL.

Add Slave to MySQL Replication Setup with no downtime

No Comments

Post a Comment

Time limit is exhausted. Please reload CAPTCHA.