Learning MySQL

Running the Monitor in Batch Mode

The MySQL monitor can be used in interactive mode or in batch mode. In interactive mode, you type in SQL queries or MySQL commands such as SHOW DATABASES at the MySQL prompt, and view the results.

In batch mode, you tell the monitor to read in and execute a list of commands from a file. This is useful when you need to run a large set of operations—for example, when you want to restore a database from a backup file. It’s also useful when you need to run a particular sequence of operations frequently; you can save the commands in a file and then tell the monitor to read in the file whenever you need it.

The examples we’ve presented earlier in this chapter, and most of the examples in this book, show the monitor being used in interactive mode. Let’s look at an example for batch mode. Say you have a text file called count_users.sql containing the SQL commands:

use mysql;
SELECT COUNT(*) FROM user;

This script tells MySQL that you want to use the mysql database, and that you want to count all the users who have accounts on the MySQL server (we’ll explain the syntax of the SELECT command in Chapter 5).

You can run all the commands in this file using the SOURCE command:

mysql> SOURCE count_users.sql
Database changed
+----------+
| count(*) |
+----------+
| 4        |
+----------+
1 row in set (0.00 sec)

If the count_users.sql file isn’t in the current directory, you should give the full path to the file—for example, /home/adam/Desktop/count_users.sql or C:\count_users.sql ...