10.6 Log and Status Files

The MySQL server can write information to several types of log files. The following list briefly describes each of these logs:

• The general query log contains a record of when clients connect and disconnect, and the text of every single query received by the server (whether or not it was processed successfully). This log is useful for determining the frequency of a given type of statement or for troubleshooting queries that are not otherwise logged.

• The binary log contains a record of queries that update data. It is stored in binary format, but its contents can be viewed using the mysqlbinlog utility. The server logs only successful queries to this file, and only queries that modify data. (For example, the server logs DELETE statements to the binary log, but not SELECT statements.) The binary log is used for communication between master and slave replication servers. It can also be used for data recovery.

  The server also can write a text-format update log, but that log is deprecated in MySQL 4, and eventually will disappear entirely.

• 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. If the server is started with the --log-long-format option, it also writes to the slow query log queries for which no index was used, whether or not the queries took a long time to execute. The contents of this log are helpful for identifying queries that should be optimized.

None of the preceding logs are enabled by default. You must enable them explicitly using appropriate startup options, which are shown in the following table. Each option may be given in --option or --option=file_name form. If no filename is specified, the server uses a default name, as shown in the table:

Log Type	Option	Default Log Name
General query log	--log	host_name.log
Binary log	--log-bin	host_name-bin.nnn
Slow query log	--log-slow-queries	host_name-slow.log

By default, each log file is created under the data directory unless you specify an absolute pathname. host_name stands for the server hostname. nnn 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.

The server also produces diagnostic messages about normal startups and shutdowns, as well as about abnormal conditions. On Windows, the server logs these messages to an error log, unless you invoke it with the --console option to send the messages to the console window. On Unix, the error log is set up by the mysqld_safe startup script, which then invokes the server with its output redirected to the error log. (That is, the server itself does not directly create the error log.) The default error log name is host_name.err in the data directory. (Older Windows servers use the name mysql.err instead.)

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.

All logs are written in text format except for the binary log which, as the name implies, is in binary format. 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 binary log, use the mysqlbinlog utility.

On Unix, the server records its process ID in a PID file, for use by other programs that need to send the server a signal. (Unix processes send signals to each other using process ID values.) The default PID filename is host_name.pid in the data directory. The name and location can 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 --socket so that they know where the socket file is located.