MySQL

General MySQL Administration

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

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.

Logging Option	Log Enabled by Option
--log[=file_name]	General log file
--log-bin[=file_name]	Binary update log file
--log-bin-index=file_name	Binary update log index file
--log-update[=file_name]	Update log file
--log-slow-queries[=file_name]	Slow-query log file
--log-isam[=file_name]	ISAM/MyISAM log file
--log-long-format	Affects slow-query and update log format

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:

Logging Option	Purpose
--bdb-logdir=dir_name	BDB log file directory
--innodb_log_arch_dir=dir_name	InnoDB log archive directory
--innodb_log_group_home_dir=dir_name	InnoDB log file directory

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

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.

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

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.

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.

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 &

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.

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

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

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