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:
mysql < c:\commands.sql
Dont 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
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:
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:
- 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
If at a later time youd 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 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 youve 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 arent 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.
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.