Automatic replication of MySQL databases with Rsync

In some posts   I have written about replicating Business Intelligence information to Local Area Network satellites.

Instead of using normal database backup procedures that guarantees the integrity of the database I use rsync and file copy the database from the source database server over to the target database server. I can do this since I know no updates are done to the database while replicating and I use MySQL MyISAM storage engine. My rsync procedure is very simple, fast and self-healing, but

Do not try this at home

The real reason why I replicate this way is - I like to experiment and try new things and I have not seen anyone replicate databases like this before.  

The Setup

This is how I have set it up. From the controlling ETL server I issue commands via ssh  to the source and target systems:

           Source system           Target system

1        Flush tables

2                                                 Stop MySQL

3        Run Rsync_repl.sh

4                                                Start MySQL.

I use ssh from the ETL server (where my Job scheduler runs) and issue the commands from a job.

First I need to set up SHH (control server):

ssh-keygen

ssh-copy-id -i ~/.ssh/id_rsa.pub userid@targetDBserver

and then sudo (Target server/BI Satelite):

visudo  (add)

MYSQLADM ALL = NOPASSWD: /usr/sbin/service

and then test it:

ssh -t userid@targetDBserver sudo service mysql status

from the control server. You should receive mysql status from the target database server with any prompts for password.

In the source database server I did almost the same thing. First SHH (control server):

ssh-copy-id -i ~/.ssh/id_rsa.pub userid@sourceDBserver

and then sudo (Source server/BI Master):

User_Alias MYSQL_REPL = userid

MYSQL_REPL ALL=(ALL) NOPASSWD:/path2/rsync_repl.sh *

replicate.sh * is a bash script (appended below) that rsync Mysql databases to the target database server. Now I have all things in place and I can system test from my control server

ssh -t userid@targetDBserver sudo service mysql stop

ssh -t userid@sourceDBserver sudo rsync_repl.sh

ssh -t userid@targetDBserver sudo service mysql start

The automation.

With everything in place and tested, I only have to create a job and schedule it.

<?xml version='1.0' encoding='UTF-8' standalone='yes'?> <job name='replicateDB' type='sql'> <!-- This job replicate databases from Source Host to Target Host --> <!-- Note of Warning! This is not according to any safe procedure. Do not try this at home! -->   <tag><name>TargetHost</name><value>TIPaddr</value></tag>   <tag><name>TargetUser</name><value>Tuserid</value></tag>   <tag><name>SourceHost</name><value>SIPaddr</value></tag>   <tag><name>SourceUser</name><value>Suserid</value></tag>     <sql>FLUSH TABLES</sql>   <exit>     <!--Action 1:  stop mysql in target server -->     <!--Action 2:  replicate from Source to Target -->     <!--Action 3:  start mysql in target server -->     <action wait='yes' cmd='ssh' parm='-t @TargetUser@@TargetHost sudo service mysql stop'/>     <action wait='yes' cmd='ssh' parm='-t @SourceUser@@SourceHost sudo /path2/rsync_repl.sh'/>     <action wait='yes' cmd='ssh' parm='-t @TargetUser@@TargetHost sudo service mysql start'/>   </exit> </job>

As a safety measure this job first flush MySQL tables to disk and then runs the exit actions. And that’s it.

If which God forbid the replicated database is trashed, I just have to run the job again. You can guarantee the integrity of the database by running the job repeatedly until no data is replicated, basically if this replication is faster than the ‘update rate’ your database will be fine.  

 I conclude this series of posts  with the rsync_repl.sh script. The comments say it all ‘hopefully the replicated database is OK’ no guarantees!

p.s.

You can make this procedure secure by take a table lock before the second replicate, but in my case it is not necessary.