Running MySQL in Batch Mode

August 17, 2009

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:

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:

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