24.5. Log and Status Files

MySQL Server can write information to several types of log files. The logs record various types of information about the SQL statements processed by the server:

• The general query log records all statements that the server receives from clients.

• The binary log records statements that modify data.

• The slow query log contains a record of queries that take a long time to execute.

These logs can be used to assess the operational state of the server, for data recovery after a crash, for replication purposes, and to help you determine which queries are running slowly. The following sections describe each of these logs briefly and how to enable them. (None of them are enabled by default.) However, it's important to realize that log files, particularly the general query log, can grow to be quite large. Thus, you do not necessarily want to enable them all, especially for a busy server. Here is a recommended logging strategy:

1.	Enable the general query log, the binary log, and the slow query log when you set up a server initially.
2.	After the server has been configured and you have verified that it is running smoothly, disable the general query log to save disk space.

All logs are written in text format except for the binary log which, as the name implies, is a binary file. Text logs can be viewed using any program capable of displaying text files. For the slow query log, another approach is to use the mysqldumpslow utility; it can summarize the log contents. To view the contents of a binary log file, use the mysqlbinlog utility.

When the server logs statements to the binary log and slow query log, it writes extra information. For example, for the slow query log, the server writes execution times and which user executed each statement. To suppress the extra information, start the server with the --log-short-format option.

The server also produces diagnostic information (which normally is written to an error log), and it creates several status files. Later sections describe these files as well.

24.5.1. The General Query Log

The general query log contains a record of when clients connect and disconnect, and the text of every SQL statement received by the server (whether or not it was processed successfully). The server writes statements to the log in the order that it receives them. This log is useful for determining the frequency of a given type of statement or for troubleshooting queries that are not logged to other log files.

To enable the general query log, use the --log or --log=file_name option. If no filename is given, the default name is host_name.log, where host_name stands for the server hostname. By default, the server creates the general query log file under the data directory unless you specify an absolute pathname.

24.5.2. The Binary Log

The binary log contains a record of statements that modify data. For example, the server logs UPDATE and DELETE statements to the binary log, but not SELECT statements. Statements are written to the binary log only after they execute. Statements that are part of a multiple-statement transaction are written as a group after the transaction has been committed. That is, statements are logged in transactional units.

This log is stored in binary format, but its contents can be viewed using the mysqlbinlog utility. The binary log is used for communication between master and slave replication servers, and also can be used for data recovery.

To enable the binary log, use the --log-bin or --log-bin=file_name option. If no filename is given, the default name is host_name-bin.nnnnnn, where host_name stands for the server hostname. nnnnnn in the name means that the server writes a numbered series of logs, creating a new log each time the server starts up or the logs are flushed. (This means that "the binary log" actually comprises a set of log files.) By default, the server creates the binary log files under the data directory unless you specify an absolute pathname.

If binary logging is enabled, the server also creates a binary log index file that lists the names of the current set of binary log files. By default, the name of the index file is the same as the binary log basename, with a suffix of .index rather than .nnnnnn. To specify the name explicitly, use a --log-bin-index=file_name option.

24.5.3. The Slow Query Log

The slow query log contains the text of queries that take a long time to execute, as well as information about their execution status. By default, "a long time" is more than 10 seconds. This can be changed by setting the long_query_time server variable. The server writes queries to this log after they finish because execution time is not known until then.

The contents of the slow query log can helpful for identifying queries that should be optimized. For more information, see Section 40.3, "Using The Slow Query Log for Diagnostic Purposes."

To enable the slow query log, use the --log-slow-queries or --log-slow-queries=file_name option. If no filename is given, the default name is host_name-slow.log, where host_name stands for the server hostname. By default, the server creates the slow query log file under the data directory unless you specify an absolute pathname.

To log queries that are not processed with the benefit of indexes, use the --log-queries-not-using-indexes option.

24.5.4. The Error Log

The server produces diagnostic messages about normal startups and shutdowns, as well as about abnormal conditions:

• On Windows, the server opens an error log file, which by default is named host_name.err in the data directory. If you start the server from the command line with the --console option, it writes error information to the console window rather than to the error log.

• On Unix, if you invoke mysqld directly, it sends diagnostics to its standard error output, which normally is your terminal. You can start the server with a --log-error=file_name option to log errors to the given file. However, it's more usual to invoke the mysqld_safe script (or mysql.server, which in turn invokes mysqld_safe). mysqld_safe creates the error log and then starts the server with its output redirected to the error log. (Thus, the server writes to the error log, but does not itself directly create the log file.) The default error log name is host_name.err in the server's data directory. mysqld_safe itself also may write information to the error log. For example, if mysqld_safe detects that the server has died, it automatically restarts the server after writing mysqld restarted to the log.

The contents of the error log can be useful for troubleshooting server operation. For a description of the kinds of information you might find in this log, see Section 40.2, "Using the Error Log for Diagnostic Purposes."

24.5.5. Status Files

The server creates several status files. Some of these are located in the data directory by default, but not all.

The server records its process ID in a PID file, for use by other programs that need to send the server a signal. For example, on Unix, processes send signals to each other using process ID values. mysqld_safe is one program that uses this approach. It looks in the PID file to determine the server process ID, and then tries to send the server a signal to check whether it is running.

The default PID filename is host_name.pid in the data directory. The name and location may be changed with the --pid-file=file_name option.

Unix servers create a Unix socket file so that local clients can establish socket connections. By default, this file is /tmp/mysql.sock. A different filename can be specified by starting the server with the --socket option. If you change the location, client programs also need to be started with the same --socket option so that they know where the socket file is located.