MySQL Administrators Guide [Electronic resources] نسخه متنی

4.8 The MySQL Log Files

MySQL has several different log files that can help you find out what's going on inside mysqld:Chapter 5, "Replication in MySQL."

4.8.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 dies unexpectedly and mysqld_safe needs to restart it, mysqld_safe will write a restarted mysqld message to the error log. 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 will contain a stack trace if mysqld dies. The trace can be used to determine where mysqld died.

Beginning with MySQL 4.0.10, 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. (Prior to MySQL 4.0.10, the Windows error log name is mysql.err.) If you execute FLUSH LOGS, the error log is renamed with a suffix of -old and mysqld creates a new empty log file.

In older MySQL versions on Unix, error log handling was done by mysqld_safe which redirected the error file to host_name.err. You could change this filename by specifying a --err-log=filename option to mysqld_safe.

If you don't 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.

4.8.2 The General Query Log

If you want to know what happens within mysqld, you should start it with the --log[=file_name] or -l [file_name] option. If no file_name value is given, the default name is host_name.log This will log all connections and statements to the log file. This log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.

Older versions of the mysql.server script (from MySQL 3.23.4 to 3.23.8) pass a --log option to safe_mysqld to enable the general query log. If you need better performance when you start using MySQL in a production environment, you can remove the --log option from mysql.server or change it to --log-bin. See Section 4.8.4, "The Binary Log."

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 update log and the binary log, which are written after the query is executed, but before any locks are released.

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 hostname.log hostname-old.log
shell> mysqladmin flush-logs
shell> cp hostname-old.log to-backup-directory
shell> rm hostname-old.log

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

4.8.3 The Update Log

Note:
The update log has been deprecated and replaced by the binary log. See Section 4.8.4, "The Binary Log." The binary log can do anything the old update log could do, and more. The update log is unavailable as of MySQL 5.0.0.

When started with the --log-update[=file_name] option, mysqld writes a log file containing all SQL statements that update data. If no file_name value is given, the default name is name of the host machine. If a filename is given, but it doesn't contain a leading path, the file is written in the data directory. If file_name doesn't have an extension, mysqld creates log files with names of the form file_name.###, where ### is a number that is incremented each time you start the server or flush the logs.

Note:
For this naming scheme to work, you must not create your own files with the same names as those that might be used for the log file sequence.

Update logging is smart because it logs only statements that really update data. So, an UPDATE or a DELETE with a WHERE that finds no rows is not written to the log. It even skips UPDATE statements that set a column to the value it already has.

The update logging is done immediately after a query completes but before any locks are released or any commit is done. This ensures that statements are logged in execution order.

If you want to update a database from update log files, you could do the following (assuming that your update logs have names of the form file_name.###):

shell> ls -1 -t -r file_name.[0-9]* | xargs cat | mysql

ls is used to sort the update log filenames into the right order.

This can be useful if you have to revert to backup files after a crash and you want to redo the updates that occurred between the time of the backup and the crash.

4.8.4 The Binary Log

The binary log has replaced the old update log, which is unavailable starting from 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 transactionally safe.Chapter 5, "Replication in MySQL."

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[=file_name] option, mysqld writes a log file containing all SQL commands that update data. If no file_name value is given, the default name is the name of the host machine followed by -bin. If the file name is given, but it doesn't contain a path, the file is written in the data directory.

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

mysqld appends a numeric extension to the binary log name. The number is incremented each time you start the server or flush the logs. A new binary log also is created automatically when the current log's size reaches max_binlog_size. A binary log may become larger than max_binlog_size if you are using large transactions: A transaction is written to the binary log in one piece, never split between binary logs.

To be able to know which different binary log files have been used, mysqld also creates a binary log index file that contains the name of all used binary log files. By default this has the same name 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.

You can delete all binary log files with the RESET MASTER statement, or only some of them with PURGE MASTER LOGS.

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.

--binlog-do-db=db_name

Tells the master that it should log updates to the binary log if the current database (that is, the one selected by USE) is db_name. All other databases that are not explicitly mentioned are ignored. If you use this, you should ensure that you only do updates in the current database.

An example of what does not work as you might expect: If the server is started with binlog-do-db=sales, and you do USE prices; UPDATE sales.january SET amount=amount+1000;, this statement will not be written into the binary log.

--binlog-ignore-db=db_name

Tells the master that updates where the current database (that is, the one selected by USE) is db_name should not be stored in the binary log. If you use this, you should ensure that you only do updates in the current database.

An example of what does not work as you might expect: If the server is started with binlog-ignore-db=sales, and you do USE prices; UPDATE sales.january SET amount=amount+1000;, this statement will be written into the binary log.

To log or ignore multiple databases, specify the appropriate option multiple times, once for each database.

The rules for logging or ignoring updates to the binary log are evaluated in the following order:

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 or binlog-ignore-db or both). Is there a current 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 current database. Are there some binlog-do-db rules?

Yes: Does the current 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 current 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 will not write to the binary log any statement whose current 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. One way to do this is to do mysqladmin flush-logs once a day and then remove any logs that are more than three days old. You can remove them manually, or preferably using PURGE MASTER LOGS, which will also safely update the binary log index file for you (and which can take a date argument since MySQL 4.1).

A client with the SUPER privilege can disable binary logging of its own statements by using a SET SQL_LOG_BIN=0 statement.

You can examine the binary log file 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 7.5, "The mysqlbinlog Binary Log Utility," for more information on the mysqlbinlog utility and how to use it.

If you are using transactions, you must use the MySQL binary log for backups instead of the old update log.

The binary logging is done immediately after a query completes but before any locks are released or any commit is done. This ensures that the log will be logged in the execution order.

Updates to non-transactional tables are stored in the binary log immediately after execution. For transactional tables such as BDB or InnoDB tables, all updates (UPDATE, DELETE, or INSERT) that change tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the whole 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 queries. 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.

The max_binlog_cache_size (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 will fail and roll back.

If you are using the update log or binary log, concurrent inserts will be converted to normal inserts when using CREATE ... SELECT or INSERT ... SELECT. This is to ensure that you can re-create an exact copy of your tables by applying the log on a backup.

The binary log format is different in versions 3.23, 4.0, and 5.0.0. Those format changes were required to implement enhancements to replication. MySQL 4.1 has the same binary log format as 4.0. See Section 5.5, "Replication Compatibility Between MySQL Versions."

4.8.5 The Slow Query Log

When started with the --log-slow-queries[=file_name] option, mysqld writes a log file containing all SQL statements that took more than long_query_time seconds to execute. The time to acquire the initial table locks are not counted as execution time.Section 4.2.1, "mysqld Command-Line Options."

4.8.6 Log File Maintenance

The MySQL Server can create a number of different log files that make it easy to see what is going on. See Section 4.6.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, the 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 to handle 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. If you are using MySQL 3.21, you must use mysqladmin refresh.

A log flushing operation does the following:

If standard logging (--log) or slow query logging (--log-slow-queries) is used, closes and reopens the log file (mysql.log and `hostname`-slow.log as default).

If update logging (--log-update) or binary logging (--log-bin) is used, closes the log and opens a new log file with a higher sequence number.

If you are using only an update log, you only have to rename the old log file and then flush the logs before making a backup. For example, you can do something like this:

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

Then make a backup and remove mysql.old.