I am hosting lesterchan.net on ex-FRRO shared/reseller server, now it can be considered a dedicated server that I am sharing together with Michael Feng. I have also a shared hosting account with a dedicated IP sponsored by Vodien Internet Solutions (which has excellent and fast customer service by the way). The FRRO server is using DirectAdmin while Vodien uses cPanel.

I have been Googling for days to find a way to replicate my files and databases from DirectAdmin (FRRO) To cPanel (Vodien) on a daily basis, but unfortunately I can’t find a way to do it. So I decided to think out of the box/back to basic and not do use DirectAdmin to cPanel but rather use rsync and mysqldump from one server to another.

I stumbled upon this article by HowtoForge entitled, Mirror Your Web Site With rsync. I followed the tutorial closely and it works perfectly, my files are mirrored from FRRO to Vodien everyday at midnight.

Within the same article, there are links to How To Set Up Database Replication In MySQL and How To Set Up A Load-Balanced MySQL Cluster. But unfortunately these tutorials requires root access to both servers which I do not have.

I thought of another idea which is to use mysqldump (at FRRO) to backup the database to a folder which will be included in the mirroring using rsync. After the rsync runs, the backup file should already be copied over to Vodien. Over at Vodien, I will use mysql to restore the database. This is quite a tiring process.

23:50 – Run mysqldump at FRRO (backup database)
00:00 – Run rsync at Vodien (mirror files)
00:30 – Run mysql at Vodien (restore database)

Notice that I give the cron jobs a buffer of 10 minutes for the backup database to happen before rsync and 30 minutes for the rsync to takes place before restoring the database.

Still not satisfied with the efficiency, I continued Googling for better solutions and I found an article by DevShed entitled, Backing Up and Restoring Your MySQL Database, in particular this paragraph:

Moving Data Directly Between Databases
How would you like to replicate your present database to a new location? When you are shifting web hosts or database servers, you can directly copy data to the new database without having to create a database backup on your machine and restoring the same on the new server. mysql allows you to connect to a remote database server to run sql commands. Using this feature, we can pipe the output from mysqldump and ask mysql to connect to the remote database server to populate the new database. Let’s say we want to recreate the Customers database on a new database server located at 202.32.12.32, we can run the following set of commands to replicate the present database at the new server 

mysqldump -u sadmin -p pass21 Customers | mysql --host=202.32.12.32 -C Customers

And that is when I realized I can pipe the output of mysqldump of FRRO to the MYSQL server of Vodien. But before that can take place, you need to set the allowed host in Remote MYSQL in cPanel. In this case, I added the IP of FRRO as well as the hostname to the allowed host in Vodien’s cPanel.

I tested the MYSQL piping and it works perfectly! So now the cron jobs are reduced to just two.

00:00 – Run rsync at Vodien (mirror files)
00:10 – Run mysqldump at FRRO (pipe MYSQL database)

Here is my rsync cron job command that will run at midnight everyday:

/usr/bin/rsync -avz --delete -e "ssh -i /path/to/key/mirror-rsync-key" username@domain.com:/home/username/domain.com/public_html/ /home/username/public_html/

Here is my mysqldump cron job command that will run at 00:10 everyday:

/usr/bin/mysqldump --user="mysql_username" --password="mysql_password" --opt --all-databases | /usr/bin/mysql --host=192.168.1.1 --user="mysql_username" --password="mysql_password"

*UPDATE*

Uzyn suggested a more secured and faster way of “piping” the MYSQL database:

/usr/bin/mysqldump | gzip > /path/to/d.gz; ssh user@mirror "scp user@primary:/path/to/d.gz; mysql < gunzip -c d.gz"

What this command does is basically, gzip your database, ssh/scp it over to the backup server and gunzip it from there. It is quite similar to my rsync method mentioned previously.