Send Critical MySQL Data to Your Users Automatically

any organizations are developing applications that provide information to users and employees when they need it?regardless of their locations. These types of applications, called notification applications, offer these organizations many beneficial uses, including the following:

• Sales manager can receive nightly notifications of the sales figures.
• Webmasters can track weekly hits.
• Product managers can get a list of resolved and unresolved bugs twice a week.
• Investors can receive notification whenever the price of a particular stock reaches a certain level.

SQLyog Job Agent (SJA), a high-performance, multithreaded application designed to automate and schedule MySQL database jobs, enables you to perform all the above tasks at regular intervals. For Windows, it’s available with the MySQL GUI, SQLyog. The Linux version of SJA is free, and can be downloaded from webyog.com.

In particular, SJA automates and schedules two types of jobs:

• Synchronization of data between two MySQL hosts
• Generation, formatting, and distribution of personalized emails with results from a query

You can also use SJA 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 from the command line, or use one of the wizards included with SQLyog. You don’t need to know anything about XML or the Job Definition schema to create your job files. Linux users can use only the command prompt option.

This article demonstrates how to set up and schedule SQLyog Notification Services using SJA for Windows.

SQLyog’s Notification Services Wizard
First of all, you need to install SQLyog. A 30-day trial version is available at webyog.com. After installation, start up the SQLyog Notification Services in the Tools menu. Figure 1 shows the first screen you will see.

Figure 1: Notification Services Wizard Startup

This page allows you to create a new notification services session or to edit an existing session.

Select Start a new Session and click Next to start a new email notification session.

Choosing What to Do with the Query
Figure 2 shows the page in which you specify what you want to do with the query.

Figure 2: Provide Information About What You Want to Do

Check Send query Result to an Email Address if you want to format and send result sets returned from queries to email addresses. If you want to include the query and any messages returned from the server, check the corresponding options.

Check Execute Maintenance Query(s) if you just want to execute some maintenance (such as ANALYZE, REPAIR, etc.) at scheduled intervals. (Note: If you specify any SELECT queries after checking this option, SQLyog Notification Services will ignore the result and won’t send any emails.)

Two other options are available for handling errors:

1. Exit Immediately tells the program to exit immediately on the first occurrence of an error.
2. Continue with next query tells the program to ignore the error and continue with the next query.

Click Next to specify your MySQL Connection Details.

Specify MySQL Connection Details
Figure 3 shows the MySQL Connection Details page, where you provide the following details about your MySQL server:

• Host: A host name where the database is located or the IP address of the server
• Username: A user name for connecting to the database server (Note: This is a MySQL username, not your FTP or Web server username.)
• Password: MySQL user password
• Port: A TCP/IP port for connecting to the database server
• Database: The database to which the server will connect

Figure 3: MySQL Connection Details

Click Next to specify email and outgoing server information.

Note: If you selected Execute Maintenance Query(s), you would be taken to the page where you have to specify the query(s).

Specify Email and Outgoing Server Information
Figure 4 shows the page where you provide the following information about the email address(es) of the recipients to whom you want to send the resultset(s):

• Name: Specifies the friendly name associated with your email address (When you send messages, this name appears in the From box of your outgoing messages.)
• To, CC, BCC: In the To, CC, or BCC boxes, type the email name of each recipient, separating names with a comma or a semicolon (;).
• From: Specifies the email address that people should use when sending email to you at this account (The email address must be in the format [email protected], for example, [email protected])
• Reply Address: Specifies that you want replies to your outgoing messages sent to a different email address, which you can type here (For example, you might send messages through [email protected] and specify that you want replies sent to [email protected] If you do not select this option, SQLyog Notification Services sends replies to your email messages to the email account from which you send them.)

Figure 4: Your Email Information

SMTP Server Information
You must specify two attributes for your SMTP server:

• Host: Specifies your Simple Mail Transfer Protocol (SMTP) server for outgoing messages (You can get this information from your Internet service provider (ISP) or local area network (LAN) administrator.)
• Port: Specifies the port number you connect to on your outgoing email (SMTP) server (This port number is usually 25.)

If your SMTP server requires authorization to send email, you need to check “SMTP server requires authentication” and provide the details. You can get this information from your ISP or your LAN administrator.

Click Next to specify Subject for the email.

Specify Subject for Email
Figure 5 shows the page where you either specify a subject for your outgoing messages or select YYYY-MM-DD or YYYY-MM-DD HH:MM:SS to auto generate the subject from the current Date/Time value.

Figure 5: Subject for the Email

Click Next to specify query (or queries) to execute.

Specify Query
Figure 6 shows the page where you specify the query (or queries) that you want to execute. You can separate multiple queries using semicolons (;).

Figure 6: Query for the Job

Click Parse to check whether your query (or queries) are valid or not.

Click Next to specify what you want to do with the job.

Executing Jobs
Figure 7 shows the page where you specify what you want to do with the job.

Figure 7: Different Options to Execute the Job

Select Execute Now to start up SJA as a child process that executes the query (or queries) and sends the resultset(s) over email. The last step of the wizard shows the complete details of the process.

Select Save As Job File to save the information as a XML definition file. You can then pass this file as an argument to SJA.

Select Save & Schedule It to save the information and schedule it. In Windows, SQLyog uses Windows Task Scheduler to schedule the app.

Click Next to go to the next page.

Finishing Up the Notification Wizard
Figure 8 shows the final page of the Notification Wizard.

Figure 8: Finishing Up the Job

If you selected Execute Now in the previous page (see Figure 7), this page of the wizard would show the complete details of the process.

Figure 9 shows a screenshot of my Outlook with a sample resultset.

Figure 9: Sample Email Generated by SJA

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. The following is the syntax to execute SJA from command line with the XML file as a parameter:

sja   [?l]

The sja supports the following options:

• -l ? File where sja will log all the errors it has encountered while syncing databases
• If you do not specify a log file, sja creates a default log file (sja.log) in the current directory of the executable and logs all error messages in this file.

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

Figure 10: Providing Job Name

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

Figure 11: Scheduling the Job Using Windows Task Manager

Sample SJA Job File
The following is a sample job file SJA used:

localhostroot3306mysqlKaram Chand[email protected][email protected]samtp.yahoo.com25MySQL Helpselect * from mysql.user;

Provide Critical Data On Demand
Immediate access to information is critical in business today, and users can’t sit in front of their terminals waiting for important data. They need information as and when they require it. SQLyog’s Notification Services allows you to provide just that with a few clicks of the mouse.