By: Karam Chand

February 9th, 2004

Reader Rating: 8.5

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 [1]

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 [2]. 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.

The next page of the wizard allows you to select the tables you want to sync.

Selecting [ALL]… will sync all the tables that are present in the database. If you don't want to sync all the tables, select each table individually without selecting the [ALL]… option. You can even change the order in which tables will be synced using the "Move Up…" and "Move Down…" options.

Additionally, you can configure SJA to detect changes only for specific columns. This makes it an ideal tool to use to sync data, even if there is limited bandwidth. Since SQLyog uses checksums to detect changes, unselecting BLOB data may significantly speed up the process, as these columns will not be used to generate checksums. To select specific columns, check the corresponding table and select the columns.

Clicking on the "SQL Where" button will open another window, where you can specify a valid SQL WHERE clause.

This is very helpful when you don't want to sync archived data.

The two options in Select Table's page are:

• Abort Synchronization On Error – Checking this option aborts the sync operation whenever it comes across a problem. If you leave it unchecked, SQLyog will continue with the sync process. Otherwise, it will stop at the first occurrence of an error.
• Set FOREIGN_KEY_CHECKS=0 for Target – If this option is checked, SQLyog issues the SET FOREIGN_KEY_CHECK=0 command before the sync on the target server. It allows you sync data without validating FOREIGN KEY integrity.

Selecting Synchronize Now... on the next page will start the synchronizing process. SQLyog starts up SJA as a child process to sync the databases. The complete detail of the sync process is shown in the last step of the wizard.

Running SJA from the command prompt

You can also execute SJA as a command line tool that takes an XML file containing the session detail. In Linux, the only way to use SJA is to run it from command prompt. You can execute SJA from command line with the XML file as a parameter. The syntax of SJA is:

sja  <jobfile> [–l<logile>]

sja supports the following options:

• -l<filename> - File where sja will log all the errors it has encountered while syncing databases. If no log file is specified, sja will create a default log file sja.log in the current directory of the executable, and will log all error messages in this file.

Scheduling Synchronization Process

In Windows, SQLyog uses the Windows Task Scheduler to schedule sync process. If you want to schedule it, just click on the Back button and select the Save and Schedule It... option.

You need to save the session details in an XML file before you can schedule it. Clicking on Finish button starts up the Windows Task Scheduler dialog.

Sample SJA Job File

Following are two sample SJA files containing the details of a sync job.

<job version="1.2">    
<abortonerror abort="no" />  
<fkcheck check="no" />  
<syncjob>  
<source>  
<host>123.123.0.1</host>  
<user>root</user>  
<pwd />  
<port>3306</port>  
<database>data</database>  
</source>  
<target>  
<host>localhost</host>  
<user>root</user>  
<pwd>complex</pwd>  
<port>3306</port>  
<database>localcopy</database>  
</target>  
<tables all="yes" />  
</syncjob>  
</job>

(Sample 1 : All tables and all columns)

<job version="1.2">  
<syncjob>  
<abortonerror abort="yes" />  
<fkcheck check="yes" />  
<source>  
<host>localhost</host>  
<user>root</user>  
<pwd></pwd>  
<port>3306</port>  
<database>mysql</database>  
</source>  
<target>  
<host>localhost</host>  
<user>root</user>  
<pwd></pwd>  
<port>3306</port>  
<database>new_mysql</database>  
</target>  
<tables all="no">  
<table>  
<name>`columns_priv`</name>  
<columns all="yes" />  
</table>  
<table>  
<name>`db`</name>  
<columns all="no">  
<column>`Host`</column>  
<column>`Db`</column>  
<column>`User`</column>  
</columns>  
</table>  
<table>  
<name>`host`</name>  
<columns all="yes" />  
<sqlwhere>host like '%%'</sqlwhere></table>  
</tables>  
</syncjob>  
</job>

(Sample 2 : Specific tables and columns with WHERE clause)

Points to Remember

SQLyog uses MySQL's concat_ws() function to generate checksums. This function is known to give different results across different versions of MySQL. SJA works best if both the Source and Target and running the same version of MySQL.

SQLyog checks for similarities in the structure and Primary Keys for both the tables. If they are not the same, SQLyog will skip the table during the sync process.

All information about the sync, including errors and other information, is stored in the log file, sja.log, found in the installation directory of SQLyog.

Conclusion

Syncing data between two servers can often be a full-time job, and tracking duplicate databases strewn across client sites, all of which undergo changes at different times, has the potential to be an administrative nightmare. Fortunately, SJA allows you to synchronize databases, even in an Occasionally Connected environment, without going through complex setup and installation tasks.

[1] http://www.webyog.com
[2] http://www.webyog.com