Running MySQL in Batch Mode

Anyone who has spent a fair amount of time
working with databases comes to realize that common tasks like backups and data
transfers should be run from a script rather than interactively. Having MySQL
read commands from a file is called running in batch mode, because such a file
generally contains multiple commands that all participate in the completion of
the task. Here are a few reasons to use batch jobs:

  • If you run a query repeatedly (say, every day or
    every week), making it a script saves you from retyping it every time you
    execute it.
  • You can generate new queries from existing ones
    that are similar by copying and editing script files.
  • Batch mode can also be useful while you’re
    developing a query, particularly for multiple-line commands or
    multiple-statement sequences of commands. If you make a mistake, you don’t have
    to retype everything. Just edit your script to correct the error, then have
    MySQL execute it again.

In this
tutorial, we will be writing a common batch file to back up a table to a file.

The SQL File

By
convention, database commands are placed in an SQL file. It may contain any command
that can be run from the command line, including code to set the database, SQL
statements, and calls to stored procedures. The following code will extract
some fields from my_table to a file called result.text. This windows version
includes a file path that contains backslashes; in UNIX, it would contain
forward slashes instead:


use my_database;
SELECT field1, field2, field3 INTO OUTFILE “C:\dboutput\result.text”
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
LINES TERMINATED BY “\n”
FROM my_table;

Running the Batch Job

To run your SQL
batch file from the command line, enter the following:

In Windows:


mysql < c:\commands.sql

Don’t forget to enclose the
file path in quotes if there are any spaces.

In UNIX:


/user/bin/mysql < commands.sql

Running the Batch Job as a Scheduled Task

In Windows

Batch jobs
can be even more automated by running them as a scheduled task. In Windows,
batch files are used to execute DOS commands. We can schedule our batch job by
placing the command code that we entered earlier in a file, such as “runsql.bat”.
This file will contain only one line:


mysql < c:\commands.sql

To schedule
the batch job:

1. 
Open Scheduled Tasks.

  • Click Start, click All
    Programs
    , point to Accessories, point to System Tools, and
    then click Scheduled Tasks:

Open Scheduled Tasks

2. 
Double-click Add
Scheduled Task
to start the Scheduled Task Wizard, and then click Next
in the first dialog box.

3. 
The next dialog box
displays a list of programs that are installed on your computer, either as part
of the Windows operating system, or as a result of software installation. Click
Browse and select your SQL file, and then click Open.

4. 
Type a name for the
task, and then choose when and how often you want the task to run, from one of
the following options:

  • Daily
  • Weekly
  • Monthly
  • One time only
  • When my computer
    starts (before a user logs on)
  • When I log on
    (only after the current user logs on)

5. 
Click Next,
specify the information about the day and time to run the task, and then click Next.

6. 
OPTIONAL: Enter the
name and password of the user who is associated with this task. Make sure that
you choose a user with sufficient permissions to run the program. By default,
the wizard selects the name of the user who is currently logged on.

Scheduled Tasks in Windows

Scheduled Tasks in Windows

If at a later time you’d
like to suspend this task, you can open it via the Scheduled Tasks dialog
(pictured above) and deselect the Enabled checkbox on the “Task” tab:

The “Task” Tab Containing the “Enabled” Checkbox

The “Task” Tab Containing the “Enabled” Checkbox

Similarly, you can remove
the task by deleting it like any file. In fact, the task is saved as a .job
file in the WINNT\Tasks folder.

In UNIX

On UNIX operating systems,
scheduled tasks are called cron jobs after the cron command that is used to run
them. Whereas Windows commands are stored in .bat files, UNIX uses shell
scripts to execute batch jobs. To run your SQL file as a cron job, create a
file with one line similar to the following and save it as “runsql”:


0 1 * * * /user/bin/mysql < commands.sql

Now, at the command line,
type the following:


crontab runsql

Your scheduled task will
now run at the time specified until you cancel it. The first five fields in the
file indicate the time that the job will run. The sixth field is the UNIX
command that will execute at the specified time.

Here is a description of
each field:


Field 1 | Field 2 | Field 3 | Field 4 | Field 5
Minutes | Hours | Day of Month | Month | Day of Week
(0-59) | (0-23) | (1-31) | (1-12) | (0-6)

You can enter a number in
the field, a range of numbers, or an * to indicate all. Here are a few more
examples of time fields:


0 1 * * 1-5 (run every Monday-Friday at 1am)
0 1 * * 1,3,5 (run every Monday, Wednesday and Friday at 1am)
10 2 1 * * (run at 2:10am on the first of every month)
0 1 1 1 * (run at 1am on January 1 every year)

In UNIX, cron jobs are stored in one file. To disable your database cron job, type
“crontab -e” at the shell prompt to edit the file. Once you’ve
identified your cron job in the list, you can insert a pound character (#) at
the start of the line to comment it out. For a more permanent delete, either
remove the line from the cron file, or cancel all scheduled jobs by typing
"crontab -r" at the command prompt. You may want to run "crontab
-l" first, to make sure that you aren’t canceling any other jobs that you
want to continue running.

Additional Command Line Options

If you need to
specify connection parameters on the command line, the command might look like
this:


shell> mysql -h host -u user –p password < batch-file

If you want the
script to continue even if some of the statements produce errors, you should
use the –force command-line option.

Conclusion

Using batch jobs is
a very efficient way of performing common database adminsitration tasks. The
simple example that we saw here today is only the tip of the iceberg. There
are all sorts of tasks that can be accomplished using batch files or automated
tasks. When combined with other technologies, products, and programming languages,
batch processes can add a lot of efficiency to your data management and
hopefully, free up time for more attention-worthy matters.

»


See All Articles by Columnist
Rob Gravelle

Robert Gravelle
Robert Gravelle
Rob Gravelle resides in Ottawa, Canada, and has been an IT guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial businesses. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.

Latest Articles