SQLyog Job Agent ( SJA ) tools delivers an extremely fast, powerful and cross platform solution to bring any two MySQL databases completely in sync with each other. Being a command line utility, your database synchronization jobs can be scheduled by the scheduling services of the OS.
If you are using SQLyog 3.63, you can start SJA using the wizard included with SQLyog. If you want to run it from the command prompt type:
sja <jobfile>
sja supports the following options -
-l<filename>
File where sja will log all the errors that it has encountered while syncing databases. If no log file is specified then sja will create a default log file sja.log in the current directory of the executable.
Note : It is recommended that you have the same version of MySQL in Source and Target server.
<jobfile> is an XML configuration file that contains details about the server, databases and tables that needs to be synced. This document describes the schema of the XML file in complete detail. You can either use SQLyog wizards or your favorite text editor to create the XML file.
Sample 1 ( All tables and all columns ):
<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 2 ( Specific tables and columns with WHERE clause )
<job version="1.0">
<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>
The schema object description tags are defined as follows:
<job version="1.0">
This is the root tag.
<syncjob>
Notifies the tool about the type of job.
<abortonerror abort="no" />
Specifying, NO for abort attribute stops the sja if it encounters any error.
<fkcheck check="yes" />
Specifying yes for check attribute tells sja to issue SET FOREIGN_KEY_CHECKS = 0. It allows you to sync data without validating FOREIGN KEY integrity.
<source>
It describes the connection parameters of the source MySQL. Data from the source is copied to the target. The source database is not changed. The source schema object can have the following sections:
<host>123.123.0.1</host>
<user>root</user>
<pwd />
<port>3306</port>
<database>data</database>
<target>
It describes the connection parameters of the target MySQL. The target database is the one whose data will be synced with the source database. All the properties are required.
<tables all="yes" />
The above element specifies whether to sync all the tables in the source database or specific tables. This section can have the following child tags:
<table>
This section allows you to select specific tables for syncing. It may be used in a schema description multiple times, once for each table that is to be synced. Note: The following steps are taken while syncing the tables.
- If a table from source database table does not exist in the target database, it is automatically created in the target database and data is inserted using standard insert delayedà syntax.
- If NO primary key is defined in the source table, syncing of the particular table is skipped.
- If the columns and primary key definition of the two tables does not match the table is skipped. The order of columns on multiple primary key should be exactly same.
The table section should have the following sections
<name>
The name of the table.
<columns all="yes" />
Use this section to specify whether to consider all columns or specific columns in a table for detecting changes. This option is useful when you want to update data in the target table if the value of some specific columns have changed.
It can have the following subsections:
<column>
If All attribute of Columns section is set to No, then you have to use this section to specify the columns. Note: If your column has spaces or it is a reserved word then you have to quote it with `.
<sqlwhere>
This section describes a valid SQL WHERE clause that can be used to ignore some particular rows while sync. For e.g. it is very useful if you don't have to sync old data, e.g column > 200 . Note: Don't put a WHERE before the query - sja will automatically put a WHERE before the clause.
========================== End of Document ====================================
