Using Unison to Take MySQL Copy

Recently I needed to take a copy of a mysql database dump on a regular basis from a remote CentOS database server hosted in a data centre in the UK, on to a Windows Server based in Ireland also running a similar version of MySQL.

We require a copy of the data on an hourly basis

So I decided to try out unison to synchronize folders between a remote server and local server

Windows Server

  • Firstly download unison from this located at
    http://www.cis.upenn.edu/~bcpierce/unison/download.html

    When I downloaded the latest binary option for Windows you get two files, one is the text/command line tool and the other is an application based on gtk.

    I took a copy of the command line application, created a folder called c:\unison an dropped my file in to that folder.
  • You will also need to grab a copy of an ssh client that will run under windows. you can go with something like cygwin but I wanted something that was quick to install and had a small foot print so I selected

    OpenSSH for Windows
    http://sshwindows.sourceforge.net/
    Rather then use the standard program files folder I dropped this in to c:\openssh just to make life easier for myself

    Next I went in to
    c:\openssh\bin\ and ran ssh-keygen -t rsa

    I asked ssh-keygen to create a new public/private key but to save it to a new directory e.g. c:\keys\id_rsa and c:\keys\id_rsa.pub

Remote CentOS  Server

  • Then I copied the contents of c:\keys\id_rsa.pub up on to my remote unix server, amending to the end of /root/.ssh/authorized_keys
  • Next step I wrote a little batch script to run via cronjob to dump data from a mysql database on an hourly basis saving the compressed contents in to a file live_data.zip which will contain live_data.sql  This just saves bandwidth in terms of file synchronization 
  • I also had to install unison on the remote CentOS host so I was lucky and I just used
    yum install unison  /// you may need to download binaries or change your repos 

Windows Server

  • After this on the windows server I ran a command similar to this on an hourly basis

    unison -sshargs "-i c:\keys\id_rsa.pub" ssh://root@77.77.77.77//backups c:\backups




Comments

Popular posts from this blog

Basic Send Message to MQ with Java and IBM MQ JMS

Basic Receive Message to MQ with Java and IBM MQ JMS

Configure Database Connection using MyBatis