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

Creating a simple Alert / Success Message with ASP.NET/VB using Bootstrap