22 The MySQL log files

MySQL has a lot of different log files which can help you find out what's going on inside mysqld:

All logs can be found in the mysqld database directory. You can tell force mysqld to switch to reopen the log files (or in some cases switch to a new log) by executing FLUSH LOGS. See section 7.26 FLUSH Syntax (Clearing Caches).

22.1 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.err').

This contains information on 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 automaticly checked or repaired.

On some operating systems, the error log will contain a stack trace for where mysqld died. This can be used to find out where mysqld died. See section I.1.3 Using a stack trace.

22.2 The 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 send 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-binary.

The entries in this log are written as mysqld receives the questions. This may be different than the order in which the statements is executed. This is in contrast to the update log and the binary log which is written after the query is executed, but before any locks are released.

22.3 The Update Log

NOTE: The update log is replaced by the binary log. See section 22.4 The Binary Log. 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 of type 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 above scheme to work, you should 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', and 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.

22.4 The Binary Log

In the future the binary log will replace the update log, so we recommend you to 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 11 Replication in MySQL.

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 is given, it defaults to the name of the host machine followed by -bin. If file name is given, but it doesn't contain a path, the file is written in the data directory.

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.

To be able to know 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 mysqlbinlib 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 direct from a remote mysql server!

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

If you are using BEGIN or SET AUTO_COMMIT=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 changes a transactional table (like BDB tables) is cached until a COMMIT. Any updates to a not transactional table is 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.

22.5 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 are not counted as execution time.

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

If no file name 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 takes a long time to execute and are thus candidates for optimization.

You are using --log-long-format then also queries that are not using indexes are printed. See section 4.16.4 Command-line Options.

You can also use the update logs when you have a mirrored database on another host and you want to replicate the changes that have been made to the master database. See section 21.1 Database Replication with Update Log.

Go to the first, previous, next, last section, table of contents.