Synchronize MySQL Data Using SQLyog Job Agent

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.