Enabling MySQL query logging is an essential task for database administrators and developers who want to monitor and analyze the queries running on their MySQL server. Query logs help in identifying slow queries, diagnosing performance issues, auditing database activity and much more. This article explains how to enable MySQL query logging with technical details, examples, and additional insights.

Types of MySQL Query Logs

MySQL supports several types of logs to help with query analysis:

1. General Query Log: Logs all SQL queries received by the server. Useful for debugging, but can result in large log files.
2. Slow Query Log: Logs queries that take longer than a specified duration to execute. Useful for performance tuning.

Steps to Enable MySQL Query Logs

Enabling the General Query Log

The general query log can be enabled by modifying the MySQL configuration file, my.cnf (my.ini on Windows), or dynamically through SQL commands.

Step 1: Configuration via my.cnf

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

• general_log = 1 enables the general log.
• general_log_file specifies the file location for the log.

Step 2: Dynamic Configuration

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/path/to/log/general.log';

To verify the settings:

SHOW VARIABLES LIKE 'general_log%';

Enabling the Slow Query Log

The slow query log is typically preferred for performance tuning because it only logs queries that exceed a specified duration.

Step 1: Configuration via my.cnf

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

• slow_query_log = 1 enables the slow query log.
• slow_query_log_file sets the destination for the log file.
• long_query_time indicates the threshold in seconds for query execution.

Step 2: Dynamic Configuration

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/path/to/log/slow.log';
SET GLOBAL long_query_time = 2;

To confirm the settings:

SHOW VARIABLES LIKE 'slow_query%';

Maintenance and Best Practices

• Rotate Logs: Log rotation helps in managing disk space effectively. Use tools like logrotate on Linux for automatic log rotation.
• Monitor Disk Usage: Constantly monitor disk usage as query logs can grow quickly, especially general logs.
• Use Log Analysis Tools: Utilize log analysis tools such as pt-query-digest to parse and analyze log files for performance optimization.
• Security: Ensure logs are stored in secured directories and accessed by authorized users only.

Sample Query Log Format

A typical general query log entry looks like this:

Time                Id Command    Argument
2023-10-11T09:34:34 123 Connect   user@localhost as anonymous on 
2023-10-11T09:34:34 123 Query     SET NAMES utf8mb4

A slow query log format:

1# Time: 2023-10-11T09:35:44.468569Z
2# User@Host: root[root] @ localhost []  Id: 3
3# Query_time: 11.234  Lock_time: 0.000  Rows_sent: 0  Rows_examined: 0
4SET timestamp=1602182144;
5SELECT SLEEP(11);

Summary Table

The ability to toggle query logging on and off dynamically without restarting the server is a powerful feature that gives DBAs flexibility in monitoring and diagnosing issues. Administrators should always consider the potential performance impact of logging, especially with the general query log, and keep log maintenance and security in mind.