There’s no rule against this. Just be sure to supply the appropriate arguments to the command.
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 use the DBI interface; see Recipe 2.1). But for simple, short, or quick-and-dirty tasks, it might 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 in this section. Shell scripts are also useful for prototyping programs that you intend to convert for use with a programming interface 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, and comments briefly on Windows scripting.
See Appendix B if you need instructions for
running programs from your command interpreter or for making sure that
variable is set properly to tell your command interpreter which
directories to search for installed programs.
The scripts discussed here can be found in the mysql directory of the
Here is a shell script that reports the current uptime of
the MySQL server. It runs a
STATUS statement 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 --skip-column-names -B -e "SHOW /*!50002 GLOBAL */ STATUS LIKE 'Uptime'"
script runs mysql using
--skip-column-names to suppress the
column header line,
-B to generate batch (tab-delimited)
-e to indicate the statement string
to execute. 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, make it executable with
+x, and run it. The
resulting output looks like this:
The command shown here begins with
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 leading
./ when you run the script.
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
STATUS;Connection id: 12347 Current database: cookbook Current user: cbuser@localhost Current pager: stdout Using outfile: '' Server version: 5.0.27-log Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. 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
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.shUptime: 14 days 14 hours 2 min 46 sec
The preceding two scripts specify the statement to be executed
-e command option, but you can use other
mysql input sources described
earlier in the chapter, such as files and pipes. For example, the
script is like mysql_uptime2.sh
but provides input to mysql using
#!/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:
input line 1
input line 2
input line 3...
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 a lengthy
statement or multiple statements as input. In such cases, when
-e becomes awkward to use, a here-document is more
convenient and easier to write. Suppose that you have a log table
log_tbl that contains a column
date_added to indicate when each
row was added. A statement to report the number of rows 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 statement could be specified in a script using
-e, but the command line would be difficult to read
because the statement is so long. A here-document is a more suitable
choice in this case because you can write the statement in more
#!/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 statement input—although the
following example demonstrates that it might be best to avoid the
practice. Suppose that you have a simple script count_rows.sh for counting the rows of
any table in the
#!/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, and then uses it
as a table name in a row-counting statement. To run the script,
invoke it with a table name argument:
./count_rows.sh limbsRows in table: 12
Variable substitution can be helpful for constructing statements, but you should use this capability with caution. If your script can be executed by other users on your system, someone can invoke it with malicious intent as follows:
./count_rows.sh "limbs;DROP TABLE limbs"
SELECT COUNT(*) AS 'Rows in table:' FROM limbs;DROP TABLE limbs;
This input counts the table rows, and 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 enables special characters such as
; to be dealt with and rendered harmless.
Handling Special Characters and NULL Values in Statements
covers techniques for doing
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 --skip-column-names -B -e "SHOW /*!50002 GLOBAL */ STATUS LIKE 'Uptime'"
Batch files can be invoked without the .bat extension:
Windows 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 Windows Command Line Restrictive?”