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

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

mysql --skip-column-names -B -e "SHOW /*!50002 GLOBAL */ STATUS LIKE 'Uptime'"

The mysql_uptime.sh script runs mysql using --skip-column-names to suppress the column header line, -B to generate batch (tab-delimited) output, and -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 chmod +x, and run it. 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 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 following information:

mysql>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 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 using the -e command 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 <<MARKERinput line 1input line 2input 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 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 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 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 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, 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 limbs
Rows 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"

This is a simple form of “SQL injection” attack. After argument substitution, the resulting input to mysql looks like this:

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 this.

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:

C:>mysql_uptime
Uptime  9609

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?”