MySQL Logging

Logging is a critical aspect of MySQL administration that allows you to monitor database activities, troubleshoot issues, investigate security breaches, and maintain an audit trail of operations. In this guide, we'll explore the various types of MySQL logs, how to configure them, and how to use them effectively for database management.

Why is MySQL Logging Important?

Proper logging practices help you:

• Identify and solve performance issues
• Track unauthorized access attempts
• Debug application errors
• Comply with audit requirements
• Recover from disasters using binary logs
• Monitor database operations

Types of MySQL Logs

MySQL provides several types of logs, each serving different purposes:

1. Error Log - Records problems encountered during server startup, operation, and shutdown
2. General Query Log - Records all client connections and SQL statements received
3. Binary Log - Records data-modifying statements for replication and recovery
4. Slow Query Log - Records queries that exceed a specified execution time
5. Relay Log - Used in replication by replica servers
6. DDL Log - Records metadata operations

Let's explore each of these in detail.

Error Log

The error log contains information about server startup and shutdown processes, critical errors, and warnings. It's crucial for troubleshooting MySQL server issues.

Configuration

The error log location is controlled by the log_error system variable:

-- Check current error log path
SHOW VARIABLES LIKE 'log_error';

Output:

+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | /var/log/mysql/error.log |
+---------------+---------------------+

You can configure the error log in the my.cnf configuration file:

[mysqld]
log_error = /var/log/mysql/mysql-error.log
log_error_verbosity = 3

The log_error_verbosity determines what gets logged:

• 1 = Errors only
• 2 = Errors and warnings
• 3 = Errors, warnings, and notes (most verbose)

Viewing the Error Log

You can directly view the error log using standard file viewing tools:

sudo tail -f /var/log/mysql/error.log

Example error log entries:

2023-06-15T10:23:45.123456Z 0 [Note] MySQL Server started
2023-06-15T11:34:56.789012Z 0 [Warning] IP address '192.168.1.100' could not be resolved
2023-06-15T12:45:12.345678Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

General Query Log

The general query log records all SQL statements received from clients. It's helpful for debugging application issues but can generate large files and impact performance.

Configuration

Enable the general query log:

-- Check if general log is enabled
SHOW VARIABLES LIKE 'general_log%';

-- Enable general log (dynamic)
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/mysql-query.log';

Permanent configuration in my.cnf:

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

Sample General Log Output

2023-06-15T14:25:10.123456Z    42 Connect   webapp_user@localhost on webapp_db using TCP/IP
2023-06-15T14:25:10.234567Z    42 Query     SELECT * FROM users WHERE active = 1
2023-06-15T14:25:11.345678Z    42 Query     UPDATE users SET last_login = NOW() WHERE id = 123
2023-06-15T14:25:12.456789Z    42 Quit

Binary Log

The binary log contains "events" that describe database changes (INSERT, UPDATE, DELETE, etc.) and is stored in a binary format. It's essential for:

• Point-in-time recovery
• Replication
• Auditing data changes

Configuration

Enable binary logging:

-- Check binary log status
SHOW VARIABLES LIKE 'log_bin%';

-- Check binary log format
SHOW VARIABLES LIKE 'binlog_format';

Configure in my.cnf:

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
max_binlog_size = 100M
expire_logs_days = 14

Binary log formats:

• STATEMENT: Logs SQL statements (smallest size, potential replication issues)
• ROW: Logs changes to individual rows (most reliable, larger size)
• MIXED: Uses statement-based by default, switches to row-based when needed

Working with Binary Logs

List available binary logs:

SHOW BINARY LOGS;

Output:

+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 156       | No        |
| mysql-bin.000002 | 12345678  | No        |
+------------------+-----------+-----------+

View binary log events:

SHOW BINLOG EVENTS IN 'mysql-bin.000002';

The mysqlbinlog utility helps you read binary log files:

mysqlbinlog /var/log/mysql/mysql-bin.000002 | less

To back up specific binary logs:

mysqlbinlog --start-datetime="2023-06-15 10:00:00" \
  --stop-datetime="2023-06-15 11:00:00" \
  /var/log/mysql/mysql-bin.000002 > backup.sql

Slow Query Log

The slow query log records SQL statements that take longer than a specified time to execute. It's invaluable for identifying performance bottlenecks in your database.

Configuration

Enable the slow query log:

-- Check slow query log status
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- Enable slow query log (dynamic)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 1;  -- Log queries taking more than 1 second

Configuration in my.cnf:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 1000

Analyzing the Slow Query Log

View the slow query log:

sudo tail -f /var/log/mysql/mysql-slow.log

Example slow query log entry:

# Time: 2023-06-15T15:45:23.123456Z
# User@Host: webapp_user[webapp_user] @ localhost []
# Query_time: 2.456789  Lock_time: 0.000012  Rows_sent: 1000  Rows_examined: 1000000
SET timestamp=1686842723;
SELECT * FROM large_table WHERE non_indexed_column = 'value';

To analyze slow queries efficiently, use the mysqldumpslow tool:

mysqldumpslow /var/log/mysql/mysql-slow.log

Output:

Count: 28  Time=2.46s (68s)  Lock=0.00s (0s)  Rows=1000.0 (28000), webapp_user@localhost
  SELECT * FROM large_table WHERE non_indexed_column = 'S'

Log Rotation

Log files can grow large over time. MySQL provides the ability to rotate logs:

Binary Log Rotation

-- Rotate binary logs
FLUSH BINARY LOGS;

-- Purge old binary logs
PURGE BINARY LOGS BEFORE '2023-06-10 00:00:00';
-- or
PURGE BINARY LOGS TO 'mysql-bin.000010';

Other Log Rotation

For error, general, and slow query logs, you can use:

FLUSH LOGS;

You can also configure automatic log rotation with logrotate on Linux systems:

/var/log/mysql/*.log {
    daily
    rotate 7
    missingok
    create 640 mysql adm
    compress
    sharedscripts
    postrotate
        test -x /usr/bin/mysqladmin && /usr/bin/mysqladmin ping > /dev/null 2>&1 && /usr/bin/mysqladmin flush-logs
    endscript
}

Best Practices for MySQL Logging

1. Enable appropriate logs - Don't enable all logs in production as this can impact performance
2. Configure log rotation - Prevent logs from consuming all disk space
3. Monitor log sizes - Set up alerts for unexpected log growth
4. Review error logs regularly - Catch issues before they become critical
5. Use slow query logs during development - Find and fix performance issues early
6. Adjust slow query time threshold - Start with a higher value and decrease as needed
7. Enable binary logs for all production servers - Essential for recovery and replication

Real-world Example: Troubleshooting a Performance Issue

Let's say users are reporting slow performance on your application. Here's how you might use MySQL logs to investigate:

1. Enable the slow query log with a low threshold:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;  -- Log queries taking more than 0.5 seconds

2. Wait for the application to generate some traffic, then analyze the slow query log:

mysqldumpslow -t 10 /var/log/mysql/mysql-slow.log

3. Identify problematic queries, for example:

Count: 245  Time=1.35s (330s)  Lock=0.02s (4s)  Rows=10045.0 (2461025), webapp_user@localhost
  SELECT p.*, c.* FROM products p JOIN categories c ON p.category_id = c.id WHERE p.status = 'S' ORDER BY p.created_at DESC

4. Optimize the query by adding appropriate indexes:

ALTER TABLE products ADD INDEX idx_status_created (status, created_at);

5. Verify the improvement by checking the slow query log again.

Security Considerations

Logs can contain sensitive information. Ensure that:

1. Log files have appropriate permissions (typically 640 or 660)
2. Access to log directories is restricted
3. Log data is included in your backup strategy
4. Consider data privacy regulations (GDPR, CCPA) when storing logs
5. Implement log retention policies

Summary

MySQL logging provides essential tools for monitoring, troubleshooting, and maintaining your database environment:

• Error Log: Critical for identifying server issues
• General Query Log: Useful during development for debugging
• Binary Log: Essential for replication and point-in-time recovery
• Slow Query Log: Invaluable for performance optimization

Proper log configuration and regular log analysis are fundamental aspects of good database administration. By understanding and implementing appropriate logging strategies, you'll be better equipped to maintain a healthy, performant, and secure MySQL environment.

Additional Resources

• MySQL Documentation on Server Logs
• Performance Schema as an Alternative to Traditional Logging
• MySQL Enterprise Monitor for more advanced monitoring solutions

Practice Exercises

1. Enable the slow query log on your development server and identify the three slowest queries.
2. Create a script that parses the MySQL error log and sends an email alert when critical errors occur.
3. Set up binary logging and practice point-in-time recovery by restoring a database to a specific point.
4. Configure log rotation for all MySQL logs and verify it's working correctly.
5. Use mysqlbinlog to extract data-modifying statements from the binary log for a specific table.