Master MySQL Log Files: Error, Slow Query, and Binlog Management Guide

Experiment environment: MySQL Community Server (GPL) 5.7.17 on Ubuntu 16.04.

1. MySQL Log File Classification

Common MySQL logs include:

Error log

Slow query log

Binary log (binlog)

General query log

2. Error Log

Official documentation: https://dev.mysql.com/doc/refman/5.7/en/error-log.html

The MySQL error log records serious warnings, errors, and detailed information about each server start and shutdown.

You can find the error‑log file location with: show variables like 'log_error'\G; Images illustrate the command output.

3. Slow Query Log

Official documentation: https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

The slow query log records statements whose execution time exceeds the long_query_time threshold (default 10 seconds).

Check the current threshold and whether the log is enabled:

show variables like 'long_query_time'\G;

show variables like 'slow_query_log'\G;

Enable the slow query log: set global slow_query_log='ON'; Find the default slow‑query log file: show variables like 'slow_query_log_file'\G; By default the file is named host_name-slow.log.

View the log using mysqldumpslow:

sudo mysqldumpslow /var/lib/mysql/xuliugen-slow.log

For more options, run man mysqldumpslow.

Change the storage format from file to table: show variables like 'log_output'\G; Set output to TABLE: set global log_output='TABLE'; The slow queries will then be stored in the mysql.slow_log table: show create table mysql.slow_log\G; Test the slow query log by executing a deliberately slow statement: select sleep(10); Then query the table to see the recorded entry: select * from mysql.slow_log\G; MySQL 5.7 also provides two additional variables:

log_slow_admin_statements

log_queries_not_using_indexes

By default, administrative statements and queries that do not use indexes are not logged; adjusting these settings in development can help, but they are usually disabled in production to avoid excessive log growth.