Mastering MySQL Logs: Types, Configuration, and Management Guide

1. Log Types

MySQL provides several log files to help you understand what happens inside mysqld:

Error log : records problems during startup, runtime, or shutdown.

General query log : records client connections and executed statements.

Binary log : records all data‑changing statements, used for replication and point‑in‑time recovery.

Slow query log : records queries whose execution time exceeds long_query_time or queries that do not use indexes.

Transaction log : records actions of transactional storage engines such as InnoDB.

By default, all logs are created in the MySQL data directory. Flushing logs forces mysqld to close and reopen log files.

2. Error Log

What it records

Server start‑up and shutdown information.

Runtime error messages.

Event scheduler messages.

Messages generated when a replication slave starts.

Configuration

You can set the error‑log location with --log-error[=file_name]. If omitted, MySQL uses host_name.err in the data directory.

Check current setting: SHOW GLOBAL VARIABLES LIKE '%log_error%'; Check whether warnings are logged:

SHOW GLOBAL VARIABLES LIKE '%log_warnings%';

3. General Query Log

Enable/disable: general_log=ON|OFF Log file: general_log_file=/path/to/file Global switch: log=ON|OFF (enables all logs when ON)

Output type: log_output=TABLE|FILE|NONE To enable, set at least general_log=ON and log_output=FILE. If general_log_file is not specified, the default name is host_name.log.

Current defaults:

SHOW GLOBAL VARIABLES LIKE '%general_log%';

SHOW GLOBAL VARIABLES LIKE '%log_output%';

4. Slow Query Log

When slow_query_log=ON, queries taking longer than long_query_time (default 10 seconds) are recorded in slow_query_log_file. If no file name is given, the log is named host_name-slow.log.

Current settings: SHOW GLOBAL VARIABLES LIKE '%slow_query_log%'; Enable permanently by adding the options to my.cnf. To view the threshold:

SHOW GLOBAL VARIABLES LIKE 'long_query_time';

5. Binary Log

Enable

Use log-bin[=file_name]. Versions 5.6+ require an explicit file name; earlier versions default to $datadir/mysqld-binlog.

Purpose: records all data‑changing statements for replication and point‑in‑time recovery.

Types

Statement‑based : logs the executed SQL statements.

Row‑based : logs the actual row changes (needed for non‑deterministic statements such as INSERT ... CURRENT_TIME()).

Mixed : combines both methods.

Management

Rotation: set max_binlog_size=200M to limit file size; MySQL creates a new file when the limit is reached.

View logs: SHOW BINARY LOGS; Current log file: SHOW MASTER STATUS; Read log contents: SHOW BINLOG EVENTS IN 'mysqld-binlog.000002'; or mysqlbinlog with start/stop positions or datetimes.

Example to extract a range:

mysqlbinlog --start-position 203 --stop-position 389 mysqld-binlog.000002

Restore data by piping the output to a SQL file and importing it.

Delete old binary logs safely with:

PURGE BINARY LOGS TO 'mysqld-binlog.000006';

6. Common Log‑Related Server Variables

expire_logs_days

: days after which binary logs are automatically removed (0 = disabled). general_log, general_log_file: enable/disable and set file name for the general query log. binlog-format: ROW|STATEMENT|MIXED (MIXED recommended). log_bin: indicates whether binary logging is enabled. log_bin_trust_function_creators: controls restrictions on stored function creation when binary logging is on. log_error: path to the error‑log file. log_output: destination for general and slow logs ( TABLE|FILE|NONE). log_query_not_using_indexes: whether queries without index usage are logged to the slow log. log_slave_updates: records updates received from a master on a replica’s binary log. slow_query_log, slow_query_log_file: enable/disable slow query logging and set its file. log_warnings: controls warning logging level. long_query_time: threshold (seconds) for slow‑query classification. max_binlog_cache_size, max_binlog_size: size limits for binary‑log caches and files. relay_log, relay_log_index, relay_log_purge, relay_log_space_limit: settings for replication relay logs. innodb_log_buffer_size, innodb_log_file_size, innodb_log_files_in_group, innodb_log_group_home_dir: InnoDB redo‑log parameters. innodb_support_xa: enables/disables XA two‑phase commit support. sql_log_bin, sql_log_off: session‑level controls for binary and general logging. sync_binlog: how often the binary log is synchronized to disk (1 is safest).