MySql - Managing MySQL logs

Managing MySQL Logs

Efficient log management in MySQL is essential for monitoring, troubleshooting, performance tuning, auditing, and replication. MySQL produces various logs that provide insights into the server's operation. Understanding these logs and how to configure, monitor, and rotate them is crucial for database administrators and developers alike. This document provides a comprehensive guide to managing MySQL logs effectively using configuration files, SQL commands, and best practices.

Types of Logs in MySQL

MySQL supports several types of logs, each serving different purposes:

• Error Log – Logs startup, shutdown, and critical errors.
• General Query Log – Logs all client connections and executed queries.
• Slow Query Log – Logs queries that exceed a defined execution time.
• Binary Log – Logs all changes to database data; used for replication and recovery.
• Relay Log – Used on replication slaves to store changes from the master.
• Audit Log – Used for security auditing (requires plugin).

1. Error Log

Purpose

The error log captures important events like:

• Startup and shutdown messages
• Critical errors
• Configuration problems
• InnoDB storage engine messages

Location

By default, the error log file is named `hostname.err` and resides in the MySQL data directory or a location specified in the configuration file.

Configuration

[mysqld]
log_error = /var/log/mysql/error.log

Viewing the Error Log

cat /var/log/mysql/error.log

Best Practices

• Enable error logging at all times.
• Set appropriate file permissions to secure the error log.
• Regularly monitor for disk space usage.

2. General Query Log

Purpose

The general query log records every connection and statement sent to the MySQL server. It is mainly used for debugging, not recommended in production due to performance impact.

Enable General Log

To enable dynamically:

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

To configure permanently in my.cnf:

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/general.log

Disable General Log

SET GLOBAL general_log = 'OFF';

Viewing General Log

tail -f /var/log/mysql/general.log

Best Practices

• Enable only temporarily for troubleshooting.
• Rotate logs frequently if enabled.

3. Slow Query Log

Purpose

The slow query log records SQL statements that take longer than a specified threshold (default is 10 seconds).

Enable Slow Query Log

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 2;

Permanent Configuration

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

Analyzing the Slow Query Log

mysqldumpslow /var/log/mysql/slow.log

Or use pt-query-digest from Percona Toolkit for detailed analysis.

pt-query-digest /var/log/mysql/slow.log

Best Practices

• Enable in production to monitor performance.
• Adjust long_query_time to suit workload (e.g., 1 or 2 seconds).
• Review slow queries regularly for optimization.

4. Binary Log

Purpose

The binary log stores events that modify database data (e.g., INSERT, UPDATE, DELETE) and is used for replication and point-in-time recovery.

Enable Binary Logging

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7

Common Binary Log Options

• binlog_format: ROW (preferred), STATEMENT, or MIXED
• expire_logs_days: Auto-delete logs after specified days
• max_binlog_size: Limit individual binary log file size

View Binary Logs

SHOW BINARY LOGS;

Read Binary Log Content

mysqlbinlog /var/log/mysql/mysql-bin.000001

Disable Binary Logging (if not using replication)

[mysqld]
skip-log-bin

Best Practices

• Enable if using replication or requiring point-in-time recovery.
• Use expire_logs_days to manage disk space.
• Use ROW-based logging for consistency.

5. Relay Log (Replication Slave)

Purpose

Relay logs are used on replication slaves to store events received from the master before applying them.

Configuration

[mysqld]
relay-log = /var/log/mysql/relay-bin
relay-log-index = /var/log/mysql/relay-bin.index

Viewing Relay Logs

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

Managing Relay Logs

MySQL automatically purges relay logs after execution, but this can be controlled with:

relay_log_purge = 1

6. Audit Log (Optional Plugin)

The audit log plugin allows logging of all database activity including SELECT, DDL, and login events.

Enable Audit Plugin (MySQL Enterprise or MariaDB)

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

Configuration Example

[mysqld]
plugin-load-add=audit_log.so
audit_log_file=/var/log/mysql/audit.log
audit_log_policy=ALL

Best Practices

• Use audit logging for compliance or security tracking.
• Filter unnecessary events to reduce performance impact.

Log File Rotation

Why Rotate Logs?

MySQL logs can grow very large, affecting performance and disk usage. Regular rotation ensures logs are manageable and prevents service disruption.

Using logrotate on Linux

/var/log/mysql/*.log {
    daily
    rotate 7
    missingok
    compress
    delaycompress
    notifempty
    create 640 mysql adm
    sharedscripts
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}

Manual Log Rotation

FLUSH LOGS;

This command creates new log files and closes the old ones. Useful for binary and general/slow logs.

Checking Log Settings in SQL

SHOW VARIABLES LIKE '%log%';

This shows all configured log options including file paths and enabled flags.

Example Output

+-----------------------------+----------------------------+
| Variable_name               | Value                      |
+-----------------------------+----------------------------+
| general_log                 | OFF                        |
| general_log_file            | /var/log/mysql/general.log |
| slow_query_log              | ON                         |
| slow_query_log_file         | /var/log/mysql/slow.log    |
| log_error                   | /var/log/mysql/error.log   |
+-----------------------------+----------------------------+

Performance Considerations

• Disabling general log in production improves performance.
• Slow query log has minimal overhead, especially with long_query_time > 1s.
• Binary log adds overhead due to data change tracking—ensure hardware can handle it.

Security Considerations

• Restrict access to log files using file permissions.
• Sanitize logs before sharing externally as they may contain sensitive data.
• Use secure_file_priv to limit log export/import locations.

Using Log Tables (MySQL 5.1+)

MySQL can store general and slow logs in tables instead of files for easier querying:

[mysqld]
log_output = TABLE

Enable log to table:

SET GLOBAL general_log = 'ON';

Query the log table:

SELECT * FROM mysql.general_log ORDER BY event_time DESC LIMIT 10;

MySQL logging is a powerful feature set that provides vital information for troubleshooting, performance analysis, security auditing, and replication. By understanding and managing each log type—error log, general log, slow query log, binary log, relay log, and audit log—you can maintain a healthier and more secure MySQL environment. It’s crucial to monitor logs regularly, rotate them appropriately, and configure them based on workload and operational needs. Log management is a key part of MySQL administration and should not be overlooked in any production setup.