Master MySQL Log Management: Error, General, Binlog & Slow Query Tips

MySQL Log Management

MySQL provides several logs to help administrators monitor and troubleshoot the database server.

Error Log

Enabled by default. The default file location depends on the installation method:

Source or binary installation: datadir/$hostname.err YUM installation: /var/log/mysql.log You can change the path by editing /etc/my.cnf and setting log_error=/tmp/err.log. Verify with

SHOW VARIABLES LIKE 'log_error';

General Log

Disabled by default. The default file is datadir/$hostname.err. It can be enabled and its path changed:

# Enable general log and set file path
[mysqld]
general_log=1
general_log_file=/tmp/zls.log

Binary Log (binlog)

Disabled by default. Enable it by setting log-bin in /etc/my.cnf and optionally server_id (required for MySQL 5.7+). The default file is datadir/xxx.000001.

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

# Enable binlog with custom path (MySQL 5.6)
log-bin=/application/mysql/data/mysql-bin

# Enable binlog (MySQL 5.7)
server_id=1
log-bin=mysql-bin

Binlog Working Modes

Three formats are available:

STATEMENT : records only the executed SQL statements (default in MySQL 5.6). Small storage footprint but less precise.

ROW : records each row change (default in MySQL 5.7). Precise but larger storage.

MIXED : combines both; MySQL decides per statement.

Change the mode by setting binlog_format=row (or statement, mixed) in /etc/my.cnf.

Viewing Binlog

# Show binlog format
SHOW VARIABLES LIKE 'binlog_format';

# List binary logs
SHOW BINARY LOGS;

# Show events in a specific binlog
SHOW BINLOG EVENTS IN 'mysql_bin.000001';

Binlog Events

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

Binary Log Recovery

To recover data from a binlog, locate the start and stop positions, extract the relevant portion, and replay it:

# Extract a range
mysqlbinlog --start-position=219 --stop-position=1018 mysql_bin.000001 > /tmp/binlog.sql

# Apply the extracted SQL
mysql < /tmp/binlog.sql

Problems and Solutions

If a database created years ago is accidentally deleted, combine a full backup with incremental binlog data. When users intermix operations across databases, filter the binlog by database name using -d db_name.

Flushing Binlog

Restarting MySQL automatically flushes the current binlog.

When a binlog reaches 1 GB, MySQL creates a new one.

Manually flush with FLUSH LOGS; or mysqladmin flush‑log.

mysqldump can also trigger a flush.

Deleting Binlog

Retain as many binlogs as storage permits. Delete by age:

SET GLOBAL expire_logs_days = 7;  # temporary
# Permanent setting in my.cnf
expire_logs_days = 7

Or purge by time or name:

PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;
PURGE BINARY LOGS TO 'mysql_bin.000002';
RESET MASTER;  # delete all binlogs

Slow Query Log

Disabled by default. Enable and configure in /etc/my.cnf:

# Enable slow query log
slow_query_log = 1
# Set log file location
slow_query_log_file = /application/mysql/data/slow.log
# Set threshold (seconds)
long_query_time = 0.05
# Log queries that do not use indexes
log_queries_not_using_indexes

Analyzing Slow Queries

Use mysqldumpslow to sort and display the most expensive queries:

# Sort by execution time and show top 10
mysqldumpslow -s t -t 10 /path/to/slow.log

Percona Toolkit provides a richer visualizer (pt‑query‑digest) and the open‑source Anemometer UI.

Images