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
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.
tutorial, we will be writing a common batch file to back up a table to a file.
The SQL File
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:
SELECT field1, field2, field3 INTO OUTFILE “C:\dboutput\result.text”
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
LINES TERMINATED BY “\n”
Running the Batch Job
To run your SQL
batch file from the command line, enter the following:
mysql < c:\commands.sql
Don’t forget to enclose the
file path in quotes if there are any spaces.
/user/bin/mysql < commands.sql
Running the Batch Job as a Scheduled Task
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
the batch job:
Open Scheduled Tasks.
Click Start, click All
Programs, point to Accessories, point to System Tools, and
then click Scheduled Tasks:
Scheduled Task to start the Scheduled Task Wizard, and then click Next
in the first dialog box.
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.
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:
- One time only
When my computer
starts (before a user logs on)
When I log on
(only after the current user logs on)
specify the information about the day and time to run the task, and then click Next.
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
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
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.
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:
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
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
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.
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.