Setting up MySQL Master Slave Replication with rsync

This article is part of a series of setting up MySQL replication. As with most things, there is always more than one way to do something. In the case of setting up MySQL replication, or rebuilding it, some options are better than others depending on your use case.

The articles in the series are below:
Setting up MySQL Replication using mysqldump
Setting up MySQL Replication using Percona XtraBackup
Setting up MySQL Replication using Rsync
Setting up MySQL Replication using LVM snapshots

This guide will document how to setup MySQL Master / Slave Replication using Rsync. So why use Rsync for setting up or rebuilding MySQL Replication? If your databases and tables are large, but fairly quiet, you can limit the downtime felt by syncing over the majority of the content live with Rsync, then you perform a final Rsync during a scheduled maintenance window to catch any of the tables that may have changed by using a READ LOCK. This is also very useful when you do not have enough disk space available on db01 to perform a traditional backup using mysqldump or using Percona’s XtraBackup as the data is being rsync’ed directly over to db02.

This is a fairly simplistic method of setting of MySQL Replication, or rebuilding it. So in the sections below, we’ll configure the Master and Slave MySQL server for replication, then we’ll sync over the databases.

Setup the Master MySQL server

Configure the my.cnf as shown below:


Then restart MySQL to apply the settings:

# CentOS / RHEL:
[root@db01 ~]# service mysqld restart

# Ubuntu / Debian:
[root@db01 ~]# service mysql restart

Finally, grant access to the Slave so it has access to communicate with the Master:

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

Setup the Slave MySQL server

Configure the my.cnf as shown below:

relay-log-space-limit = 4G

Then restart MySQL to apply the settings:

# CentOS / RHEL:
[root@db02 ~]# service mysqld restart

# Ubuntu / Debian:
[root@db02 ~]# service mysql restart

Rsync the databases

For reference, the rest of this guide will refer to the servers as follows:

db01 - Master MySQL Server
db02 - Slave MySQL Server

On db02 only, rename the existing MySQL datadir, and create a fresh folder:

[root@db02 ~]# service mysqld stop
[root@db02 ~]# mv /var/lib/mysql /var/lib/mysql.old
[root@db02 ~]# mkdir /var/lib/mysql

On db01 only, perform the initial sync of data over to db02:

[root@db01 ~]# rsync -axvz /var/lib/mysql/ root@db02:/var/lib/mysql/

Now that you have the majority of the databases moved over, its time to perform the final sync of data during a scheduled maintenance window as you will be flushing the tables with a READ LOCK, then syncing over the data.

On db01 only, flush the tables with READ LOCK, and grab the master status information as we’ll need that later. It is critical that you do NOT exit MySQL while the READ LOCK is in place. Once you exit MySQL, the READ LOCK is removed. Therefore, the example below will run this in a screen session so it continues to run in the background:

[root@db01 ~]# screen -S mysql
[root@db01 ~]# mysql
(detach screen session with ctrl a d)

On db01 only, perform the final rsync of the databases to db02, then release the READ LOCK on db01:

[root@db01 ~]# rsync -axvz --delete /var/lib/mysql/ root@db02:/var/lib/mysql/
[root@db01 ~]# screen -dr mysql
mysql> quit
[root@db01 ~]# exit

On db02 only, remove the stale mysql.sock file, startup MySQL, configure db02 to connect to db01 using the information from the show master status command you ran on db01 previously, and start replication:

[root@db02 ~]# rm /var/lib/mysql/mysql.sock
[root@db02 ~]# rm /var/lib/mysql/auto.cnf
[root@db02 ~]# service mysqld start
[root@db02 ~]# mysql
mysql> CHANGE MASTER TO MASTER_HOST='10.x.x.x', MASTER_USER='repl', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='db01-bin-log.000001', MASTER_LOG_POS=1456783;
mysql> start slave;
mysql> show slave status\G
        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0

If those values are the same as what is shown above, then replication is working properly! Perform a final test by creating a test database on the Master MySQL server, then check to ensure it shows up on the Slave MySQL server. Afterwards, feel free to drop that test database on the Master MySQL server.

From here, you should be good to go! Just be sure to setup a monitoring check to ensure that replication is always running and doesn’t encounter any errors. A very basic MySQL Replication check can be found here:

Remote backups with rsnapshot

What type of backup strategy do you employ for your solution? Do you have backups within your datacenter, or are you utilizing your hosting providers backup infrastructure if one is available? These are both good starting points for preparing your solution for disaster.

Now, what do you have in place for remote backups? Remote backups are critical in the event something where to happen to your primary datacenter. What if there was a fire, or there was a major natural disaster that took out the datacenter?

Perhaps as a more common scenario, maybe your existing backup solution was having problems and you weren’t aware of it. When the time comes for needing to restore your backups, you find that they are corrupted and unusable. This happens more often then people think.

When you deploy a new solution, you make sure its redundant and highly available. It is important to also do the same with your backup architecture. Having an on-site backup allows you to perform a speedy recovery should something go wrong. Including an off-site backup solution allows you to plan for the worst case scenario, and also gives you the piece of mind that your data is stored outside of that datacenter, under your control.

When having solution architecture discussions with clients, I strongly encourage:
– Use all available backup solutions offered by the hosting provider
– Have an off-site backup solution that is managed by yourself or a different provider

You can never have enough backups. Your data took weeks, months, or sometimes even years to develop and fine tune. If there are concerns about how much will it cost to have a remote backup solution, here is a more important cost consideration: How much will it cost your business and reputation to rebuild all your website and database content from scratch?

As you can probably tell, I am very paranoid about my clients data. So now that I hopefully gave you some food for thought, I’ll show you one inexpensive way I like to perform remote backups for smaller solutions (Under 500G). Please keep in mind that there are many backup solutions available, this is just one of many different types of solutions I present as an option to my clients.

Welcome rsnapshot. Taken from their website,

"rsnapshot is a filesystem snapshot utility for making backups of local and remote systems.  Using rsync and hard links, it is possible to keep multiple, full backups instantly available. The disk space required is just a little more than the space of one full backup, plus incrementals. 

Depending on your configuration, it is quite possible to set up in just a few minutes. Files can be restored by the users who own them, without the root user getting involved. 

There are no tapes to change, so once it's set up, your backups can happen automatically untouched by human hands. And because rsnapshot only keeps a fixed (but configurable) number of snapshots, the amount of disk space used will not continuously grow.

Many of the more common questions such as, “How do I restore a backup?” are answered in their FAQ which is located here:

I strongly encourage you to review their documentation so you can decide if this software is good for your solution. I like this solution cause it essentially allows you to simply rsync or SCP the needed information from your remote backup server back to your production servers when you need it. There are no complicated tools required to get your critical data back on your solution.

So, what do you need to set this up? You simply need a Linux/UNIX based computer that is running offsite, maybe even at your office if it is in a secure location, and enough hard drive space to store your backups. Installation is quick and easy as I’ll outline below. For this example, I am using a Rackspace Cloud, CentOS 6 server with 2x 200G Cloud Block Storage volumes setup in a Raid 1, encrypted using LUKS, mounted under /opt/storage01. I outlined how to set this up in an older article:

My setup is a bit more elaborate, but again, I am just paranoid about data. A simple server with enough free hard drive space will work just as well. Just make sure it is in a secured location.


Without further ado, here is how I personally setup rsnapshot. Please note that you have to enable the EPEL repos on your server to yum install rsnapshot. You can enable the EPEL repo by:

CentOS 5

sudo rpm -Uvh epel-release-5*.rpm

CentOS 6

sudo rpm -Uvh epel-release-6*.rpm

Now, install rsnapshot:

yum install rsnapshot

The rest of our setup will take place in /etc/rsnapshot.conf. Make a quick backup of the configuration:

cp /etc/rsnapshot.conf /etc/rsnapshot.conf.orig

Modify the configuration to meet our needs:

vi /etc/rsnapshot.conf

Set the following to specify where you want your backups to be stored. I put in my preference, but you can change this to anything you like. Just be sure its in a location that is only accessible to root:

snapshot_root	/opt/storage02/snapshots/

Now uncomment cmd_ssh as we’ll be rsyncing over SSH:

cmd_ssh	/usr/bin/ssh

Define the backup intervals. Here is what I use:

interval        hourly  6
interval        daily   7
interval        weekly  4
interval        monthly 3

All that is left configure which remote servers you will be backing up. You will have to be sure that you setup SSH keys so rsnapshot can SSH into the remote servers without a passphrase.

As a side note, when backup up your databases, be sure to backup your MySQL dumps, (Or the dumps from whatever database software you are using). If you try to backup the live database, you will likely have severe corruption if you ever need to restore it.

For our example, I am backing up 2 servers:
– ( : /etc, /var/lib/mysqlbackup
– ( : /etc, /var/www, and excluding /var/www/

# (
backup  [email protected]:/etc/
backup  [email protected]:/var/lib/mysqlbackup/

# (
backup  [email protected]:/etc/
backup  [email protected]:/var/www exclude=file/big_log_file.log

Finally, setup the cron jobs:

crontab -e
0 */4 * * * /usr/bin/rsnapshot hourly
30 8 * * * /usr/bin/rsnapshot daily
55 8 * * 1 /usr/bin/rsnapshot weekly
15 9 1 * * /usr/bin/rsnapshot monthly

Test to ensure everything works accordingly:

/usr/bin/rsnapshot hourly

– Check the directory to ensure your content was saved:

ls /opt/storage02/snapshots/

– Check the log file to ensure there are no errors:

less /var/log/rsnapshot

Most importantly, you must check to ensure that your backup system is functionality properly pretty often. You will want to periodically test your backups, at least every 90 days, to ensure that your team is familiar with the process, and to ensure that everything is okay with your backups. Backups are not ‘set it and forget it’. Always verify your data’s integrity, otherwise you may have a really bad time the day to find you need to restore from backups!

Keeping multiple web servers in sync with rsync

People looking to create a load balanced web server solution often ask, how can they keep their web servers in sync with each other? There are many ways to go about this: NFS, lsync, rsync, etc. This guide will discuss a technique using rsync that runs from a cron job every 10 minutes.

There will be two different options presented, pulling the updates from the master web server, and pushing the updates from the master web server down to the slave web servers.

Our example will consist of the following servers: ( # Master Web Server ( # Slave Web Server ( # Slave Web Server

Our master web server is going to the single point of truth for the web content of our domain. Therefore, the web developers will only be modifying content from the master web server, and will let rsync handle keeping all the slave nodes in sync with each other.

There are a few prerequisites that must be in place:
1. Confirm that rsync is installed.
2. If pulling updates from the master web server, all slave servers must be able to SSH to the master server using a SSH key with no pass phrase.
3. If pushing updates from the master down to the slave servers, the master server must be able to SSH to the slave web servers using a SSH key with no passphrase.

To be proactive about monitoring the status of the rsync job, both scripts posted below allow you to perform a http content check against a status file to see if the string “SUCCESS” exists. If something other then SUCCESS is found, that means that the rsync script may have failed and should be investigated. An example of this URL to monitor would be is:

Please note that the assumption is being made that your web server will serve files that are placed in /var/www/html/. If not, please update the $status variable accordingly.

Using rsync to pull changes from the master web server:

This is especially useful if you are in a cloud environment and scale your environment by snapshotting an existing slave web server to provision a new one. When the new slave web server comes online, and assuming it already has the SSH key in place, it will automatically grab the latest content from the master server with no interaction needed by yourself except to test, then enable in your load balancer.

The disadvantage with using the pull method for your rsync updates comes into play when you have multiple slave web servers all running the rsync job at the same time. This can put a strain on the master web servers CPU, which can cause performance degradation. However if you have under 10 servers, or if your site does not have a lot of content, then the pull method should work fine.

Below will show the procedure for setting this up:

1. Create SSH keys on each slave web server:

ssh-keygen -t dsa

2. Now copy the public key generated on the slave web server (/root/.ssh/ and append it to the master web servers, /root/.ssh/authorized_keys2 file.

3. Test ssh’ing in as root from the slave web server to the master web server
# On web02

ssh [email protected]

4. Assuming you were able to log in to the master web server cleanly, then its time to create the rsync script on each slave web server. Please note that I am assuming your sites documentroot’s are stored in /var/www/vhosts. If not, please change the script accordingly and test!

mkdir -p /opt/scripts/
vi /opt/scripts/

# : Pull site updates down from master to front end web servers via rsync


if [ -d /tmp/.rsync.lock ]; then
echo "FAILURE : rsync lock exists : Perhaps there is a lot of new data to pull from the master server. Will retry shortly" > $status
exit 1

/bin/mkdir /tmp/.rsync.lock

if [ $? = "1" ]; then
echo "FAILURE : can not create lock" > $status
exit 1
echo "SUCCESS : created lock" > $status

echo "===== Beginning rsync ====="

nice -n 20 /usr/bin/rsync -axvz --delete -e ssh [email protected]:/var/www/vhosts/ /var/www/vhosts/

if [ $? = "1" ]; then
echo "FAILURE : rsync failed. Please refer to solution documentation" > $status
exit 1

echo "===== Completed rsync ====="

/bin/rm -rf /tmp/.rsync.lock
echo "SUCCESS : rsync completed successfully" > $status

Be sure to set executable permissions on this script so cron can run it:

chmod 755 /opt/scripts/

Using rsync to push changes from the master web server down to slave web servers:

Using rsync to push changes from the master down to the slaves also has some important advantages. First off, the slave web servers will not have SSH access to the master server. This could become critical if one of the slave servers is ever compromised and try’s to gain access to the master web server. The next advantage is the push method does not cause a serious CPU strain cause the master will run rsync against the slave servers, one at a time.

The disadvantage here would be if you have a lot of web servers syncing content that changes often. Its possible that your updates will not be pushed down to the web servers as quickly as expected since the master server is syncing the servers one at a time. So be sure to test this out to see if the results work for your solution. Also if you are cloning your servers to create additional web servers, you will need to update the rsync configuration accordingly to include the new node.

Below will show the procedure for setting this up:

1. To make administration easier, its recommended to setup your /etc/hosts file on the master web server to include a list of all the servers hostnames and internal IP’s.

vi /etc/hosts web01 web02 web03

2. Create SSH keys on the master web server:

ssh-keygen -t dsa

3. Now copy the public key generated on the master web server (/root/.ssh/ and append it to the slave web servers, /root/.ssh/authorized_keys2 file.

4. Test ssh’ing in as root from the master web server to each slave web server
# On web01

ssh root@web02

5. Assuming you were able to log in to the slave web servers cleanly, then its time to create the rsync script on the master web server. Please note that I am assuming your sites documentroot’s are stored in /var/www/vhosts. If not, please change the script accordingly and test!

mkdir -p /opt/scripts/
vi /opt/scripts/

# - Push site updates from master server to front end web servers via rsync

webservers=(web01 web02 web03 web04 web05)

if [ -d /tmp/.rsync.lock ]; then
echo "FAILURE : rsync lock exists : Perhaps there is a lot of new data to push to front end web servers. Will retry soon." > $status
exit 1

/bin/mkdir /tmp/.rsync.lock

if [ $? = "1" ]; then
echo "FAILURE : can not create lock" > $status
exit 1
echo "SUCCESS : created lock" > $status

for i in ${webservers[@]}; do

echo "===== Beginning rsync of $i ====="

nice -n 20 /usr/bin/rsync -avzx --delete -e ssh /var/www/vhosts/ root@$i:/var/www/vhosts/

if [ $? = "1" ]; then
echo "FAILURE : rsync failed. Please refer to the solution documentation " > $status
exit 1

echo "===== Completed rsync of $i =====";

/bin/rm -rf /tmp/.rsync.lock
echo "SUCCESS : rsync completed successfully" > $status

Be sure to set executable permissions on this script so cron can run it:

chmod 755 /opt/scripts/

Now that you have the script in place and tested, its now time to set this up to run automatically via cron. For the example here, I am setting up cron to run this script every 10 minutes.

If using the push method, put the following into the master web servers crontab:

crontab -e
# Datasync script
*/10 * * * * /opt/scripts/

If using the pull method, put the following onto each slave web servers crontab:

crontab -e
# Datasync script
*/10 * * * * /opt/scripts/

Full Server Rsync Migrations

There are times when you need to do a one to one server migration.  This can seem like a daunting task, however with the magic of rsync, performing full server migrations can be a fairly painless task requiring little downtime.


On both the old and new servers, you want to ensure the following requirements are met:

1. Confirm both the old and new servers are using the same hardware architecture. You cannot perform an rsync migration if one server is 32-bit, and the other is a 64-bit system. This can be verified by running the following command, and checking to see if it has “i386”, which means 32-bit, or if both have “x86_64”, which stands for 64-bit.

uname -a
Linux demo 2.6.18-308.el5xen #1 SMP Tue Feb 21 20:47:10 EST 2012 x86_64 x86_64 x86_64 GNU/Linux

So in our example, I have verified that both the old and new servers are 64-bit.

2. Confirm they are both running the same exact version of the operating system. Normally this means simply confirming both servers are at the latest patch level which you can do by running:

yum update
cat /etc/redhat-release
CentOS release 5.8 (Final)

For this article, I will be using two servers that are running CentOS 5.8 (Final).

3. Confirm rsync is installed on both servers

yum install rsync

4. Clean up server before migration. Depending on the amount of files on your server, the initial rsync migration can take quite a while. So you will want to browse through your old server and remove any extraneous temporary, cache, or other large files that you no longer need. You should also check your logs and ensure that their sizes are reasonable, and archive or delete the older logs you no longer need.

5. If you are not going to be swapping IP’s, and simply updating DNS to point to the new server, confirm that all your domains on the old server have a very low TTL set in the zonefile. A TTL of 300 is usually considered the lowest acceptable TTL to set.

Begin server migration

The procedure I’ll be writing out below is a two step process. It is meant to help minimize the amount of downtime that is involved when you swap the IP’s or update DNS, assuming you have a low TTL set. The steps are below:
1. Perform initial rsync
2. Perform final rsync and ip swap (or DNS update)

The initial rsync is just used to get the majority of the static files over to the new server. The final rsync is meant to update anything that is dynamic, such as logs, updated web content, databases, etc.

So before we begin, you will want to create an excludes file on the old server. This file will tell rsync NOT to copy over system specific information that is not needed for your new system.

vi /root/rsync-exclude.txt

The example above should cover a couple of different distros. But always review someone else’s example before applying it to your own systems!!

Now that we have the proper excludes in place, lets run a dry run of rsync to see what would have happened before we actually do it. Please note that this is to run on the old server. Replace with the IP of the new server:

rsync -azPx -e ssh --dry-run -azPx --delete-after --exclude-from="/root/rsync-exclude.txt" / [email protected]:/

If all looks well, lets perform the actual initial rsync:

rsync -azPx -e ssh -azPx --delete-after --exclude-from="/root/rsync-exclude.txt" / [email protected]:/

Depending on how much data you have, this could take a few minutes, or many hours. Once this is complete, you will want to schedule a maintenance window to perform the final rsync and IP swap (or DNS update). You want to perform this during a maintenance window as you will need to stop any database services or anything else that has very dynamic data to avoid corruption. So in the example, I just have a basic LAMP server, so I will need to shut down MySQL before performing the final rsync. Here are the steps I’ll be using:
1. Stop MySQL on old server
2. Perform final rsync
3. On new server, reboot server and test everything
4. Swap IP from old server to new, or update your DNS accordingly.

On the old server:

service mysql stop
rsync -azPx -e ssh -azPx --delete-after --exclude-from="/root/rsync-exclude.txt" / [email protected]:/

Now we are ready to start testing our new server.

Testing And Go-Live

Lets wave that dead chicken over the alter, its time to see if your new server survives a reboot, and if all the services come back online properly. There are no guides for this. Just reboot your server, then test out your sites, databases, and keep a wary eye on your system logs.

Once you have confirmed that everything looks good, it will then be safe to swap the IP’s, or update DNS accordingly. In the event that a problem surfaces shortly after the migration, you always have the option of rolling back to your older server, assuming you won’t be losing any critical transactions.

Rsync Migration Guidelines

There are numerous gotcha’s and things that you must be aware of before you can confidently perform a migration. Migrations go bad all the time. There is no guranteed way of knowing when it will fail. But there are ways to minimize the potential for some problem creeping up a month after the migration. Outlined below are steps that should be taken before proceeding with the migration.

1. Evaluate the server to be migrated. This involves:
– How large are the drives?
– Are there any directories that have hundreds of thousands of files?
– Are there any directories that contain thousands of other directories?
– Is the server extremely busy?

2. Check your backups to ensure everything is in place
You must first determine how you are going to do the migration. Are you build a new server, then use the backups to do a server restore to it? Or are you just going to perform a straight rsync migration. If your going to attempt to utilize your system’s backups:
– Check to see when the last known good backup was. You need confirmation that its good, and also attempt to get a rough eta on how long a server restore will take.
– Setup a new server with the same EXACT specs as the orginal server.

3. Ask questions
Below are some basic questions that should be asked before performing a migration. It may help shed insight on the server’s day to day tasks to ensure a smooth migration. Things to ask:
– Are their any known quirks experienced from time to time on the server?
– What are the key critical services that need special attention when migrating?
– How can you test the server to ensure the migration was successful? ie. websites that can check that utilizies both apache and mysql
– When is a good time the server can be shutdown the services on the production box so the final rsync can be performed?

4. Perform phase 1 of 2 of the rsync migration
The goal here is to create a base system on the new server. You want to be able to get the majority of the data copied over. This is to minimize the downtime the public will have during the final rsync phase. There is no need to schedule this, this is safe to do whenever.
– If you are utilizing your backups, get the new server jumped, throw on a temp ip, and do a full server restore.
– If you are just going to use rsync for everything, be aware that the server may seem sluggish as rsync may eat up the system resources.

To perform the rsync, log onto the old server (the one currently in production), and start a screen session:

screen -S migrations

Create a shared key between the old server and the new server

cd / && exec bash
for i in `ls |grep -v 'proc|etc'` do rsync -axvz --delete-after -e ssh $i [email protected]:/; done

To disconnect from the screen session, just hit:

ctrl-a then hit d

Depending on how much data and what the data structure is, at best your looking at 3-5G per hour. To speed up, use a cross connect on the gig ethernet ports.

5. Final rsync

At specified time stop all services on the production machine except sshd. (its better to just drop to single user mode with networking). Then using the same screen session, type:

for i in s | grep -v proc' do rsync -axvz --delete-after -e ssh $i [email protected]:/; done

Once complete, reboot and wave dead chicken over alter. You will want to swap the ips after you verified the new server at least boots.

6. Testing

This involves:
– Confirm websites work properly
– Confirm you can send and receive email
– Confirm mysql is functioning
– Go through error logs and correct any problems.

7. Troubleshooting

If machine doesn’t boot, you may have to fix grub (redhat). Also make sure /etc/fstab and /boot/grub/grub.conf have the labels setup right, or just specify the device: ex. /dev/hda1