5.12. MySQL Server Logs

MySQL 5.0

5.12. MySQL Server Logs

MySQL has several different log files that can help you find out what is going on inside mysqld:

Log Type Information Written to Log
The error log Problems encountered starting, running, or stopping mysqld
The general query log Established client connections and statements received from clients
The binary log All statements that change data (also used for replication)
The slow log All queries that took more than seconds to execute or didn't use indexes

By default, all log files are created in the mysqld data directory. You can force mysqld to close and reopen the log files (or in some cases switch to a new log) by flushing the logs. Log flushing occurs when you issue a statement or execute mysqladmin flush-logs or mysqladmin refresh. See Section 13.5.5.2, “ Syntax”.

If you are using MySQL replication capabilities, slave replication servers maintain additional log files called relay logs. These are discussed in Chapter 6, Replication.

5.12.1. The Error Log

The error log file contains information indicating when mysqld was started and stopped and also any critical errors that occur while the server is running. If mysqld notices a table that needs to be automatically checked or repaired, it writes a message to the error log.

On some operating systems, the error log contains a stack trace if mysqld dies. The trace can be used to determine where mysqld died. See Section E.1.4, “Using a Stack Trace”.

If mysqld dies unexpectedly and mysqld_safe needs to restart it, mysqld_safe writes a message to the error log.

You can specify where mysqld stores the error log file with the ] option. If no value is given, mysqld uses the name .err and writes the file in the data directory. If you execute , the error log is renamed with the suffix and mysqld creates a new empty log file. (No renaming occurs if the option was not given.)

If you do not specify , or (on Windows) if you use the option, errors are written to , the standard error output. Usually this is your terminal.

On Windows, error output is always written to the file if is not given.

5.12.2. The General Query Log

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.

mysqld writes statements to the query log in the order that it receives them. This may be different from the order in which they are executed. This is in contrast to the the binary log, for which statements are written after they are executed, but before any locks are released. (Also, the query log contains all statements, whereas the binary log does not contain statements that only select data.)

To enable the general query log, start mysqld with the ] or ] option. If no value is given, the default name is .log in the data directory.

Server restarts and log flushing do not cause a new general query log file to be generated (although flushing closes and reopens it). On Unix, you can rename the file and create a new one by using the following commands:

shell> .log -old.log
shell> 
shell> -old.log 
shell> -old.log

On Windows, you cannot rename the log file while the server has it open. You must stop the server and rename the file, and then restart the server to create a new log file.

5.12.3. The Binary Log

The binary log contains all statements that update data or potentially could have updated it (for example, a which matched no rows). Statements are stored in the form of “events” that describe the modifications. The binary log also contains information about how long each statement took that updated data.

Note: The binary log has replaced the old update log, which is no longer available as of MySQL 5.0. The binary log contains all information that is available in the update log in a more efficient format and in a manner that is transaction-safe. If you are using transactions, you must use the MySQL binary log for backups instead of the old update log.

The binary log does not contain statements that do not modify any data. If you want to log all statements (for example, to identify a problem query), use the general query log. See Section 5.12.2, “The General Query Log”.

The primary purpose of the binary log is to be able to update databases during a restore operation as fully as possible, because the binary log contains all updates done after a backup was made. The binary log is also used on master replication servers as a record of the statements to be sent to slave servers. See Chapter 6, Replication.

Running the server with the binary log enabled makes performance about 1% slower. However, the benefits of the binary log for restore operations and in allowing you to set up replication generally outweigh this minor performance decrement.

When started with the ] option, mysqld writes a log file containing all SQL commands that update data. If no value is given, the default name is the name of the host machine followed by . If the basename is given, but not as an absolute pathname, the server writes the file in the data directory. It is recommended that you specify a basename; see Section A.8.1, “Open Issues in MySQL”, for the reason.

If you supply an extension in the log name (for example, ), the extension is silently removed and ignored.

mysqld appends a numeric extension to the binary log basename. The number increases each time the server creates a new log file, thus creating an ordered series of files. The server creates a new binary log file each time it starts or flushes the logs. The server also creates a new binary log file automatically when the current log's size reaches . A binary log file may become larger than if you are using large transactions because a transaction is written to the file in one piece, never split between files.

To keep track of which binary log files have been used, mysqld also creates a binary log index file that contains the names of all used binary log files. By default this has the same basename as the binary log file, with the extension . You can change the name of the binary log index file with the ] option. You should not manually edit this file while mysqld is running; doing so would confuse mysqld.

Writes to the binary log file and binary log index file are handled in the same way as writes to tables. See Section A.4.3, “How MySQL Handles a Full Disk”.

You can delete all binary log files with the statement, or a subset of them with . See Section 13.5.5.5, “ Syntax”, and Section 13.6.1, “SQL Statements for Controlling Master Servers”.

The binary log format has some known limitations that can affect recovery from backups. See Section 6.7, “Replication Features and Known Problems”.

Binary logging for stored routines and triggers is done as described in Section 17.4, “Binary Logging of Stored Routines and Triggers”.

You can use the following options to mysqld to affect what is logged to the binary log. See also the discussion that follows this option list.

If you are using replication, the options described here affect which statements are sent by a master server to its slaves. There are also options for slave servers that control which statements received from the master to execute or ignore. For details, see Section 6.8, “Replication Startup Options”.

  • Tell the server to restrict binary logging to updates for which the default database is (that is, the database selected by ). All other databases that are not explicitly mentioned are ignored. If you use this option, you should ensure that you do updates only in the default database.

    There is an exception to this for , , and statements. The server uses the database named in the statement (not the default database) to decide whether it should log the statement.

    An example of what does not work as you might expect: If the server is started with , and you run , this statement is not written into the binary log.

    To log multiple databases, use multiple options, specifying the option once for each database.

  • Tell the server to suppress binary logging of updates for which the default database is (that is, the database selected by ). If you use this option, you should ensure that you do updates only in the default database.

    As with the option, there is an exception for the , , and statements. The server uses the database named in the statement (not the default database) to decide whether it should log the statement.

    An example of what does not work as you might expect: If the server is started with , and you run , this statement is written into the binary log.

    To ignore multiple databases, use multiple options, specifying the option once for each database.

The server evaluates the options for logging or ignoring updates to the binary log according to the following rules. As described previously, there is an exception for the , , and statements. In those cases, the database being created, altered, or dropped replaces the default database in the following rules.

  1. Are there or rules?

    • No: Write the statement to the binary log and exit.

    • Yes: Go to the next step.

  2. There are some rules (, , or both). Is there a default database (has any database been selected by ?)?

    • No: Do not write the statement, and exit.

    • Yes: Go to the next step.

  3. There is a default database. Are there some rules?

    • Yes: Does the default database match any of the rules?

      • Yes: Write the statement and exit.

      • No: Do not write the statement, and exit.

    • No: Go to the next step.

  4. There are some rules. Does the default database match any of the rules?

    • Yes: Do not write the statement, and exit.

    • No: Write the query and exit.

For example, a slave running with only does not write to the binary log any statement for which the default database is different from (in other words, can sometimes mean “ignore other databases”).

If you are using replication, you should not delete old binary log files until you are sure that no slave still needs to use them. For example, if your slaves never run more than three days behind, once a day you can execute mysqladmin flush-logs on the master and then remove any logs that are more than three days old. You can remove the files manually, but it is preferable to use , which also safely updates the binary log index file for you (and which can take a date argument). See Section 13.6.1, “SQL Statements for Controlling Master Servers”.

A client that has the privilege can disable binary logging of its own statements by using a statement. See Section 13.5.3, “ Syntax”.

You can display the contents of binary log files with the mysqlbinlog utility. This can be useful when you want to reprocess statements in the log. For example, you can update a MySQL server from the binary log as follows:

shell>  | mysql -h 

See Section 8.10, “mysqlbinlog — Utility for Processing Binary Log Files”, for more information on the mysqlbinlog utility and how to use it. mysqlbinlog also can be used with relay log files because they are written using the same format as binary log files.

Binary logging is done immediately after a statement completes but before any locks are released or any commit is done. This ensures that the log is logged in execution order.

Updates to non-transactional tables are stored in the binary log immediately after execution. Within an uncommitted transaction, all updates (, , or ) that change transactional tables such as or tables are cached until a statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the is executed. When the thread that handles the transaction starts, it allocates a buffer of to buffer statements. If a statement is bigger than this, the thread opens a temporary file to store the transaction. The temporary file is deleted when the thread ends.

Modifications to non-transactional tables cannot be rolled back. If a transaction that is rolled back includes modifications to non-transactional tables, the entire transaction is logged with a statement at the end to ensure that the modifications to those tables are replicated.

The status variable shows the number of transactions that used this buffer (and possibly a temporary file) for storing statements. The status variable shows how many of those transactions actually had to use a temporary file. These two variables can be used for tuning to a large enough value that avoids the use of temporary files.

The system variable (default 4GB) can be used to restrict the total size used to cache a multiple-statement transaction. If a transaction is larger than this, it fails and rolls back.

If you are using the binary log, concurrent inserts are converted to normal inserts for or statement. This is done to ensure that you can re-create an exact copy of your tables by applying the log during a backup operation.

Note that the binary log format is different in MySQL 5.0 from previous versions of MySQL, due to enhancements in replication. See Section 6.5, “Replication Compatibility Between MySQL Versions”.

By default, the binary log is not synchronized to disk at each write. So if the operating system or machine (not only the MySQL server) crashes, there is a chance that the last statements of the binary log are lost. To prevent this, you can make the binary log be synchronized to disk after every writes to the binary log, with the system variable. See Section 5.2.2, “Server System Variables”. 1 is the safest value for , but also the slowest. Even with set to 1, there is still the chance of an inconsistency between the table content and binary log content in case of a crash. For example, if you are using tables and the MySQL server processes a statement, it writes the whole transaction to the binary log and then commits this transaction into . If the server crashes between those two operations, the transaction is rolled back by at restart but still exists in the binary log. This problem can be solved with the option, which adds consistency between the content of tables and the binary log. (Note: is unneeded as of MySQL 5.0; it was made obsolete by the introduction of XA transaction support.)

For this option to provide a greater degree of safety, the MySQL server should also be configured to synchronize the binary log and the logs to disk at every transaction. The logs are synchronized by default, and can be used to synchronize the binary log. The effect of this option is that at restart after a crash, after doing a rollback of transactions, the MySQL server cuts rolled back transactions from the binary log. This ensures that the binary log reflects the exact data of tables, and so, that the slave remains in synchrony with the master (not receiving a statement which has been rolled back).

Note that can be used even if the MySQL server updates other storage engines than . Only statements and transactions that affect tables are subject to removal from the binary log at 's crash recovery. If the MySQL server discovers at crash recovery that the binary log is shorter than it should have been, it lacks at least one successfully committed transaction. This should not happen if and the disk/filesystem do an actual sync when they are requested to (some don't), so the server prints an error message . In this case, this binary log is not correct and replication should be restarted from a fresh snapshot of the master's data.

5.12.4. The Slow Query Log

The slow query log consists of all SQL statements that took more than seconds to execute. The time to acquire the initial table locks is not counted as execution time. The minimum and default values of are 1 and 10, respectively.

mysqld writes a statement to the slow query log after it has been executed and after all locks have been released. Log order may be different from execution order.

To enable the slow query log, start mysqld with the ] option.

If no value is given, the default is the name of the host machine with a suffix of . If a filename is given, but not as an absolute pathname, the server writes the file in the data directory.

The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization. However, examining a long slow query log can become a difficult task. To make this easier, you can process the slow query log using the mysqldumpslow command to summarize the queries that appear in the log. Use mysqldumpslow --help to see the options that this command supports.

In MySQL 5.0, queries that do not use indexes are logged in the slow query log if the option is specified. See Section 5.2.1, “mysqld Command Options”.

In MySQL 5.0, the server option enables you to request logging of slow administrative statements such as , , and to the slow query log.

Queries handled by the query cache are not added to the slow query log, nor are queries that would not benefit from the presence of an index because the table has zero rows or one row.

5.12.5. Server Log Maintenance

MySQL Server can create a number of different log files that make it easy to see what is going on. See Section 5.12, “MySQL Server Logs”. However, you must clean up these files regularly to ensure that the logs do not take up too much disk space.

When using MySQL with logging enabled, you may want to back up and remove old log files from time to time and tell MySQL to start logging to new files. See Section 5.10.1, “Database Backups”.

On a Linux (Red Hat) installation, you can use the script for this. If you installed MySQL from an RPM distribution, this script should have been installed automatically. You should be careful with this script if you are using the binary log for replication. You should not remove binary logs until you are certain that their contents have been processed by all slaves.

On other systems, you must install a short script yourself that you start from cron (or its equivalent) for handling log files.

You can force MySQL to start using new log files by using mysqladmin flush-logs or by using the SQL statement .

A log flushing operation does the following:

  • If general query logging () or slow query logging () is used, the server closes and reopens the general query log file or slow query log file.

  • If binary logging () is used, the server closes the current log file and opens a new log file with the next sequence number.

The server creates a new binary log file when you flush the logs. However, it just closes and reopens the general and slow query log files. To cause new files to be created on Unix, rename the current logs before flushing them. At flush time, the server will open new logs with the original names. For example, if the general and slow query logs are named and , you can use a series of commands like this:

shell> 
shell> 
shell> 
shell> 

At this point, you can make a backup of and and then remove them from disk.

On Windows, you cannot rename log files while the server has them open. You must stop the server and rename them, and then restart the server to create new logs.