5.12. MySQL Server Logs

MySQL 5.0

previous page next page

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 `long_query_time` 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 FLUSH LOGS statement or execute mysqladmin flush-logs or mysqladmin refresh. See Section 13.5.5.2, “FLUSH 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 restarted mysqld message to the error log.

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

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

On Windows, error output is always written to the .err file if --console 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 --log[=file_name] or -l [file_name] option. If no file_name value is given, the default name is host_name.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> mv host_name.log host_name-old.log shell> mysqladmin flush-logs shell> cp host_name-old.log backup-directory shell> rm host_name-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 DELETE 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 --log-bin[=base_name] option, mysqld writes a log file containing all SQL commands that update data. If no base_name value is given, the default name is the name of the host machine followed by -bin. 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, --log-bin=base_name.extension), 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 max_binlog_size. A binary log file may become larger than max_binlog_size 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 '.index'. You can change the name of the binary log index file with the --log-bin-index[=file_name] 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 MyISAM tables. See Section A.4.3, “How MySQL Handles a Full Disk”.

You can delete all binary log files with the RESET MASTER statement, or a subset of them with PURGE MASTER LOGS. See Section 13.5.5.5, “RESET 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”.

--binlog-do-db=db_name

Tell the server to restrict binary logging to updates for which the default database is db_name (that is, the database selected by USE). 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 CREATE DATABASE, ALTER DATABASE, and DROP DATABASE 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 binlog-do-db=sales, and you run USE prices; UPDATE sales.january SET amount=amount+1000;, this statement is not written into the binary log.

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

--binlog-ignore-db=db_name

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

As with the --binlog-do-db option, there is an exception for the CREATE DATABASE, ALTER DATABASE, and DROP DATABASE 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 binlog-ignore-db=sales, and you run USE prices; UPDATE sales.january SET amount=amount+1000;, 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 CREATE DATABASE, ALTER DATABASE, and DROP DATABASE statements. In those cases, the database being created, altered, or dropped replaces the default database in the following rules.

Are there --binlog-do-db or --binlog-ignore-db rules?

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

Yes: Go to the next step.

There are some rules (--binlog-do-db, --binlog-ignore-db, or both). Is there a default database (has any database been selected by USE?)?

No: Do not write the statement, and exit.

Yes: Go to the next step.

There is a default database. Are there some --binlog-do-db rules?

Yes: Does the default database match any of the --binlog-do-db rules?

Yes: Write the statement and exit.

No: Do not write the statement, and exit.

No: Go to the next step.

There are some --binlog-ignore-db rules. Does the default database match any of the --binlog-ignore-db rules?

Yes: Do not write the statement, and exit.

No: Write the query and exit.

For example, a slave running with only --binlog-do-db=sales does not write to the binary log any statement for which the default database is different from sales (in other words, --binlog-do-db 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 PURGE MASTER LOGS, 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 SUPER privilege can disable binary logging of its own statements by using a SET SQL_LOG_BIN=0 statement. See Section 13.5.3, “SET 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> mysqlbinlog log_file | mysql -h server_name

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 (UPDATE, DELETE, or INSERT) that change transactional tables such as BDB or InnoDB tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the COMMIT is executed. When the thread that handles the transaction starts, it allocates a buffer of binlog_cache_size 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 ROLLBACK statement at the end to ensure that the modifications to those tables are replicated.

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

The max_binlog_cache_size 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 CREATE ... SELECT or INSERT ... SELECT 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 N writes to the binary log, with the sync_binlog system variable. See Section 5.2.2, “Server System Variables”. 1 is the safest value for sync_binlog, but also the slowest. Even with sync_binlog 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 InnoDB tables and the MySQL server processes a COMMIT statement, it writes the whole transaction to the binary log and then commits this transaction into InnoDB. If the server crashes between those two operations, the transaction is rolled back by InnoDB at restart but still exists in the binary log. This problem can be solved with the --innodb-safe-binlog option, which adds consistency between the content of InnoDB tables and the binary log. (Note: --innodb-safe-binlog 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 InnoDB logs to disk at every transaction. The InnoDB logs are synchronized by default, and sync_binlog=1 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 InnoDB transactions from the binary log. This ensures that the binary log reflects the exact data of InnoDB tables, and so, that the slave remains in synchrony with the master (not receiving a statement which has been rolled back).

Note that --innodb-safe-binlog can be used even if the MySQL server updates other storage engines than InnoDB. Only statements and transactions that affect InnoDB tables are subject to removal from the binary log at InnoDB'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 InnoDB transaction. This should not happen if sync_binlog=1 and the disk/filesystem do an actual sync when they are requested to (some don't), so the server prints an error message The binary log <name> is shorter than its expected size. 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 long_query_time seconds to execute. The time to acquire the initial table locks is not counted as execution time. The minimum and default values of long_query_time 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 --log-slow-queries[=file_name] option.

If no file_name value is given, the default is the name of the host machine with a suffix of -slow.log. 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 --log-queries-not-using-indexes option is specified. See Section 5.2.1, “mysqld Command Options”.

In MySQL 5.0, the --log-slow-admin-statements server option enables you to request logging of slow administrative statements such as OPTIMIZE TABLE, ANALYZE TABLE, and ALTER TABLE 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 mysql-log-rotate 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 FLUSH LOGS.

A log flushing operation does the following:

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

If binary logging (--log-bin) 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 mysql.log and mysql-slow.log, you can use a series of commands like this:

shell> cd mysql-data-directory shell> mv mysql.log mysql.old shell> mv mysql-slow.log mysql-slow.old shell> mysqladmin flush-logs

At this point, you can make a backup of mysql.old and mysql-slow.log 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.

previous page start next page