MySQL Query Log Configuration and Monitoring: From Basics to Practice

Fundamental Concepts of MySQL Query Logs

MySQL query logs are essential components of the database management system, recording all SQL statements received by the server from clients. According to the reference article, the general query log provides a comprehensive record of mysqld activities, including client connections, disconnections, and each received SQL statement. This logging mechanism proves invaluable when diagnosing client errors and understanding the specific content sent from clients to mysqld.

Core Methods for Configuring Query Logs

There are multiple approaches to enable query logging in MySQL, with the most direct method being server startup using the --log option. The specific operation is as follows:

mysqld --log=log_file_name

Alternatively, persistent configuration can be achieved by modifying the configuration file. Add the following to the my.cnf file:

log = log_file_name

Both methods record all queries to the specified log file, providing comprehensive data support for database monitoring.

Specialized Configuration for Slow Query Logs

In addition to complete query logs, MySQL offers specialized slow query logging functionality. Using the --log-slow-queries option allows recording only queries with long execution times:

mysqld --log-slow-queries=slow_query_log_file

By default, queries taking more than 10 seconds to execute are considered slow queries. This threshold can be adjusted by setting the long_query_time system variable, for example to 5 seconds:

SET GLOBAL long_query_time = 5;

Log File Management Strategies

Effective log management requires consideration of file size, storage location, and rotation strategies. According to the reference article, server restarts and log flushing do not generate new general query log files, but log file rotation can be achieved through specific commands:

$> mv host_name.log host_name-old.log
$> mysqladmin flush-logs general
$> mv host_name-old.log backup-directory

On Windows systems, the rename command should be used instead of mv. Additionally, file rotation can be implemented at runtime by disabling the log, renaming the file externally, and then re-enabling the log. This method works on all platforms and does not require server restart.

Configuration Differences Across MySQL Versions

It is important to note that different MySQL versions exhibit variations in log configuration. For MySQL 5.6 and later versions, some traditional configuration methods may no longer be applicable. In such cases, the general query log can be enabled by setting system variables:

SET global general_log = 1;
SET global log_output = 'FILE';
SET global general_log_file='/path/to/general.log';

This approach offers greater flexibility, allowing dynamic adjustment of log configuration without server restart.

Security Considerations and Best Practices

When enabling query logs, special attention must be paid to security issues. According to the reference article, the MySQL server rewrites passwords in statements written to the general query log to prevent them from appearing in plain text. This password rewriting mechanism can be suppressed using the --log-raw option, but for security reasons, this is not recommended in production environments.

Statements that cannot be parsed (such as those with syntax errors) are generally not written to the general query log because it cannot be determined whether they contain passwords. If the application scenario requires logging all statements (including erroneous ones), the --log-raw option can be used, but it must be recognized that this bypasses the password rewriting mechanism.

Selection of Log Output Destinations

MySQL supports outputting logs to different destinations, configured through the log_output system variable. Available destinations include:

• FILE: Output to file
• TABLE: Output to database table
• NONE: No output

When using tables as output destinations, log content can be directly accessed through SQL queries:

SELECT * FROM mysql.general_log;

This method provides more flexible log analysis and query capabilities.

Timestamp and Timezone Handling

The log_timestamps system variable controls the timezone of timestamps in messages written to the general query log file. It is important to note that this variable does not affect the timezone of messages written to log tables, but timestamps of rows retrieved from these tables can be converted from the local system timezone to any desired timezone using the CONVERT_TZ() function or by setting the session time_zone system variable.

Performance Impact and Optimization Recommendations

Enabling query logs can impact database performance, particularly in high-concurrency environments. Recommendations include:

• Enable complete query logging only when necessary for diagnostic purposes
• For production environments, prioritize the use of slow query logs
• Regularly clean and archive log files to prevent disk space exhaustion
• Adjust log levels and recorded content based on actual requirements

Through proper configuration and management, MySQL query logs can become powerful tools for database monitoring and fault diagnosis while minimizing impact on system performance.