What are the different log files available in MySQL

Log Files

MySQL has different log files to record server activity. Log files can consume a large amount of disk space. It can be stored in either file or in tables (General and slow query logs only). Most of the log files are written in text format except for the binary logs.

Various MySQL log files record information about the SQL statements processed by the server. For example:

Error log: Diagnostic messages regarding startups, shutdowns, and abnormal conditions
General query log: All statements that the server receives from clients
Slow query log: Queries that take a long time to execute
Binary log: Statements that modify data
Audit log: Policy-based audit for Enterprise edition

Use these logs to assess the operational state of the server, for data recovery after a crash, for replication purposes, to help determine which queries are running slowly, and for security and regulatory compliance. None of these logs is enabled by default (except the error log in Windows). It is important to understand that log files, particularly the general query log, can grow to be quite large.

For more information about log files, see the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/server-logs.html and http://dev.mysql.com/doc/mysql/en/mysql-enterprise-audit.html

Log File Usage Matrix

Log File	Options	File or Table Name	Programs
Error	–log-error	host_name.err	N/A
General	–general_log	host_name.log/general_log	N/A
Slow Query	–slow_query_log and –long_query_time	host_name-slow. Log/slow_log	mysqldumpslow
Binary	–log-bin and –expire-logs-days	host_name-bin.000001	mysqlbinlog
Audit	–audit_log and –audit_log_file	audit.log	N/A

The server creates all the log files in the data directory and sets the file name to the current hostname if you do not set another pathname. You can enable these log files by starting the server with the corresponding options (on the command line or in an option file without the preceding ‘–’).

Error log: Set –log-error=[file_name] to log errors to the given file. The mysqld_safe script creates the error log and starts the server with its output redirected to the error log.
General query log: Set –general_log_file=[file_name] to log queries. The global general_log and general_log_file server variables provide runtime control over the general query log. Set general_logto 0 (or OFF) to disable the log, or to 1 (or ON) to enable it.
Slow query log: Set –slow_query_log_file=[file_name] to provide runtime control over the slow query log. Set slow_query_log to 0 to disable the log, or to 1 to enable it. If a log file is already open, it is closed and the new file is opened.
Binary log: Set –log-binto enable binary logging. The server uses the option value as a base name, adding an increasing sequential numeric suffix to the base name as it creates new log files. These log files are stored in binary format rather than text format.
Audit log: The audit log is provided as an Enterprise Edition plugin that, when loaded, you can use to log events into the file specified by the audit_log_file option. Auditing constantly writes to the audit log until you remove the plugin, or you turn off the auditing with the audit_log_policy=NONEoption setting. You can prevent the removal of the plugin by using audit_log=FORCE_PLUS_PERMANENTas an option when the server is started. For detailed information about all available audit plugin options, see the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/audit-log-plugin-optionsvariables.html

If the general or slow query logs are enabled, they can output log entries to a file, a table in the mysql database, or both, by setting the –log-output option. See the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/log-destinations.html. For detailed information on the options available for all of the above log types, see the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/server-logs.html

Binary Logging

The binary log contains “events” that describe database changes such as table creation or changes to table data. It also contains events for statements that could have potentially made changes (for example, a DELETE that matched no rows). It also contains information about how long each update statement took. The binary log has two important purposes: replication and data recovery.

MySQL uses a log-shipping replication solution. With the log-shipping system, you can store all data changes that occur on the masterin a binary log and then retrieve them with the slave, and execute from these received log files. You can download log files and execute the contents in real time; that is, as soon as a log file event is generated, it is sent to the connected slaves for execution. Due to network propagation delays, it can take from a few seconds to a few minutes (worst case) for the slave to receive the updates. In ideal situations, the delay is well under one second.

The binary log rotates when one of the following events occurs:

The MySQL server is restarted.
The maximum allowed size is reached (max_binlog_size).
A FLUSH LOGS SQL command is issued.

The binary log is independent of the storage engine. MySQL replication works regardless of the storage engine that is being used (that is, InnoDB or MyISAM).

Binary Logging Formats

There are two different types of logging to choose from:

Parameter	Statement-Based	Row-Based
Size of log files	Small	Large
Replication limitations	Not all statements can be replicated.	All statements can be replicated.
Master/Slave MySQL versions	Slave can be a newer version with a different row structure.	Slave must be an identical version and row structure.
Locking	INSERT and SELECT require a greater number of row locks.	INSERT, UPDATE, and DELETE require fewer locks on slaves.
Point-in-time recovery	Yes	Yes (more difficult due to binary format of log events)

Statement-based binary logging:

Contains the actual SQL statements
Includes both DDL (CREATE, DROP, and so on) and DML (UPDATE, DELETE, and so on) statements
The relatively small files save disk space and network bandwidth.
Not all replicated statements replay correctly on a remote machine.
Requires that replicated tables and columns be identical (or compatible with several restrictions) on both master and slave

Row-based binary logging:

Indicates how individual table rows are affected
Replays all statements correctly, even for changes caused by features that do not replicate correctly when using statement-based logging

Set the format as follows:

SET [GLOBAL|SESSION] BINLOG_FORMAT=[row|statement|mixed|default];

Note: Use the mixed option to cause MySQL to pick the most appropriate format for individual events. It generally uses the statement-based binary log, but it reverts to the rowbased replication when required.

List Binary Log Files

Statements are stored in the binary logs in the form of “events” that describe the database modifications. To identify an event, you must have the binary log file name and the byte offset (or position). For example, the log file binlog.000016, position 733481 would identify the last event in the slide. The SHOW MASTER STATUS command requires either the SUPER or the REPLICATION CLIENT privilege.

- Use the SHOW BINARY LOGS statement to list current log files and file size:

mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000015 |    724935 |
| binlog.000016 |    733481 |
+---------------+-----------+

- Use the SHOW MASTER STATUS statement to show the master status for the next event:

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000016 |   733481 | world_innodb |     manual,mysql |
+---------------+----------+--------------+------------------+

View Binary Log Contents

The binary logs annot be viewed with normal text viewers and are stored in compact binary format. The mysqlbinlog program is executed from the command line, specifying as parameters the logs to be viewed. There are several options for this program, providing features such as the ability to extract events based on time or file offset. You can view all options with the –help flag.

- View the data in standard out:

shell> mysqlbinlog host-bin.000001 host-bin.000002

- Redirect output to more:

shell> mysqlbinlog host-bin.000001 | more

In a mysqlbinlog output, events are preceded by header comments that provide information:

# at 141
#100309  9:28:36 server id 123  end_log_pos 245
Query thread_id=3350  exec_time=11  error_code=0

Cannot be viewed with normal text viewers:

If you create a binary log by using statement-based logging, and if you then run mysqlbinlog –database=test to create a readable file, the server filters out statements that are not associated with the test database:

INSERT INTO test.t1 (i) VALUES(100);
----------------------------------------
| USE test;                            |
| INSERT INTO test.t1 (i) VALUES(101); |
| INSERT INTO t1 (i)      VALUES(102); |
| INSERT INTO db2.t2 (j)  VALUES(201); |
----------------------------------------
USE db2;
INSERT INTO db2.t2 (j)  VALUES(202);

The first INSERT statement is not included because there is no default database. It outputs the three INSERTs following USE test, but not the INSERT following USE db2.

Deleting Binary Logs

By default, old log files are not deleted. To automatically delete any binary logs older than a specific number of days during a binary log rotation, use the expire_logs_dayssetting:

SET GLOBAL expire_logs_days = 7;

…or…

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

You can also configure expire_logs_daysin the option file:

[mysqld]
expire_logs_days=7

Delete logs based on file name:

PURGE BINARY LOGS TO 'mysql-bin.000010';