Logging (Managing and Using MySQL, 2nd Edition)

4.3. Logging

The MySQL server is capable of producing a number of useful log files:[19]

[19]Old versions of MySQL support another kind of log, the update log. This log has been made obsolete by the binary log. If you are still using the update log, we recommend that you switch to the binary log.

Error
Query
Binary
Slow query

MySQL produces the error log if you use mysql.server or safe_mysqld to start MySQL. You can enable any or all of the others if you wish. By default, MySQL writes the log files to the data directory.

4.3.1. The Error Log

The error log contains the redirected output from the safe_mysqld script. On Unix, it is a file called hostname.err. On Windows, it is called mysql.err. This file contains an entry for each startup and shutdown of the server, including an entry for every time the server restarted because it died. Critical errors and warnings about tables that need to be checked or repaired also appear here.

4.3.2. The Binary Log

The binary log contains all SQL commands that update data. MySQL logs only statements that actually change data. For example, a delete that fails to affect any rows is not logged. Updates that set column values to their current values are also not logged. MySQL logs updates in execution order.

The binary log is useful for journaling all update operations since the last backup. For example, if you back up your database once a day, and your database crashes in the middle of the day, you can restore the database up to the last completed transaction by:

Restoring the database (see Section 4.4 and Section 4.5 in this chapter for more information on this task)
Applying the transactions from all binary logs since the last backup

Enable the binary log through the --log-bin=file option. If you specify no filename, MySQL uses the file hostname-bin. If you specify a relative path, the path is assumed to be relative to the data directory. MySQL appends a numeric index to the filename so that the file ends up being filename.number; e.g., hostname-bin.2. MySQL uses the index for rotating the files and rotates to the next index under the following conditions:

The server is restarted.
The server is refreshed.
The log reaches the maximum log size.
The logs are flushed.

MySQL also creates an index file that contains a list of all used binary log files. By default, this file is called hostname-bin.index. You may change the name and/or location of the index with the --log-bin-index=file option.

The mysqlbinlog utility enables you to read the binary log. The example below illustrates how the binary log works. This example assumes we have started MySQL on a host called odin and specified log-bin in the global configuration file, /etc/my.cnf. MySQL places the following files in the data directory:

$ cd /usr/local/mysql/data
$ ls -l
.
.
-rw-rw----   1  mysql       mysql      73 Aug   5  17:06 odin-bin.001
-rw-rw----   1  mysql       mysql      15 Aug   5  17:06 odin-bin.index
.
.

Inspecting odin-bin.index, we see:

$ cat odin-bin.index
./odin-bin.001
$

The index tells us there is one binary log file named odin-bin.001 in the same directory as the index. The mysqlbinlog utility lets us read the actual log:

$ mysqlbinlog odin-bin.001
# at 4
#010805 17:06:00 server id 1   Start: binlog v 1, server 3.23.40-log created 010805 
17:06:00
$

As clients perform updates on the database, the binary log grows. At the mysql command prompt, for example, suppose you issue the following command:

$ mysql
mysql> USE test;
mysql> INSERT INTO test ( object_id, object_title ) VALUES (1, 'test');
Query OK, 1 row affected (0.02 sec)
mysql> QUIT;
Bye
$

The binary log now looks like this:

$ mysqlbinlog odin-bin.001
# at 4
#010805  17:06:00 server id 1   Start: binlog v 1, server 3.23.40-log created 010805 
17:06:00
# at 73
#010805  17:39:38 server id 1   Query   thread_id=2    exec_time=0    error_code=0
USE test;
SET TIMESTAMP=997058378;
INSERT INTO test ( object_id, object_title ) VALUES (1, 'test');
$

After the SQL update, the binary log contains the equivalent update. You can flush the log to start a new binary log:

$ mysqladmin -u root -ppassword flush-logs

The result is a new file called odin-bin.002 where all new updates are logged. The odin-bin.index file is also updated to reflect the new log.

You can reexecute the commands in a binary log by piping the output from mysqlbinlog to the mysql client tool:

$ mysqlbinlog odin-bin.001 | mysql

MySQL provides a few options to help you control the binary logs. The option --binlog-do-db=dbname tells MySQL to log updates for only the specified database. The option --binlog-ignore-db=dbname tells MySQL to ignore the specified database for the purpose of binary logging.

4.3.3. The Slow Query Log

The slow query log contains a record of all SQL commands that took longer than the time specified in the system variable long_query_time. This log can help identify problem queries and expose parts of your database or application that need tuning.

You can enable the slow query log with the --log-slow-queries=filename option. If no filename is provided, it defaults to hostname-slow.log. If no directory is given, the directory defaults to the data directory. The long_query_time variable can be set using the --set-variable long_query_time=time command-line option (in which time is specified in seconds).

4.3.4. Log Rotation

No matter which log files you choose to enable, you need to maintain them so they do not fill up a filesystem.

Unfortunately, none of the techniques we discuss here work for the error log. Since it is written from the safe_mysqld script, it is not controlled by the MySQL server and the flush-logs command does not flush it. Also note that safe_mysqld will continue to append to it on successive restarts. You may want to modify the startup or shutdown scripts for your MySQL server to maintain the error log.

If you are running RedHat Linux, you can use mysql-log-rotate to rotate logs. It can be found in the support-files directory of your MySQL installation. It uses the logrotate utility to automatically rotate your error log for you. To install mysql-log-rotate on RedHat Linux, simply copy it to /etc/logrotate.d. You may wish to edit the script to rotate other logs that you have enabled as well. By default, it rotates only the query log. For more information on logrotate, read the manpage or refer to your RedHat documentation.

If you installed MySQL on Linux using the RPM package, mysql-log-rotate may have already been installed on your system. The RPM installer renames mysql-log-rotate to mysql when it copies it to /etc/logrotate.d. If the file /etc/logrotate.d/mysql is already there, it has already been installed.

On systems other than RedHat, you will have to devise your own scripts for rotating the logs. Depending on which logs you have enabled and how you want them located, the scripts could range from very simple to very complex. In general, the procedure is to copy the log files out of the way and use mysqladmin flush-logs to reinitialize the logs.