MySQL Reference Manual

The MySQL Log Files

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

Log file	Description
The error log	Problems encountered when starting, running, or stopping mysqld.
The isam log	Logs all changes to the ISAM tables. Used only for debugging the isam code.
The query log	Established connections and executed queries.
The update log	Deprecated: stores all statements that change data.
The binary log	Stores all statements that change something. Used also for replication.
The slow log	Stores all queries that took more than long_query_time to execute or didn’t use indexes.

All logs can be found in the mysqld data directory. You can force mysqld to reopen the log files (or in some cases switch to a new log) by executing FLUSH LOGS. See Section 4.5.3.

The Error Log

mysqld writes all errors to the stderr, which the safe_mysqld script redirects to a file called 'hostname'.err. (On Windows, mysqld writes this directly to \mysql\data\mysql.err.)

This contains information indicating when mysqld was started and stopped and also any critical errors found when running. If mysqld dies unexpectedly and safe_mysqld needs to restart mysqld, safe_mysqld will write a restarted mysqld row in this file. This log also holds a warning if mysqld notices a table that needs to be automatically checked or repaired.

On some operating systems, the error log will contain a stack trace that can be used to find out where mysqld died. See Section D.1.4.

The General Query Log

If you want to know what happens within mysqld, you should start it with --log[=file]. This will log all connections and queries to the log file (by default named 'hostname'.log). This log can be very useful when you suspect an error in a client and want to know exactly what mysqld thought the client sent to it.

By default, the mysql.server script starts the MySQL server with the -l option. If you need better performance when you start using MySQL in a production environment, you can remove the -l option from mysql.server or change it to --log-bin.

The entries in this log are written as mysqld receives the questions. This may be different from the order in which the statements 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.

The Update Log

Note: the update log is replaced by the binary log. See Section 4.9.4. With this you can do anything that you can do with the update log.

When started with the --log-update[=file_name] option, mysqld writes a log file containing all SQL commands that update data. If no filename is given, it defaults to the name of the host machine. If a filename is given, but it doesn’t contain a path, the file is written in the data directory. If file_name doesn’t have an extension, mysqld will create log file names like so: file_name.###, where ### is a number that is incremented each time you execute mysqladmin refresh, execute mysqladmin flush-logs, execute the FLUSH LOGS statement, or restart the server.

Note: For the previous scheme to work, you must not create your own files with the same filename as the update log + some extensions that may be regarded as a number, in the directory used by the update log!

If you use the --log or -l options, mysqld writes a general log with a filename of hostname.log. Restarts and refreshes do not cause a new log file to be generated (although it is closed and reopened). In this case you can copy it (on Unix) by doing:

mv hostname.log hostname-old.log
mysqladmin flush-logs
cp hostname-old.log to-backup-directory
rm hostname-old.log

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 the log will be logged in the execution order.

If you want to update a database from update log files, you could do the following (assuming 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 get all the log files in 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.

The Binary Update Log

The intention is that the binary log should replace the update log, so we recommend you switch to this log format as soon as possible!

The binary log contains all information that is available in the update log in a more efficient format. It also contains information about how long every query that updated the database took.

The binary log is also used when you are replicating a slave from a master. See Section 4.10.

When started with the --log-bin[=file_name] option, mysqld writes a log file containing all SQL commands that update data. If no filename is given, it defaults to the name of the host machine followed by -bin. If a filename is given, but it doesn’t contain a path, the file is written in the data directory.

If you supply an extension to --log-bin=filename.extension, the extension will be silently removed.

To the binary log filename, mysqld will append an extension that is a number that is incremented each time you execute mysqladmin refresh, execute mysqladmin flush-logs, execute the FLUSH LOGS statement, or restart the server. A new binary log will also automatically be created when it reaches max_bin_log_size. You can delete all inactive binary log files with the RESET MASTER command. See Section 4.5.4.

You can use the following options to mysqld to affect what is logged to the binary log:

Option	Description
binlog-do-db=database_name	Tells the master it should log updates for the specified database, and exclude all others not explicitly mentioned. (Example: binlog-do-db=some_database.)
binlog-ignore-db=database_name	Tells the master that updates to the given database should not be logged to the binary log. (Example: binlog-ignore-db=some_database.)

To determine which different binary log files have been used, mysqld will also create 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=[filename] option.

If you are using replication, you should not delete old binary log files until you are sure that no slave will ever need 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 3 days old.

You can examine the binary log file with the mysqlbinlog command. For example, you can update a MySQL server from the binary log as follows:

mysqlbinlog log-file | mysql -h server_name

You can also use the mysqlbinlog program to read the binary log directly from a remote MySQL server!

mysqlbinlog --help will give you more information on how to use this program!

If you are using BEGIN [WORK] or SET AUTOCOMMIT=0, 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.

All updates (UPDATE, DELETE, or INSERT) that change a transactional table (like BDB tables) are cached until a COMMIT. Any updates to a non-transactional table are stored in the binary log at once. Every thread will, on start, allocate a buffer of binlog_cache_size to buffer queries. If a query is bigger than this, the thread will open a temporary file to handle the bigger cache. The temporary file will be deleted when the thread ends.

The max_binlog_cache_size can be used to restrict the total size used to cache a multi-transaction query.

If you are using the update or binary log, concurrent inserts will not work together with CREATE ... INSERT and 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 Slow Query Log

When started with the --log-slow-queries[=file_name] option, mysqld writes a log file containing all SQL commands that took more than long_query_time to execute. The time to get the initial table locks is not counted as execution time.

The slow query log is logged after the query is executed and after all locks have been released. This may be different from the order in which the statements are executed.

If no filename is given, it defaults to the name of the host machine suffixed with -slow.log. If a filename is given, but doesn’t contain a path, the file is written in the data directory.

The slow query log can be used to find queries that take a long time to execute and are thus candidates for optimisation. With a large log, that can become a difficult task. You can pipe the slow query log through the mysqldumpslow command to get a summary of the queries that appear in the log.

If you are using --log-long-format, queries that are not using indexes are also printed. See Section 4.1.1.

Log File Maintenance

MySQL has a lot of log files, which makes it easy to see what is going on. See Section 4.9. One must, however, from time to time clean up after MySQL to ensure that the logs don’t take up too much disk space.

When using MySQL with log files, you will, from time to time, want to remove/back up old log files and tell MySQL to start logging on new files. See Section 4.4.1.

On a Linux (RedHat) 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. Note that you should be careful with this if you are using the log for replication!

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 command FLUSH LOGS. If you are using MySQL Version 3.21 you must use mysqladmin refresh.

The preceding command does the following:

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

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

If you are using only an update log, you only have to flush the logs and then move away the old update log files to a backup. If you are using normal logging, you can do something like:

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

and then take a backup and remove mysql.old.