Comprehensive Guide to Enabling and Analyzing MySQL General Query Log

Overview of MySQL General Query Log

The MySQL general query log serves as a comprehensive record of mysqld activities, documenting client connections, disconnections, and all SQL statements received from clients. This log is particularly valuable for diagnosing client errors, analyzing query patterns, and monitoring database activities. It's important to note that on busy servers, the general query log file can grow rapidly and consume significant disk space, requiring careful management.

Methods to Enable General Query Log

Enabling via Configuration File

For MySQL versions prior to 5.1.29, enable the query log by adding the following configuration to the [mysqld] section in /etc/my.cnf:

log = /path/to/query.log

For MySQL 5.1.29 and later versions, the log option is deprecated. Use the following configuration instead:

general_log_file = /path/to/query.log
general_log = 1

Enabling via MySQL Console

Alternatively, you can dynamically enable the general query log from the MySQL console:

SET global general_log = 1;

To specify the log file location simultaneously, execute:

SET global general_log_file = '/tmp/mysql.log';
SET global general_log = 1;

Log Output Destination Configuration

MySQL supports multiple log output destinations, configurable through the log_output system variable:

File Output Mode

SET global log_output = 'file';
SET global general_log = 1;

Table Output Mode

Output logs to database tables for direct querying and analysis:

SET global log_output = 'table';
SET global general_log = 1;

Query log contents in table mode:

SELECT * FROM mysql.general_log;

Log File Management

Log File Rotation

To prevent log files from becoming too large, perform regular log rotation:

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

Runtime Log File Renaming

Rename log files without restarting the server:

SET GLOBAL general_log = 'OFF';
-- Rename log file from command line
SET GLOBAL general_log = 'ON';

Log Content Analysis

Basic Log Structure

The general query log records: client connection and disconnection events, received SQL statements, and connection types (TCP/IP, SSL/TLS, Socket, etc.). Each entry includes timestamps, with timezone controlled by the log_timestamps system variable.

Connection Type Identification

The connection_type field in logs identifies the protocol used for client connections:

• TCP/IP: TCP/IP connection established without SSL
• SSL/TLS: TCP/IP connection established with SSL
• Socket: Unix socket file connection
• Named Pipe: Windows named pipe connection
• Shared Memory: Windows shared memory connection

Security Considerations

Password Rewriting Mechanism

For security reasons, MySQL server rewrites passwords in statements written to the general query log to prevent plain text exposure. Password rewriting can be suppressed using the --log-raw option during server startup, but this poses security risks and is not recommended for production environments.

Syntax Error Handling

Statements that cannot be parsed (due to syntax errors) are not written to the general query log by default because they cannot be verified as password-free. Use the --log-raw option to log all statements, including those with errors.

Advanced Configuration Options

Timezone Settings

The log_timestamps system variable controls the timezone of timestamps in messages written to the general query log file. For messages written to log tables, the timezone is not affected by this variable, but timezone conversion can be performed using the CONVERT_TZ() function or by setting the session time_zone system variable.

Session-Level Control

Enable or disable general query logging for the current session:

SET sql_log_off = ON;  -- Disable logging for current session
SET sql_log_off = OFF; -- Enable logging for current session

Performance Considerations and Best Practices

Enabling the general query log can impact server performance, particularly in high-load environments. Recommendations include:

• Enable general query logging only when necessary for troubleshooting
• Regularly monitor log file sizes to prevent disk space exhaustion
• Consider using table output mode for easier querying and analysis
• Use the --log-raw option cautiously in production environments
• Combine with slow query log and error log for comprehensive analysis

Relationship with Other Logs

The general query log differs from the binary log in recording order: general query log records statements in the order they are received, while binary log records statements after execution but before lock release. When using statement-based binary logging for replication, replica servers write received statements to their general query logs. However, with row-based binary logging, updates are sent as row changes and are not written to the general query log.