The MySQL General Log is one of the core logging features that records every client connection and SQL statement executed, including queries, updates, connections, and disconnections. Unlike other logs (error log, slow query log), the general log provides a complete view of database activity, making it valuable for debugging application issues, auditing database operations, and analyzing user behavior.
The general log captures:
Client connection and disconnection timestamps
All executed SQL statements (SELECT, INSERT, UPDATE, DELETE, etc.)
Query execution start times
Important: Since the general log records all operations, enabling it in high-traffic production environments can significantly impact performance and consume substantial disk space. Therefore, it's recommended to enable it temporarily only for debugging or auditing purposes.
Before enabling, verify the current status:
SHOW VARIABLES LIKE 'general_log%';
This displays two key parameters:
general_log: Shows if logging is enabled (ON/OFF)
general_log_file: Shows the log file path
Also check the log output format:
SHOW VARIABLES LIKE 'log_output';
The log_output parameter can be set to TABLE, FILE, or NONE, determining whether logs are stored in files or database tables.
Use these SQL commands to temporarily enable the general log (settings lost after server restart):
SET GLOBAL general_log = 'ON';
To change output format to table for easier querying:
SET GLOBAL log_output = 'TABLE';
The log_output parameter can be set to TABLE, FILE, or NONE, determining whether logs are stored in files or database tables.
Use these SQL commands to temporarily enable the general log (settings lost after server restart):
SET GLOBAL general_log = 'ON';
To change output format to table for easier querying:
SET GLOBAL log_output = 'TABLE';
When using TABLE format, logs are stored in mysql.general_log table and can be queried:
SELECT * FROM mysql.general_log;
Note: Using table format increases system table load - file format is generally recommended:
SET GLOBAL log_output = 'FILE';
3. Enable via Configuration File (Permanent)
For permanent configuration, edit MySQL configuration file (typically my.cnf or mysql-server.cnf - location varies by OS and installation method).
Add or modify these parameters in the [mysqld] section:
[mysqld]
general_log = ON
general_log_file = /path/to/your/general.log
log_output = FILE
Restart MySQL service to apply changes:
systemctl restart mysql
Warning: Restarting MySQL in production requires careful planning - perform during low-traffic periods.
MySQL 8.0 introduced the SET PERSIST command, which persists configuration changes without requiring service restart:
SET PERSIST general_log = 'ON';
SET PERSIST log_output = 'FILE';
The SET PERSIST command saves configuration to mysqld-auto.cnf in the data directory, ensuring settings survive server restarts. This is the recommended configuration method in MySQL 8.
Unlike binary logs, MySQL doesn't have built-in parameters for general log expiration. Alternative methods are required to manage retention periods.
On Linux systems, use logrotate to manage general log files. Create or edit /etc/logrotate.d/mysql-general:
/path/to/your/general.log {
daily
rotate 7
compress
delaycompress
missingok
notifempty
create 644 mysql mysql
postrotate
# Signal MySQL to reopen log file
mysql -e "FLUSH GENERAL LOGS;"
endscript
}
This configuration:
Rotates logs daily
Retains last 7 days of logs
Compresses old logs to save space
Recreates log files after rotation
Create custom scripts for periodic log cleanup. Here's a sample shell script:
#!/bin/bash
# Set log file path and retention period
LOG_FILE="/path/to/your/general.log"
RETENTION_DAYS=7
# Rename current log file (triggers new file creation)
mv $LOG_FILE $LOG_FILE.$(date +%Y%m%d)
# Notify MySQL to reopen log file
mysql -e "FLUSH GENERAL LOGS;"
# Delete logs older than retention period
find $(dirname $LOG_FILE) -name "$(basename $LOG_FILE).*" -mtime +$RETENTION_DAYS -delete
Add this script to cron for regular execution (e.g., daily).
If using table format, periodically truncate the mysql.general_log table:
Note: Frequent truncation may affect ongoing logging - perform during low-activity periods.
Performance Impact: General log records all queries, significantly affecting IO and performance. Enable temporarily only when needed in production and disable after debugging.
Security Concerns: General logs may contain sensitive information (passwords, personal data). Ensure proper file permissions to prevent unauthorized access.
Storage Monitoring: General logs can grow rapidly, especially in high-traffic environments. Ensure adequate disk space and implement effective log rotation strategies.
Selective Logging: Consider logging only specific user activities through application-level controls.
Disabling General Log:
SET GLOBAL general_log = 'OFF';
Or remove related settings from configuration file and restart service.