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
....
...
log-bin=/var/lib/mysql/db0-binary-log
expire-logs-days=7
server-id=1
...
....

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=/var/lib/mysql/db1-relay-log
relay-log-space-limit = 4G
read-only=1
server-id=2
...
...

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* --exclude=master.info --exclude=relay-log.info /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* --exclude=master.info --exclude=relay-log.info /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!