MySQL Cookbook

Using mysql in Shell Scripts

Problem

You want to invoke mysql from within a shell script rather than using it interactively.

Solution

There’s no rule against that. Just be sure to supply the appropriate arguments to the command.

Discussion

If you need to process query results within a program, you’ll typically use a MySQL programming interface designed specifically for the language you’re using (for example, in a Perl script you’d use the DBI interface). But for simple, short, or quick-and-dirty tasks, it may be easier just to invoke mysql directly from within a shell script, possibly postprocessing the results with other commands. For example, an easy way to write a MySQL server status tester is to use a shell script that invokes mysql, as is demonstrated later in this section. Shell scripts are also useful for prototyping programs that you intend to convert for use with a standard API later.

For Unix shell scripting, I recommend that you stick to shells in the Bourne shell family, such as sh, bash, or ksh. (The csh and tcsh shells are more suited to interactive use than to scripting.) This section provides some examples showing how to write Unix scripts for /bin/sh. It also comments briefly on DOS scripting. The sidebar “Using Executable Programs” describes how to make scripts executable and run them.

Using Executable Programs

When you write a program, you’ll generally need to make it executable before you can run it. In Unix, you do this by setting the “execute” file access modes using the chmod command:

% chmod +x myprog

To run the program, name it on the command line:

% myprog

However, if the program is in your current directory, your shell might not find it. The shell searches for programs in the directories named in your PATH environment variable, but for security reasons, the search path for Unix shells often is deliberately set not to include the current directory (.). In that case, you need to include a leading path of ./ to explicitly indicate the program’s location:

% ./myprog

Some of the programs developed in this book are intended only to demonstrate a particular concept and probably never will be run outside your current directory, so examples that use them generally show how to invoke them using the leading ./ path. For programs that are intended for repeated use, it’s more likely that you’ll install them in a directory named in your PATH setting. In that case, no leading path will be necessary to invoke them. This also holds for common Unix utilities (such as chmod), which are installed in standard system directories.

Under Windows, programs are interpreted as executable based on their filename extensions (such as .exe or .bat), so chmod is unnecessary. Also, the command interpreter includes the current directory in its search path by default, so you should be able to invoke programs that are located there without specifying any leading path. (Thus, if you’re using Windows and you want to run an example command that is shown in this book using ./, you should omit the ./ from the command.)

Writing Shell Scripts Under Unix

Here is a shell script that reports the current uptime of the MySQL server. It runs a SHOW STATUS query to get the value of the Uptime status variable that contains the server uptime in seconds:

#! /bin/sh
# mysql_uptime.sh - report server uptime in seconds

mysql -B -N -e "SHOW STATUS LIKE 'Uptime'"

The first line of the script that begins with #! is special. It indicates the pathname of the program that should be invoked to execute the rest of the script, /bin/sh in this case. To use the script, create a file named mysql_uptime.sh that contains the preceding lines and make it executable with chmod +x. The mysql_uptime.sh script runs mysql using -e to indicate the query string, -B to generate batch (tab-delimited) output, and -N to suppress the column header line. The resulting output looks like this:

% ./mysql_uptime.sh
Uptime  1260142

The command shown here begins with ./, indicating that the script is located in your current directory. If you move the script to a directory named in your PATH setting, you can invoke it from anywhere, but then you should omit the ./ from the command. Note that moving the script make cause csh or tcsh not to know where the script is located until your next login. To remedy this without logging in again, use rehash after moving the script. The following example illustrates this process:

% ./mysql_uptime.sh
Uptime  1260348
% mv mysql_uptime.sh /usr/local/bin
% mysql_uptime.sh
mysql_uptime.sh: Command not found.
% rehash
% mysql_uptime.sh
Uptime  1260397

If you prefer a report that lists the time in days, hours, minutes, and seconds rather than just seconds, you can use the output from the mysql STATUS statement, which provides the following information:

mysql> STATUS;
Connection id:          12347
Current database:       cookbook
Current user:           cbuser@localhost
Current pager:          stdout
Using outfile:          ''
Server version:         3.23.47-log
Protocol version:       10
Connection:             Localhost via UNIX socket
Client characterset:    latin1
Server characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 14 days 14 hours 2 min 46 sec

For uptime reporting, the only relevant part of that information is the line that begins with Uptime. It’s a simple matter to write a script that sends a STATUS command to the server and filters the output with grep to extract the desired line:

#! /bin/sh
# mysql_uptime2.sh - report server uptime

mysql -e STATUS | grep "^Uptime"

The result looks like this:

% ./mysql_uptime2.sh
Uptime:                 14 days 14 hours 2 min 46 sec

The preceding two scripts specify the statement to be executed by means of the -e command-line option, but you can use other mysql input sources described earlier in the chapter, such as files and pipes. For example, the following mysql_uptime3.sh script is like mysql_uptime2.sh but provides input to mysql using a pipe:

#! /bin/sh
# mysql_uptime3.sh - report server uptime

echo STATUS | mysql | grep "^Uptime"

Some shells support the concept of a "here-document,” which serves essentially the same purpose as file input to a command, except that no explicit filename is involved. (In other words, the document is located “right here” in the script, not stored in an external file.) To provide input to a command using a here-document, use the following syntax:

command <<MARKER
input line 1
input line 2
input line 3
...
MARKER

<<MARKER signals the beginning of the input and indicates the marker symbol to look for at the end of the input. The symbol that you use for MARKER is relatively arbitrary, but should be some distinctive identifier that does not occur in the input given to the command.

Here-documents are a useful alternative to the -e option when you need to specify lengthy query input. In such cases, when -e becomes awkward to use, a here-document is more convenient and easier to write. Suppose you have a log table log_tbl that contains a column date_added to indicate when each row was added. A query to report the number of records that were added yesterday looks like this:

SELECT COUNT(*) As 'New log entries:'
FROM log_tbl
WHERE date_added = DATE_SUB(CURDATE( ),INTERVAL 1 DAY);

That query could be specified in a script using -e, but the command line would be difficult to read because the query is so long. A here-document is a more suitable choice in this case because you can write the query in more readable form:

#! /bin/sh
# new_log_entries.sh - count yesterday's log entries

mysql cookbook <<MYSQL_INPUT
SELECT COUNT(*) As 'New log entries:'
FROM log_tbl
WHERE date_added = DATE_SUB(CURDATE( ),INTERVAL 1 DAY);
MYSQL_INPUT

When you use -e or here-documents, you can refer to shell variables within the query input—although the following example demonstrates that it might be best to avoid the practice. Suppose you have a simple script count_rows.sh for counting the rows of any table in the cookbook database:

#! /bin/sh
# count_rows.sh - count rows in cookbook database table

# require one argument on the command line
if [ $# -ne 1 ]; then
    echo "Usage: count_rows.sh tbl_name";
    exit 1;
fi

# use argument ($1) in the query string
mysql cookbook <<MYSQL_INPUT
SELECT COUNT(*) AS 'Rows in table:' FROM $1;
MYSQL_INPUT

The script uses the $# shell variable, which holds the command-line argument count, and $1, which holds the first argument after the script name. count_rows.sh makes sure that exactly one argument was provided, then uses it as a table name in a row-counting query. To run the script, invoke it with a table name argument:

% ./count_rows.sh limbs
Rows in table:
12

Variable substitution can be helpful for constructing queries, but you should use this capability with caution. A malicious user could invoke the script as follows:

% ./count_rows.sh "limbs;DROP TABLE limbs"

In that case, the resulting query input to mysql becomes:

SELECT COUNT(*) AS 'Rows in table:' FROM limbs;DROP TABLE limbs;

This input counts the table rows, then destroys the table! For this reason, it may be prudent to limit use of variable substitution to your own private scripts. Alternatively, rewrite the script using an API that allows special characters such as ; to be dealt with and rendered harmless (see Recipe 2.8).

Writing Shell Scripts Under Windows

Under Windows, you can run mysql from within a batch file (a file with a .bat extension). Here is a Windows batch file, mysql_uptime.bat, that is similar to the mysql_uptime.sh Unix shell script shown earlier:

@ECHO OFF
REM mysql_uptime.bat - report server uptime in seconds

mysql -B -N -e "SHOW STATUS LIKE 'Uptime'"

Batch files may be invoked without the .bat extension:

C:\> mysql_uptime
Uptime  9609

DOS scripting has some serious limitations, however. For example, here-documents are not supported, and command argument quoting capabilities are more limited. One way around these problems is to install a more reasonable working environment; see the sidebar “Finding the DOS Prompt Restrictive?”

Finding the DOS Prompt Restrictive?

If you’re a Unix user who is comfortable with the shells and utilities that are part of the Unix command-line interface, you probably take for granted some of the commands used in this chapter, such as grep, sed, tr, and tail. These tools are so commonly available on Unix systems that it can be a rude and painful shock to realize that they are nowhere to be found if at some point you find it necessary to work at the DOS prompt under Windows.

One way to make the DOS command-line environment more palatable is to install Cygnus tools for Windows (Cygwin) or Unix for Windows (UWIN). These packages include some of the more popular Unix shells as well as many of the utilities that Unix users have come to expect. Programming tools such as compilers are available with each package as well. The package distributions may be obtained at the following locations:

http://www.cygwin.com/

http://www.research.att.com/sw/tools/uwin/

These distributions can change the way you use this book under Windows, because they eliminate some of the exceptions where I qualify commands as available under Unix but not Windows. By installing Cygwin or UWIN, many of those distinctions become irrelevant.