Synchronize MySQL Data Using SQLyog Job Agent

As MySQL developers, we are often required to keep two databases in complete sync with one another.

For example, imagine a client's Web store data is stored in his ISP's MySQL server, and he needs to make a local backup of the data every day. This can be achieved in one of the following ways:

• Use a scripting language like PHP to delete all the data from client's local database and insert it all again from the master server. This approach might work for some time but, as the size of data increases, the whole process slows to a crawl.
• Use MySQL's replication feature to replicate data between the two MySQL servers. This option is again not suitable, as ISPs don't give sufficient permissions on their MySQL servers to allow us to do this. Moreover, setting up replication is not an easy task

Thus, the question arises, what would be the best and most efficient method by which to synchronize data between two MySQL databases?

One solution is provided by SQLyog Job Agent (SJA). On Windows, it's available with the award-winning MySQL GUI SQLyog. The Linux version of SJA is free, and can be downloaded from webyog.com

In this article, we'll discuss how to set up and schedule Data Synchronization using SQLyog Job Agent for Windows.

Overview

SJA is a high-performance, multithreaded application designed to automate and schedule the synchronization of Data between two MySQL hosts.

SJA can also be used as a command line tool that accepts as one parameter a Job Definition file encoded in XML. You can either create the Job Definition file manually, or use one of the wizards included with SQLyog. If you use SQLyog to create your job files, you don't need to have any knowledge about XML or the Job Definition schema.

SJA doesn't require any installation at hosts already running the MySQL server. You can use any host to run the SJA. For example, you can use SJA to keep your production database (probably hosted with an ISP) in complete sync with a test database located on your PC or LAN.

SJA uses an efficient algorithm to generate checksums that identify the changes. Therefore, only those rows that have been inserted, updated or deleted since the last sync are transferred between the hosts.

Getting Started

You can get up and running with SJA using the Wizards available with SQLyog, or by running SJA from the command line. Linux users can only use the command prompt option.

Using SQLyog Wizard to Configure Your Sync Job

First of all, we need to install SQLyog. A 30-day trial version is available at webyog.com. After installation, start up the Database Synchronization Wizard found in the Tools menu. The first screen you will see is:

This page allows you start up a new synchronization session, or to edit an existing synchronization session.

Select "Start a new Synchronization Session", and click Next to start a new synchronization session.

On this screen, you need to provide details about your Source and Target database. The source database is the one that has the data you want to copy. The target database is the one that will contain the data that's synced with the source database. No modification is done in the source database. The extra rows present in the target database are deleted during the synchronization.