A Comprehensive Guide to Configuring and Using MySQL Global Query Log

Overview of MySQL Query Logging

Monitoring and analyzing executed SQL queries is a critical task in database management and performance optimization. MySQL offers robust query logging capabilities that record all queries executed across servers, providing essential support for troubleshooting, performance analysis, and security auditing.

Runtime Configuration for Enabling Query Logs

For users running MySQL 5.1.12 and above, query logging can be dynamically controlled at runtime using SET GLOBAL commands, offering significant advantages over traditional configuration file modifications.

Output to Database Table

Outputting query logs to a database table provides the most convenient monitoring approach. Begin by setting the log output destination:

SET GLOBAL log_output = 'TABLE';

Next, enable the general query log:

SET GLOBAL general_log = 'ON';

Once configured, all executed queries will be recorded in the mysql.general_log system table. Log contents can be viewed using standard SELECT statements:

SELECT * FROM mysql.general_log;

Output to File System

For file-based query logging, employ the file output mode. First, set the log output method:

SET GLOBAL log_output = "FILE";

Specify the log file path, ensuring the MySQL process has write permissions:

SET GLOBAL general_log_file = "/path/to/your/logfile.log";

Finally, enable query logging:

SET GLOBAL general_log = 'ON';

After log file generation, contents can be examined using system commands, such as viewing the last 10 lines with tail:

tail /path/to/your/logfile.log

Advantages of Runtime Configuration

Using SET GLOBAL commands for runtime configuration offers multiple benefits compared to modifying my.cnf configuration files:

Avoids potential permanent logging activation that can result from direct configuration file editing, providing more flexible control mechanisms. Eliminates the need to search for query log file locations in the filesystem, as table-based access allows direct database inspection. Most importantly, this approach requires no MySQL server restart, ensuring uninterrupted database connections and business continuity. Server restarts automatically revert logging settings to default states, preventing unintended long-term logging.

Specific Session Query Tracking

Beyond viewing all query records, granular monitoring can be performed for specific user sessions. For example, to find the last query executed by user 'root', use the following SQL statement:

SELECT event_time as time,
       user_host,
       thread_id,
       server_id,
       command_type,
       argument
FROM mysql.general_log
WHERE user_host LIKE 'root%'
ORDER BY event_time DESC
LIMIT 1;

This query returns complete records including timestamp, user host information, thread ID, server ID, command type, and the specific query statement.

Performance and Storage Considerations

Enabling query logging requires careful consideration of system performance impact. In high-load production environments, continuous query logging may consume significant I/O resources and storage space. Temporary activation for specific problem diagnosis is recommended, with prompt deactivation after issue resolution. For table output methods, regular cleanup of historical records in the mysql.general_log table is necessary to prevent performance degradation from table bloat.

Best Practice Recommendations

In practical applications, selecting the appropriate log output method based on specific requirements is advised. Table output proves more convenient for short-term problem diagnosis, while file output better suits long-term auditing needs. Additionally, establishing comprehensive log management strategies—including log rotation, archiving, and cleanup mechanisms—ensures system stability and optimal performance.