Maintaining Log Files

When the MySQL server begins executing, it examines its startup options to see whether or not it should perform logging and opens the appropriate log files if it should. There are several types of logs you can tell the server to generate:

• The general query log

  This log contains a record of client connections, queries, and various other miscellaneous events. It is useful for monitoring server activity?who is connecting, from where, and what they are doing. It's the most convenient log to use when you want to determine what queries clients are sending to the server, which can be very useful for troubleshooting or debugging.

• The slow-query log

  This log's purpose is to help you identify statements that may be in need of being rewritten for better performance. The server maintains a long_query_time variable that defines "slow" queries. If a query takes more than that many seconds of real time, it is considered slow and is recorded in the slow-query log. The slow-query log is also used to log queries for which no indexes were used.

• The update log

  This log records queries that modify the database. The term "update" in this context refers not just to UPDATE statements, but to any statement that modifies data. For this reason, it contains a record of queries such as DELETE, INSERT, REPLACE, CREATE TABLE, DROP TABLE, GRANT, and REVOKE. Update log contents are written as SQL statements in a form that can be used as input to the mysql program. Originally, the purpose of this log was to create a record to be used in conjunction with backups to restore tables after a crash. (You can restore a database from your backup files and then rerun any queries that modified the database subsequent to the backup by using the update logs as input to mysql. That way you can bring the tables to the state they were in at the time of the crash.) But as of MySQL 3.23.14, when the binary update log was introduced, the update log should be considered deprecated.

• The binary update log and the binary log index file

  The binary update log contents are similar to the contents of the update log, but it's stored in a more efficient format and with additional information. It is used for recovery operations and for transmitting updates to replication slave servers. The binary logs are accompanied by an index file that lists which binary log files exist on the server.

The default location for each of these log files is the data directory, although the server won't create any of them unless you ask for them. Each log can be enabled by specifying a startup option for mysqld. Other than the binary log, these logs are written in ASCII format and can be viewed directly. To see the contents of a binary log, use the mysqlbinlog utility.

Another log file, the error log, is a special case that is handled somewhat differently. (For example, on UNIX it's created by the mysqld_safe script rather than by the server.) It's described in detail later in the section "The Error Log." The server also manages some special-purpose logs that are associated with particular table handlers. The ISAM log is used for debugging purposes to record changes to ISAM and MyISAM tables; I won't mention it further. The BDB and InnoDB table handlers maintain logs of their own for internal purposes (such as for performing auto-recovery after a crash).

Of all the logs, the general query log is most useful for monitoring the server, so when you first start using MySQL, I recommend that you enable the general log in addition to whatever other logs you want. After you have gained some experience with MySQL, you may want to turn off the general log to reduce your disk-space requirements.

To enable logging, use the options shown in the following table. If the log filename is optional (as indicated by square brackets) and you don't provide one, the server uses a default name and writes the log file in the data directory. The default name for each of the log files is derived from the name of your server host, represented by HOSTNAME in the following discussion. If you specify a log name that is a relative pathname, the name is interpreted with respect to the data directory. A full pathname can be specified to place the log in some other directory. The server will create any log file that does not exist, but will not create the directory in which the file is to be written. If necessary, create the directory before starting the server.

If the BDB or InnoDB table handlers are enabled, they create their own logs (by default, in the data directory). You cannot control whether or not the logs are generated, but you can specify where they are written by using the following options:

If you specify either of the InnoDB options, you should specify both, and you must give both the same value.

You can specify logging options on the command line for mysqld or mysqld_safe. However, because you usually specify log options the same way each time you start the server, it's most common to list them in an appropriate group of an option file. Typically, options are listed in the [mysqld] group, but they need not always be. The "Specifying Startup Options" section earlier in this chapter details the option groups applicable to the server and to the server startup programs.

The General Query Log

This log contains a record of when clients connect to the server, each query that is sent to it by clients, and various other events that are not represented as queries (such as server startup and shutdown). If you enable the general log by specifying the --log option without a filename, the default name is HOSTNAME.log in the data directory.

Queries are written to this log in the order that the server receives them. This may well be different than the order in which they finish executing, particularly for a mix of short and long queries.

The Slow-Query Log

The slow-query log provides a record of which queries took a long time to execute, where "long" is defined by the value of the long_query_time server variable in seconds. Slow queries also cause the server to increment its Slow_queries status counter. The slow-query log can be useful for identifying queries that you might be able to improve if you rewrite them. However, you'll need to take general load into account when interpreting the contents of this log. Query time is measured in real time (not CPU time), so if your server is bogged down, it's more likely that a query will be assessed as being slow, even if at some other time it runs under the limit.

If you enable the slow-query log by specifying --log-slow-queries without a filename, the default name is HOSTNAME-slow.log in the data directory. If the --log-long-format option is given in conjunction with --log-slow-queries, MySQL also logs queries that execute without benefit of any index.

Because the time a query takes is not known until it finishes, queries are written to the slow-query log after they execute, not when they are received.

Use the mysqldumpslow utility to see what queries are contained in the slow-query log.

The Update Log

The update log is used to record statements that modify data, such as INSERT, DELETE, or UPDATE. SELECT statements are not written to this log. An UPDATE statement such as the following is not written to the update log, either, because it doesn't actually change any values:

UPDATE t SET i = i; 

MySQL must execute a statement first to determine whether it modifies data, so queries are written to the update log when they finish executing rather than when they are received.

Prior to MySQL 3.23.14 (when the binary update log was introduced), the update log can be used for database backup and recovery. However, the update log now is deprecated in favor of the binary log, which serves the same purposes and supports replication operations as well.

Update logging is enabled with the --log-update option. The MySQL server names update log files using the following rules:

• If you enable the update log by specifying --log-update without a filename, the server generates a numbered series of log files in the data directory using your server's hostname as the file basename: HOSTNAME.001, HOSTNAME.002, and so forth.^[1]

  ^[1] There are plans to change numbered log names to use six digits rather than three. This will help make log names sort better. Currently, they sort out of order when you cross the threshold from .999 to .1000. Using six digits will make out-of-order sorting much less likely.

• If you specify a log name that contains no extension, the server uses that name rather than the hostname as the basename and generates a numbered series of log files. For example, if you specify --log-update=update, it generates update logs named update.001, update.002, and so forth.

• If you enable update logging and specify a log name that contains an extension, the server always uses exactly that name for the log and does not generate a numbered series of log files.

For update logs that are generated in numbered sequence, the server creates the next file in the series whenever it starts up or the logs are flushed.

If the --log-long-format option is given in conjunction with --log-update, MySQL writes additional information to the log, indicating which user issued each statement and at what time.

The Binary Update Log and the Binary Log Index File

Like the update log, the binary update log is used for recording queries that modify data, but its contents are written in a more efficient binary format rather than in ASCII. The binary log also contains additional information, such as query execution timestamps. The binary nature of this log means that it is not directly viewable, but you can use the mysqlbinlog utility to produce readable binary log output.

The binary update log can be used for database backup and recovery, and you must enable it if you want to set up a server as a master server that is replicated to a slave server.

If you enable the binary log by specifying --log-bin without a filename, binary logs are generated in numbered sequence, using HOSTNAME-bin as the basename?HOSTNAME-bin.001, HOSTNAME-bin.002, and so on. Otherwise, the name that you specify is used as the basename (with the exception that if the name includes an extension, the extension is stripped). The next file in the sequence is generated each time you start the server, flush the logs, or when the current log reaches its maximum size. This size is determined by the value of the max_binlog_size server variable. (Note that these rules for generating binary log file names are similar to but are not quite the same as the rules used for update log naming.)

Queries are written to the binary update log in order of execution. That is, they're logged in the order they finish, not the order in which they are received, which is an important property for making replication work properly. Queries that are part of a transaction are cached until the transaction is committed, at which time all queries in the transaction are logged. If the transaction is rolled back, the transaction is not written to the binary log because it results in no changes to the database. (This is similar to the way individual queries are not written to the update log unless they actually change data; for the binary update log, the same principle applies, but extends across multiple statements in transactional context.)

If you enable binary logging, the server also creates an accompanying binary log index file that lists the names of the existing binary log files. The default index filename is the same as the basename of the binary logs, with an .index extension. To specify a name explicitly, use the --log-bin-index option. If the name includes no extension, .index will be added to the name automatically. For example, if you specify --log-bin-index=binlog, the index filename becomes binlog.index.

If you are using the binary logs for replication purposes, be sure not to delete any binary log file until you are sure that its contents have been replicated to all applicable slave servers and it is no longer needed. The "Expiring Replication-Related Log Files" section, later in this chapter, describes how to check this.

The Error Log

The error log is used for recording diagnostic and error information. This log is handled differently on UNIX and Windows, as described in the following discussion.

The Error Log on UNIX

On UNIX, the error log is not created by the server, unlike the other logs, but rather by the mysqld_safe script that is used to start up the server.

mysqld_safe creates the error log by redirecting the server's standard output and standard error output (the output streams known as stdout and stderr in the C programming language). The default error log name is HOSTNAME.err. You can specify a different error log name by passing an --err-log option to mysqld_safe on the command line or by including an err-log line in the [mysqld_safe] group of an option file. (Prior to MySQL 4, mysqld_safe is named safe_mysqld. The safe_mysqld script supports --err-log back to version 3.23.22 . Before that, safe_mysqld always writes the log using the default name, and there is no way to change it other than by editing the script.)

If you specify a relative pathname for the error log, the name is interpreted with respect to the directory from which mysqld_safe is invoked. This is in contrast to the other log files, which are created by mysqld and for which relative pathnames are interpreted with respect to the data directory. Because you won't necessarily always invoke mysqld_safe from the same directory (for example, if you execute it manually on different occasions), it's best to specify an absolute pathname to ensure that the error log is always created in the same location.

Note that if the error log file already exists but is not writable to the login account used for running the server, startup will fail with no output being written to the error log. This can happen if you start up the server with different --user values at different times. It's best to use the same account consistently, as discussed in the "Running the Server Using an Unprivileged Login Account" section earlier in this chapter.

The error log is created if you start the server using the mysql.server script because mysql.server invokes mysqld_safe. However, mysql.server doesn't recognize --err-log on the command line or in its [mysql_server] option group, so if you want to give a specific error log name in this case, you must do so in the [mysqld_safe] group of an option file.

If you start mysqld directly, error messages go to your terminal and there is no error log. You can redirect the output yourself to capture a record of diagnostic output. For example, to write error information to a file named /tmp/mysql.err, invoke the server like this for csh or tcsh:

% mysqld >& /tmp/mysql.err & 

or like this for sh and similar shells:

% mysqld > /tmp/mysql.err 2>&1 & 

The Error Log on Windows

On Windows, the server writes diagnostic information to the file mysql.err in the data directory by default. No alternative filename can be given. If you start the server with the --console option, it writes diagnostic output to the console window and does not create an error log. (The --console option has no effect if you run the server as a service because there is no console to write to in that case.)

Log File Expiration

One danger of enabling logging is that it has the potential to generate huge amounts of information, possibly filling up your disks. This is especially true if you have a busy server that processes lots of queries. To keep the last few logs available online while preventing log files from growing without bound, you can use log file expiration techniques. Some of the methods available for keeping logs manageable include the following:

• Log rotation. This applies to logs that have a fixed filename, such as the general query log and the slow-query log.

• Age-based expiration. This method removes log files that are older than a certain age. It can be applied to numbered log files that are created in numbered sequence, such as the update logs and the binary update logs.

• Replication-related expiration. If you use the binary update log files for replication, it's better not to expire them based on age. Instead, you should consider a binary log file eligible for expiration only after its contents have been replicated to all slave servers. This form of expiration therefore is based on determining which binary logs are still in use.

Log rotation is often used in conjunction with log flushing to make sure that any buffered log information has been written to disk. Logs can be flushed by executing a mysqladmin flush-logs command or by issuing a FLUSH LOGS statement.

The rest of this section describes how to use these expiration techniques. For any that you put into practice, you should also consider how the log files fit into your database-backup methods. (It's a good idea to back up any log files that may be needed for recovery operations, so you don't want to expire such files before you've backed them up!) The example scripts discussed here can be found in the admin directory of the sampdb distribution.

Rotating Fixed-Name Log Files

The MySQL server writes some types of log information to files that have fixed names. This is true for the general query log and the slow-query log. It's also true for the update log if you're not logging updates to a numbered series of files. To expire fixed-name logs, use log rotation. This allows you to maintain the last few logs online, but limit the number to as many as you choose to prevent them from overrunning your disk.

Log file rotation works as follows. Suppose the log file is named log. At the first rotation, you rename log to log.1 and tell the server to begin writing a new log file. At the second rotation, rename log.1 to log.2, log to log.1, and tell the server to begin writing another new log file. In this way, each file rotates through the names log.1, log.2, and so forth. When the file reaches a certain point in the rotation, you expire it by letting the previous file overwrite it. For example, if you rotate the logs daily and you want to keep a week's work of logs, you would keep log.1 through log.7. At each rotation, you would expire log.7 by letting log.6 overwrite it to become the new log.7.

The frequency of log rotation and the number of old logs you keep will depend on how busy your server is (active servers generate more log information) and how much disk space you're willing to devote to old logs.

On UNIX, you can rename the current log file while the server has it open. Flushing the logs causes the server to close that file and open a new one, thereby creating a new log file with the original name. The following shell script can be used to perform rotation of fixed-name log files:

#! /bin/sh 
# rotate_fixed_logs.sh - rotate MySQL log file that has a fixed name

# Argument 1: log file name

if [ $# -ne 1 ]; then
    echo "Usage: $0 logname" 1>&2
    exit 1
fi

logfile=$1

mv $logfile.6 $logfile.7
mv $logfile.5 $logfile.6
mv $logfile.4 $logfile.5
mv $logfile.3 $logfile.4
mv $logfile.2 $logfile.3
mv $logfile.1 $logfile.2
mv $logfile $logfile.1
mysqladmin flush-logs

The script takes the log file name as its argument. You can either specify the full pathname of the file or change directory into the log directory and specify the file's name in that directory. For example, to rotate a log named log in /usr/mysql/data, you can use the following command:

% rotate_fixed_logs.sh /usr/mysql/data/log 

or the following commands:

% cd /usr/mysql/data 
% rotate_fixed_logs.sh log

It's best to run the script while logged in as mysqladm, to make sure that you have permission to rename the log files. Note that the mysqladmin command in the script includes no connection parameter arguments, such as -u or -p. If the relevant connection parameters for invoking mysql are stored in mysqladm's .my.cnf option file, you don't need to specify them on the mysqladmin command in the script. If you don't use an option file, the mysqladmin command needs to know how to connect to the server using a MySQL account that has sufficient privileges to flush the logs. To handle this, you might want to set up a limited-privilege account that can't do anything but issue flush commands. Then you can put that account's password in the script with minimal risk if you make the script accessible only to mysqladm. If you want to do this, the MySQL account should have only the RELOAD privilege. For example, to call the user flush and assign a password of flushpass, use the following GRANT statement:

GRANT RELOAD ON *.* TO 'flush'@'localhost' IDENTIFIED BY 'flushpass'; 

After creating this account, change the mysqladmin command in the rotate_fixed_logs.sh script to look like this:

mysqladmin -u flush -pflushpass flush-logs 

To rotate and flush the logs periodically, see the "Automating the Log Expiration Procedure" section later in this chapter.

Under Linux, you may prefer to use the logrotate utility to install the mysql-log-rotate script that comes with the MySQL distribution rather than using rotate_fixed_logs.sh or writing your own script. Look for mysql-log-rotate in /usr/share/mysql for RPM distributions, in the support-files directory of your MySQL installation for binary distributions, or under the share/mysql directory of MySQL source distributions.

On Windows, log rotation doesn't work quite the same way as on UNIX. If you attempt to rename a log file while the server has it open, a "file in use" error occurs. To rotate the logs, shut down the server first and then rename the files and restart the server. I'll leave it to you to stop and restart the server as you want, but the log file renaming can be performed using the following batch script:

@echo off 
REM rotate_fixed_logs.bat - rotate MySQL log file that has a fixed name

if not "%1" == "" goto ROTATE
    @echo Usage: rotate_fixed_logs logname
    goto DONE

:ROTATE
set logfile=%1
erase %logfile%.7
rename %logfile%.6 %logfile%.7
rename %logfile%.5 %logfile%.6
rename %logfile%.4 %logfile%.5
rename %logfile%.3 %logfile%.4
rename %logfile%.2 %logfile%.3
rename %logfile%.1 %logfile%.2
rename %logfile% %logfile%.1
:DONE

rotate_fixed_logs.bat is invoked much like the rotate_fixed_logs.sh shell script, with a single argument that names the log file to be rotated. For example, like this:

C:\> rotate_fixed_logs C:\mysql\data\log 

or like this:

C:\> cd \mysql\data 
C:\> rotate_fixed_logs log

The first few times a log rotation script executes, you won't have a full set of log files in the rotation and the script may complain that it can't find all the files to be rotated. That's normal.

Expiring Numbered Log Files

Fixed-name log files can be expired using filename rotation, as just discussed. For numbered log files, such as those you can generate for the update log and the binary update log, log expiration needs to be handled a bit differently. In this case, you can expire files based on age (assessed as time of last modification) rather than by rotating them through a given set of names. The reason for doing this is that numbered logs are not necessarily created on a fixed schedule, so you can't assume that it's okay to retain just the last n files. If the server happens to receive several log flushing commands in a short time span, you can easily have that many logs, none of which are old enough to need expiring.

For logs generated by the server with sequenced filenames, an expiration script based on age might look like this:

#! /usr/bin/perl -w 
# expire_numbered_logs.pl - Look through a set of numbered MySQL
# log files and delete those that are more than a week old.

# Usage: expire_numbered_logs.pl logfile ...

use strict;
die "Usage: $0 logfile ...\n" if @ARGV == 0;
my $max_allowed_age = 7;    # max allowed age in days
foreach my $file (@ARGV)    # check each argument
{
    unlink ($file) if -e $file && -M $file >= $max_allowed_age;
}
exit (0);

expire_numbered_logs.pl is written in Perl. It works on both UNIX and Windows because Perl is a cross-platform scripting language. To use the script, invoke it with the names of the log files that are candidates for expiration. For example, on UNIX, you can do this:

% expire_numbered_logs.pl /usr/mysql/data/update.[0-9]* 

or this:

% cd /usr/mysql/data 
% expire_numbered_logs.pl update.[0-9]*

Note that the expire_numbered_logs.pl script is dangerous if you don't pass it appropriate arguments! For example, you definitely don't want to invoke it as follows:

% cd /usr/mysql/data 
% expire_numbered_logs.pl *

That will remove all files in the data directory that are more than a week old, not just log files.

Expiring Replication-Related Log Files

The server generates binary update logs in numbered sequence. One way to manage them is to expire them based on age, as described in the previous section. However, if you're using the binary logs for replication, age is not necessarily an indicator of whether a log can be removed. Instead, you should expire a binary log only after its contents have been replicated to all the slave servers.

Unfortunately, the master server itself doesn't know how many slaves there are or which files have been propagated to them. The master won't purge binary logs that have not yet been sent to connected slaves, but there is no guarantee that a given slave is connected at any particular time. This means that you yourself must know which servers are acting as slaves and then connect to each one and issue a SHOW SLAVE STATUS statement to determine which of the master's binary log files the slave currently is processing. (The file's name is the value in the Master_Log_File column.) Any binary log that is no longer used by any of the slaves can be removed. Suppose you have the following scenario:

• The local server is the master and it has two slaves, S1 and S2.

• The binary log files that exist on the master have names from binlog.038 through binlog.042.

• SHOW SLAVE STATUS produces the following result on S1:

  mysql> SHOW SLAVE STATUS\G 
  ...
  Master_Log_File: binlog.41
  ...
  

And this result on S2:

mysql> SHOW SLAVE STATUS\G 
...
Master_Log_File: binlog.40
...

In this case, the lowest-numbered binary log still required by the slaves is binlog.40, so any log with a lower number can be removed. To do that, connect to the master server and issue the following statement:

mysql> PURGE MASTER LOGS TO 'binlog.040'; 

That causes the server to delete all binary logs with numbers lower than the named file, which for the situation just described, includes binlog.038 and binlog.039.

SHOW SLAVE STATUS is available as of MySQL 3.23.22, and PURGE MASTER LOGS is available as of MySQL 3.23.28. Both statements require the SUPER privilege (PROCESS prior to MySQL 4.0.2).

Automating the Log Expiration Procedure

It's possible to invoke log expiration scripts on a manual basis, but if you have a way to schedule the commands to execute automatically, you don't have to remember to run them yourself. One way to do this is to use the cron utility and set up a crontab file that defines the expiration schedule. If you're not familiar with cron, check the relevant UNIX manual pages using the following commands:

% man cron 
% man crontab

You may need to use another command to read about the crontab file format:

% man 5 crontab 

Suppose that you want to rotate the general query logs and expire numbered update logs using the rotate_fixed_logs.sh and expire_numbered_logs.pl scripts, and that these scripts are installed in /u/mysqladm/bin. Log in as mysqladm and then edit the mysqladm user's crontab file using the following command:

% crontab -e 

This command will allow you to edit a copy of your current crontab file (which may be empty if no cron jobs have yet been set up). Add lines to the file that look like the following:

0 4 * * * /u/mysqladm/bin/rotate_fixed_logs.sh /usr/mysql/data/log
0 4 * * * /u/mysqladm/bin/expire_numbered_logs.pl /usr/mysql/data/update.[0-9]*

These entries tell cron to run both scripts at 4 a.m. each morning. You can vary the time or scheduling as desired; check the crontab manual page for the format of the entries. You'll probably want to expire the logs more frequently for a busy server that generates lots of log information than for one that is less active.

If you want to make sure the logs are flushed regularly (for example, to generate the next numbered update log or binary update log), you can schedule a mysqladmin flush-logs command to execute periodically by adding another crontab entry. You may need to list the full pathname to mysqladmin to make sure that cron can find it.