How to Setup MySQL Master Slave Replication Using Rsync

There are lots of online tutorials for setting up a slave replication database include dumping the database on the master with mysqldump. If your databases and tables are large then mysqldump cam be very slow. This is also very useful when you do not have enough disk space available on the database server to perform a traditional backup using mysqldump or using Percona’s XtraBackup.

In this article I will show you how to setup MySQL Master Slave Replication using Rsync.

Setup MySQL Master Server

First of all edit your MySQL master’s my.cnf file as shown below.

# vim /etc/my.cnf

Restart MySQL service to apply the above setting:

On CentOS / RHEL

#service mysqld restart

On Ubuntu / Debian

# service mysql restart

Then grant access to the Slave so that slave can communicate with the master.

mysql> GRANT REPLICATION SLAVE ON *.* to 'repluser’@’10.x.x.x’ IDENTIFIED BY 'your_password';

Setup MySQL Slave Server

Now edit your Slave’s my.cnf file as shown below.

# vim /etc/my.cnf
relay-log-space-limit = 4G

Then restart MySQL service to apply the above setting:

On CentOS / RHEL

# service mysqld restart

On Ubuntu / Debian

# service mysql restart

Rsync the Databases

For example we have two databases servers:

db0 – MySQL Master Server
db1 – MySQL Slave Server

Now rename the the existing MySQL data directory on db1 and create a new folder.

# service mysqld stop
# mv /var/lib/mysql /var/lib/mysql-old
# mkdir /var/lib/mysql
# chown mysql:mysql /var/lib/mysql

Now rsync the data from db0 to db1 as shown below. We rsync the MASTER binary data to the SLAVE. For this you’ll need to have root ssh access enabled. Depending on your setup, there are a few files you’ll have to exclude. For example, if you don’t want to overwrite the users on your slave, you’ll have to exclude the ‘mysql’ directory. You should experiment a bit with what you should and shouldn’t exclude.

# rsync --progress -axvz --delete --exclude=mastername* /var/lib/mysql/ root@db1:/var/lib/mysql/

Now we are going synchronized the bulk of the master data to the slave. Now we need another sync to put the slave in a valid state. We do this by locking the MASTER to prevent write actions.

# mysql -uroot -p
mysql> flush tables with read lock;
mysql> show master status;
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
| db0-bin-log.000001 |    15786 |              | mysql information_schema performance_schema |

Note down the value of the position filed and bin log file details, since we will need it later on the slave. At this time master is now locked against writes.

At this time Keep this MySQL session open and open the next rsync in a different terminal.

# rsync --progress -axvz --delete --exclude=mastername* /var/lib/mysql/ root@db1:/var/lib/mysql/

Once the final rsync is done now unlock the master. Go to your previous open terminal session and unlock the tables:

mysql> unlock tables;

Now the master (db0) is able to writes again. Now start MySQL service on Slave(db1) server.

# /etc/init.d/mysql start

On the slave(db1), we are going to change the master log position and binlog file and start replication:

# mysql
mysql> CHANGE MASTER TO MASTER_HOST='%', MASTER_USER='repluser', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='db0-bin-log.000001', MASTER_LOG_POS=15786;
mysql> start slave;
mysql> show slave status\G
        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0

If you get the same value as mention above, congratulation! your slave is now up and running!

Thank you! for visiting LookLinux.

If you find this tutorial helpful please share with your friends to keep it alive. For more helpful topic browse my website To become an author at LookLinux Submit Article. Stay connected to Facebook.

About the author


Santosh Prasad

Hi! I'm Santosh and I'm here to post some cool article for you. If you have any query and suggestion please comment in comment section.


  • Hey Santosh,
    This is about the clearest explanation I’ve found, well done man!

    I have a question,
    Our app runs DB on the same server, we want to create a mirror’d failover so we have setup our app and a database on server 2.

    Both app’s need to write to its own DB. Master will of course have the last working copy of the DB but if it fails our Slave becomes our new master and must write to the database for users to continue using it.

    Is Rsync appropriate for this?

Leave a Comment