MENU


Logging

MySQL maintains several distinct logs, each serving a different administrative purpose: debugging the Workbench client, recording server errors, auditing every statement executed, flagging slow statements, supporting replication and point-in-time recovery, and tracking storage-engine-internal transaction state.

On Windows, unless specified otherwise during installation, logs are found in the hidden directory C:\ProgramData\MySQL\MySQL Server 8.0\Data\ (accessing it requires launching the command prompt as Administrator). On Linux, logs are typically stored in /var/log/mysql/ or /var/lib/mysql/.

Workbench Log

C:\Users\<username>\.config\MySQL\Workbench\wb.log is created by MySQL Workbench when it starts. It contains debugging information such as paths used, modules and plugins loaded, and system information – useful when reporting a Workbench bug.

Error Log

The error log records errors occurring during the server's operation: syntax errors, connection failures, and server crashes. The file is commonly named hostname.err. There is no specific SQL command to view it directly; on Windows, view it via MySQL Workbench launched as an administrator.

General Query Log

The general query log records every SQL statement executed by the server. It exists as a file or a table, depending on the log_output system variable, and can be very large with a negative performance impact if left enabled. By default it is named hostname.log and is disabled.

SET GLOBAL general_log = 1;
SET GLOBAL log_output = 'TABLE';

SHOW VARIABLES LIKE 'general_log%';

Enables the general query log, routes it to a table instead of a file, and checks the resulting variables. log_output accepts FILE (default), TABLE, or NONE.

To view the log, open the log file (for FILE) or query the built-in mysql database (for TABLE):

SELECT event_time, CAST(argument AS CHAR) FROM mysql.general_log;

TRUNCATE TABLE mysql.general_log;

Reading logged statements, then emptying the table once it grows too large.

Slow Query Log

The slow query log records statements that take longer than a specified threshold to execute, useful for identifying performance bottlenecks. It is disabled by default and, like the general query log, can be stored as a file or a table.

SET PERSIST slow_query_log = 'ON';
SET PERSIST log_output = 'FILE';

SELECT * FROM mysql.slow_log;
SHOW VARIABLES LIKE '%slow%';

Enabling the slow query log and inspecting it. Its default file is named hostname-slow.log.

SET GLOBAL long_query_time = 3; -- seconds

Changes the slow-query threshold from its 10-second default.

Binary Log

The binary log records all changes to the database – both data and structural changes – in binary format, essential for Replication and for point-in-time recovery. On Windows, binary log files are typically named hostname-bin.#, where # is a sequence number.

SHOW BINLOG EVENTS IN 'PHANG-bin.000002' FROM 100;

Views events starting at a given byte position in a specific binary log file.

PURGE BINARY LOGS BEFORE DATE(NOW()) - INTERVAL 7 DAY;
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';

Three ways to delete old binary logs. The binlog_expire_logs_auto_purge variable enables automatic purging (on by default); binlog_expire_logs_seconds controls the retention interval.

Binary logging is enabled by default; use --skip-log-bin at server startup to disable it. The mysqlbinlog utility views the events from a binary log (also usable on relay log files, which share the binary log format).


# View the events from a binary log
mysqlbinlog binlog.000001 | more

# Capture the events in a text file
mysqlbinlog mysql-bin.000001 > mysql-bin.000001.txt

# Execute the events from one or more binary logs directly against a server
mysqlbinlog binlog.000001 binlog.000002 | mysql -uroot -pPASSWORD

# Execute only the events within a given time range
mysqlbinlog \
    --start-datetime="2014-04-01 10:00:00" \
    --stop-datetime="2014-05-01 19:00:00" binlog.000001 |
    mysql -uroot -pPASSWORD

# Execute only the events within a given transaction-position range
mysqlbinlog \
    --start-position=123456 \
    --stop-position=123499 binlog.000001 |
    mysql -uroot -pPASSWORD

Relay Log

A relay log is a binary log file, located in the replica's data directory, containing the SQL statements executed on a source server; it is used by a replica to apply changes made on the source. Its default name is relay-bin.000001, numbered sequentially. When a replica starts, it reads and applies the relay log until reaching its end; as the source continues to make changes, the replica creates new relay log files.

SHOW SLAVE STATUS;
SHOW RELAYLOG EVENTS;
SHOW RELAYLOG EVENTS IN 'relay-bin.000001';

Inspecting replica status and relay log events (in addition to using mysqlbinlog on the relay log files directly).

Redo Log

The redo log is a binary file recording changes to the database that have not yet been committed to disk; it is used for crash recovery and rollback. Its files are named ib_logfile0 and ib_logfile1, with the #innodb_redo/ directory holding InnoDB's redo logs. The redo and undo logs are internal InnoDB components not meant to be inspected or interpreted manually.

Backup utilities that copy redo log records can sometimes fail to keep pace with redo log generation during a backup, losing records that get overwritten. Redo log archiving addresses this by sequentially writing redo log records to an archive file that backup utilities can copy from as needed.

SET GLOBAL innodb_redo_log_archive_dirs='label1:directory_path1[;label2:directory_path2;...]';

Enables redo log archiving by naming one or more labeled archive directories.

Undo Log

Undo logs are binary files used by InnoDB to store information about changes made to the database so that they can be rolled back if necessary. They are stored in the system tablespace by default, but can also be stored in separate undo tablespaces (default filenames undo_001 and undo_002) used for specific tables or groups of tables.

Undo logs are divided into segments containing a fixed number of records. When a transaction modifies data, the changes are recorded in that transaction's undo log segment. On commit, changes are written to the database and the undo log records are deleted; on rollback, the changes are undone by reading those records. Undo logs also let InnoDB reuse space freed by deleted data, improving performance.

Undo logs are stored circularly and purged once no longer needed, so they cannot be read for transactions that occurred long ago, nor used to view the database's current state – they are useful only for recovering from a failed or interrupted transaction.

LOGFILE GROUP

Each log file group consists of a redo log file and an undo log file. Separating these lets MySQL write to them in parallel, improving performance on multi-CPU systems.

CREATE LOGFILE GROUP logfile_group
    ADD UNDOFILE 'undo_file'
    [INITIAL_SIZE [=] initial_size]
    [UNDO_BUFFER_SIZE [=] undo_buffer_size]
    [REDO_BUFFER_SIZE [=] redo_buffer_size]
    [NODEGROUP [=] nodegroup_id]
    [WAIT]
    [COMMENT [=] 'string']
    ENGINE [=] engine_name

ALTER LOGFILE GROUP logfile_group
    ADD UNDOFILE 'file_name'
    [INITIAL_SIZE [=] size]
    [WAIT]
    ENGINE [=] engine_name

DROP LOGFILE GROUP logfile_group
    ENGINE [=] engine_name

Creating, altering, and dropping a log file group. Logfile groups are specific to the NDB storage engine; other engines handle transaction logs through their own mechanisms.

ISAM Log

The MyISAM log records changes made to MyISAM tables, used to recover from a crash or power failure by replaying changes made since the last checkpoint. It is located in the data directory, defaults to the name myisam.log, and can be rotated via the log-rotate option in my.cnf. Like other transaction logs, it is divided into segments; on commit, changes are written to the table and the segment's records deleted, while on rollback the changes are undone by reading them.

myisamlog
myisamlog > myisamlog.txt

Viewing the MyISAM log with the myisamlog utility.

Audit Log

MySQL Enterprise Audit tracks and monitors user activity by recording information about all database operations, including queries, table changes, and logins/logouts, useful for tracking down security breaches, identifying performance bottlenecks, and complying with regulatory requirements such as GDPR. Records are stored in the audit log; the level of detail and set of logged events are configurable through the audit plugin. 

-- General query log, routed to a table
SET GLOBAL general_log = 1;
SET GLOBAL log_output = 'TABLE';
SELECT event_time, CAST(argument AS CHAR) FROM mysql.general_log;
TRUNCATE TABLE mysql.general_log;

-- Slow query log, with a tighter threshold
SET PERSIST slow_query_log = 'ON';
SET GLOBAL long_query_time = 3;
SELECT * FROM mysql.slow_log;

-- Binary log inspection and retention
SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'mysql-bin.000002' FROM 100;
PURGE BINARY LOGS BEFORE DATE(NOW()) - INTERVAL 7 DAY;

-- Redo log archiving (InnoDB)
SET GLOBAL innodb_redo_log_archive_dirs = 'backup:/var/backups/redo';

-- NDB logfile group
CREATE LOGFILE GROUP lg1
    ADD UNDOFILE 'undo.dat'
    INITIAL_SIZE = 10M
    ENGINE = NDB;