Master MySQL Log Management: From Error to Slow Query Logs

MySQL Log Overview

MySQL provides several logs: error log, general log, binary log, and slow query log. This article explains default settings, how to enable or disable each log, modify their paths, and use them for troubleshooting and recovery.

Error Log

Default: enabled. Location varies: source/binary install uses datadir/$hostname.err, yum install uses /var/log/mysql.log. The path can be changed via log_error in my.cnf.

# vim /etc/my.cnf
[mysqld]
log_error=/tmp/err.log

Check the variable:

mysql> show variables like 'log_error';

General Log

Default: disabled. Location: datadir/$hostname.err. It can be enabled and its file path set with general_log and general_log_file.

# vim /etc/my.cnf
[mysqld]
general_log=1
general_log_file=/tmp/zls.log

Binary Log (binlog)

Default: disabled in MySQL 5.6, enabled in MySQL 5.7 (requires server_id). Path defaults to datadir/xxx.000001 and can be changed with log-bin. The binlog records all changes to the database.

# Enable binlog (MySQL 5.6)
[mysqld]
log-bin=mysql-bin
# Enable binlog (MySQL 5.7)
server_id=1
log-bin=mysql-bin

Show the setting:

show variables like 'log_bin';

Binary Log Formats

STATEMENT : records only the SQL statements. Small size, easier to understand, but less strict.

ROW : records each row change. Precise, larger size, more storage.

MIXED : combines statement and row modes, letting MySQL decide per statement.

Changing the Format

# vim /etc/my.cnf
[mysqld]
log-bin=/application/mysql/data/mysql-bin
binlog_format=row

Viewing the Binary Log

# List binary logs
show binary logs;
# Show events in a specific binlog
show binlog events in 'mysql_bin.000001';
# Decode rows
mysqlbinlog -vvv --base64-output=decode-row mysql_bin.000001

Binary Log Events

The smallest unit in a binlog is an event .

A transaction is split into multiple events.

Each event has a start and stop position; the first 120 bytes (MySQL 5.6) or 154 bytes (MySQL 5.7) are reserved for file‑format information.

Binary Log Recovery Example

Steps: create a database and table, perform DML, then use mysqlbinlog with start/stop positions to extract the SQL and replay it.

# Find positions
mysqlbinlog -vvv --base64-output=decode-row mysql_bin.000001
# Extract the range
mysqlbinlog --start-position=219 --stop-position=1018 mysql_bin.000001 > /tmp/binlog.sql
# Apply the extracted SQL
mysql < /tmp/binlog.sql

Slow Query Log

Default: disabled. Path defaults to datadir/$hostname-slow.log. It can be enabled and configured via slow_query_log, slow_query_log_file, long_query_time, and log_queries_not_using_indexes.

# vim /etc/my.cnf
[mysqld]
slow_query_log=1
slow_query_log_file=/application/mysql/data/slow.log
long_query_time=0.05
log_queries_not_using_indexes

Analyze the slow log with mysqldumpslow: mysqldumpslow -s t -t 10 db02-slow.log Percona Toolkit can provide deeper analysis:

pt-query-digest /application/mysql/data/db02-slow.log

Visualization can be done with Anemometer (based on pt-query-digest).