This FAQ is intended to be a general Introduction to the 'SQLyog Job Agent' (SJA). The document was written to give a short but still comprehensive overview of the SJA. You will find more information including examples on the SQLyog help file and the Webyog website.
SQLyog Job Agent (SJA) is a high-performance, multithreaded, multi-platform application that lets you run maintenance scripts with your MySQL databases, have e-mailed formatted resultsets of a query, import data and metadata from ODBC-sources, synchronize MySQL databases and tables and do high-performance scheduled backups. On Windows, it is included with SQLyog Enterprise and SQLyog Ultimate. SJA for Linux is free for commercial and personal use.
SJA is a command line tool that accepts a Job Definition file encoded in XML as one of the parameters. 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. The Windows version also can be run from inside the SQLyog GUI, making its command line nature transparent to users if you want so.
On Windows platforms, SQLyog uses the Task Scheduler services to schedule your jobs. On Linux, you can use cron to schedule jobs. SJA can also be invoked by an application, no matter if it is a web page script (PHP, JSP, ASP) or a compiled binary.
SJA does not require any installation at hosts running the MySQL server. You can use any host to run the SJA. Technically SJA is a MySQL client that connects to any MySQL server in the world that allows for connections from a remote client. The client code that is used is the standard MySQL C-API compiled into the SJA executable. The Windows version of SJA further has the same advanced connectivity options as SQLyog Professional and higher: HTTP(S)-tunneling, SSH-tunneling and proxy authentication.
There are four job types available with the SJA:
1) SQL Scheduler and Reporting Tool (formerly 'Notifications Services')
2) SQLyog External Data Import Tool (formerly Migration Tool) (NOTE: this one is NOT available with the Linux SJA version)
3) MySQL data synchronization
4) SQLyog Backup Tool. Mostly used for ‘Scheduled Backup’
You can start SJA from SQLyog ‘Powertools’ menu or from command line. Command line syntax is
sja “jobfile.xml” –l”log.txt” –s”session.xml”
The -s and -l parameters for specifying the session file and the log file are optional. A complete file path can be specified (using the quotes is necessary if there are spaces or a ‘:’ in the path). That is useful if you don’t have write privilege to the installation folder. If not specified default values logfile.txt and session.xml are used and file are placed in the directory from where SJA runs.
Also SJA supports (since version 8.13) a '-v' (can also be written '--version') parameter. 'sja -v' and 'sja --version' will return the program version.
Data sync jobs additionally supports a '-r' parameter (it is ignored with other types of jobs). It has only an effect when a non-empty source-table is synced to an empty target-table and defines how big CHUNKS should be fetched from source server (for a (source) HTTP connection it is 1000 rows by default if -r option is not specified explicitly). -r2000 (note that the number should not be quoted) will copy CHUNKS of 2000 rows from source. If no -r parameter is specified SQLyog will fetch "all rows" from source server in one 'SELECT' query operation what may cause memory exhaustion on the client machine. Also if use (or no use) of the -r parameter results in larger CHUNKS than target 'max_allowed_packet' setting on target, this 'max_allowed_packet' will still be respected. So in short if your sync job syncs a large non-empty source-table to an empty target-table you could use the -r parameter to control memory consumption on the client. Note that the -r parameter is a command line option only that is not supported from the GUI wizard.
The jobfile will often be a file with an .xml extension (and the full file name must be written), but need not use that extension. Extension can be anything. Thus you may use the extension .sja for instance and associate the .sja extension with the SJA executable in the OS. Then you can execute all .sja –files with a single mouse-click.
To execute more SJA jobfiles and to execute SJA with other executables and system commands you can use the batch-processing facility of the OS (a .bat –file on Windows) or a script/an application.
What to use it for?
SJA basically lets you perform 4 types of MySQL database -related jobs:
1) SQL Scheduler and Reporting Tool.
Lets you run ANY SQL script against a MySQL database and lets you to have the result of ANY SELECT-query delivered to your mailbox as formatted HTML. To be used for all sorts of database maintenance, and any sorts of verification of data.
2 Import External Data Tool
Import Data and Metadata (all sorts or indexes – also Foreign Keys/relationships) from any ODBC-compliant data source. There are a broad variety of options available for mapping and filtering the imported data. Also the SQLyog Import External Data Tool includes a trigger-functionality to ‘write-back’- changes to the database imported from. Used in conjunction with the filtering options it can be used for incremental import. The SQLyog Import External Data Tool is the most flexible and versatile tool if you want to migrate your databases to MySQL or if need to have more database systems within an organization.
3) MySQL data synchronization.
To be used to synchronize data whenever you have two MySQL databases or database tables of identical structure. For example you can use SJA to keep your production databases (probably hosted with an ISP) in complete sync with your test database located in your PC or LAN.
You can configure SQLyog/SJA for One Way or Two Way synchronization. In one way synchronization, no modification is done in the source database. Two way synchronization allows you to transfer data from the source to the target and vice-versa. Additionally, you can configure SJA to detect changes only for specific rows and columns. For example you can exclude blob columns or include only those rows that fulfill a WHERE clause. This makes it an ideal tool to sync data even if there is limited bandwidth or limited server resources. SJA uses an efficient algorithm to generate checksums to find out the changes. Therefore, only those rows that have been inserted, updated or deleted since the last sync are transferred between the hosts.
4) SQLyog backup Tool
This tool is mostly used as a tool for Scheduled Backup. However you may of course too define a job and run it immediately. This backup tool has many options for selection of what should be backed up and how it shall be saved. It backs up all available information (if you choose) from the databases or tables selected: data, all sorts of indexes, MySQL ver 5 Objects, partition structure of partitioned tables (as of MySQL 5.1).
The SJA XML Schema
The SJA job file is a pure text file in a standard XML format. It specifies what the SJA must do. Everything is included between the <job> and </job> tags, that starts and concludes a SJA jobfile. Each of the four above-mentioned job types are identified by the tags <notifyjob>, <odbcimport>, <syncjob> and <exportjob>. Thus a job file for each of the four job types then looks like
| SQL Scheduler and Reporting Tool | Import External Data Tool | Data Synchronization | Backup |
|
<job version="4.1"> <notifyjob> ... </notifyjob> </job> |
<job version="4.1"> <odbcimport> ... </odbcimport > </job> |
<job version="4.1"> <syncjob> ... </syncjob> </job> |
<job version="4.1"> <exportjob> ... <exportjob> </job> |
The XML here represented by “…” can be a few lines only or it can be hundreds of lines. Refer to the examples and the help that ships with SQLyog, study the help file and try practicing it for yourself.