MySQL Log Management Guide: Configuration, Monitoring, and Best Practices

1 1. Introduction
2 2. Main Types of MySQL Logs
3 3. How to Configure Each Log
4 4. Log File Locations and Management
5 5. How to Use Logs Effectively
6 6. Important Considerations and Best Practices
7 7. Conclusion

1. Introduction

The Role and Importance of MySQL Log Management

MySQL is widely used by many companies and developers as a database management system. In its operation, log management plays a critical role. Logs are files that record the operational status of the database, errors, query execution history, and more. These records are essential for system administration, troubleshooting, and performance tuning.

For example, during a server failure, the cause can often be identified from the error log. By analyzing the slow query log, it is possible to implement measures that prevent performance degradation. Properly configuring and managing logs can significantly improve the stability and efficiency of your database.

Purpose of This Article and Target Audience

This article explains the basic concepts and specific configuration methods for MySQL log management. It is intended for the following readers:

Beginners with basic knowledge of MySQL
Intermediate users who want to learn practical log management
Those aiming to improve database performance and troubleshooting skills

By reading this guide, you will deepen your understanding of MySQL log management and be able to apply it in real-world operations and projects.

Next Steps

In the next section, we will explain in detail the different types of logs available in MySQL. This will help you accurately understand the role and purpose of each log.

2. Main Types of MySQL Logs

Error Log

Role and Purpose

The error log records MySQL server startup and shutdown events, system errors, warnings, and other important messages. It is used to verify whether the server is operating normally and to identify the cause when problems occur.

Use Cases

Analyzing errors that occur during server startup
Identifying configuration mistakes or file permission issues
Diagnosing and recovering from crashes

Example of an Error Log

2023-12-22T10:30:45.123456Z 0 [ERROR] [MY-013132] [Server] Plugin 'InnoDB' initialization failed.
2023-12-22T10:30:45.123456Z 0 [ERROR] [MY-010119] [Server] Aborting

General Query Log

Role and Purpose

The general query log records all SQL queries sent to the MySQL server. It is useful for tracking user activity and verifying behavior in test environments.

Use Cases

Monitoring user operations and log management
Analyzing executed SQL statements and verifying behavior
Query analysis during debugging

Example of a General Query Log

2023-12-22T10:35:00.123456Z 10 Query SELECT * FROM users WHERE id=1;
2023-12-22T10:35:01.123456Z 11 Query UPDATE users SET status='active' WHERE id=1;

Slow Query Log

Role and Purpose

The slow query log records queries whose execution time exceeds a configured threshold. It helps identify performance bottlenecks and supports efficient SQL writing and index optimization.

Use Cases

Identifying queries causing performance degradation
Analyzing query execution time and optimizing performance
Monitoring server load caused by heavy queries

Example of a Slow Query Log

# Time: 2023-12-22T10:40:00
# User@Host: root[root] @ localhost []
# Query_time: 12.345  Lock_time: 0.001  Rows_sent: 1000  Rows_examined: 20000
SELECT * FROM orders WHERE status='pending';

Binary Log

Role and Purpose

The binary log records data modification operations (such as INSERT, UPDATE, and DELETE) within the database. It is an essential log used for data recovery and replication (database replication).

Use Cases

Restoring the latest state after a database failure
Managing data synchronization through replication settings
Tracking change history and supporting audits

Example of a Binary Log (Output by mysqlbinlog)

# at 12345
#2023-12-22T10:45:00 server id 1  end_log_pos 234
INSERT INTO orders (id, status) VALUES (1, 'pending');

Summary

Each of these logs serves a different purpose and is an essential component of MySQL administration and monitoring. In the next section, we will explain the specific procedures for configuring these logs, including configuration examples and sample code for practical usage.

3. How to Configure Each Log

Configuring the Error Log

Steps

The error log is enabled by default in MySQL’s initial configuration, but you can customize its location and format.

Configuration Example

Edit the MySQL configuration file (my.cnf or my.ini).

[mysqld]
log_error = /var/log/mysql/error.log

Restart the MySQL service to apply the settings.

sudo systemctl restart mysql

Key Points

Configure the error log output location with consideration for the server’s disk capacity.
Set up log rotation to automatically remove old logs, which makes management easier.

Configuring the General Query Log

Steps

The general query log is disabled by default. To enable it, add the following settings.

Configuration Example

Edit the configuration file.

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

Restart the MySQL service.

sudo systemctl restart mysql

Notes

Because the general query log records all SQL statements, disk usage can increase rapidly. Disable it when not needed, or configure rotation as appropriate.

Configuring the Slow Query Log

Steps

Since the slow query log is useful for performance tuning, it must be configured carefully.

Configuration Example

Edit the configuration file.

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Restart the MySQL service.

sudo systemctl restart mysql

Explanation

slow_query_log: Enables the slow query log (1 = on, 0 = off).
slow_query_log_file: Path where the log file is stored.
long_query_time: Threshold (in seconds) for queries to be logged.

Additional Note

Using the slow query analysis tool pt-query-digest makes log analysis much easier.

Configuring the Binary Log

Steps

The binary log is disabled by default, but you should enable it when using replication or needing it for backups.

Configuration Example

Edit the configuration file.

[mysqld]
log_bin = /var/log/mysql/mysql-bin
server_id = 1
binlog_format = ROW
expire_logs_days = 7

Restart the MySQL service.

sudo systemctl restart mysql

Parameter Details

log_bin: Specifies where to store binary logs.
server_id: Server identifier required for replication configuration.
binlog_format: Binary log format. ROW records row-level changes.
expire_logs_days: Retention period (in days). Older logs are deleted automatically.

Summary

These settings are extremely important for operating MySQL. Configure the error log, general query log, slow query log, and binary log appropriately for your use case, and leverage them for monitoring and optimization during operations.

4. Log File Locations and Management

Checking and Configuring Log File Locations

Default Locations

MySQL log file locations vary depending on the environment and installation method, but they are generally stored in the following locations:

Error log: /var/log/mysql/error.log
General query log: /var/log/mysql/general.log
Slow query log: /var/log/mysql/slow.log
Binary log: /var/lib/mysql/mysql-bin.*

Customizing the Location

To change the storage location, edit the configuration file (my.cnf or my.ini).

Example: Changing the Error Log Location

[mysqld]
log_error = /custom/log/mysql_error.log

After updating the configuration, restart MySQL to apply the changes.

sudo systemctl restart mysql

Key Points

Set appropriate permissions on the destination directory so the MySQL user can write to it.
Regularly monitor the disk capacity where log files are stored.

Configuring Log Rotation

Rotation Example (Using logrotate)

Create or edit the configuration file.

sudo nano /etc/logrotate.d/mysql

Add the following content.

/var/log/mysql/*.log {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql mysql
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}

Apply the Configuration

sudo logrotate -f /etc/logrotate.d/mysql

Best Practices for Disk Space Management

Disk Usage Check Commands

du -sh /var/log/mysql

Check available disk space:

df -h

Example of an Automatic Cleanup Script

Automatically deletes log files older than 30 days.

#!/bin/bash
find /var/log/mysql/*.log -mtime +30 -exec rm {} \;

Log File Encryption and Security Management

Permission Setting Example

chmod 640 /var/log/mysql/*.log
chown mysql:mysql /var/log/mysql/*.log

Encryption Example

You can encrypt logs using OpenSSL.

openssl enc -aes-256-cbc -salt -in /var/log/mysql/general.log -out /var/log/mysql/general.log.enc

Summary

MySQL log files can be managed safely and efficiently by properly configuring storage locations, rotation settings, and disk capacity management. In particular, encryption and permission management also strengthen security.

5. How to Use Logs Effectively

Troubleshooting

Using the Error Log

The error log helps identify issues related to server startup and operation.

Situation: The MySQL server does not start.
Check the error log:

sudo tail -n 20 /var/log/mysql/error.log

Log example:

2023-12-22T10:30:45.123456Z 0 [ERROR] [MY-013132] [Server] Plugin 'InnoDB' initialization failed.
2023-12-22T10:30:45.123456Z 0 [ERROR] [MY-010119] [Server] Aborting

Solution:

Check the InnoDB configuration and, if corruption is detected, repair it using recovery mode.

[mysqld]
innodb_force_recovery = 1

After updating the configuration, restart MySQL and attempt recovery.

Using the General Query Log

Situation: A specific user may be performing suspicious operations.
Check the query log:

sudo tail -n 20 /var/log/mysql/general.log

Log example:

2023-12-22T10:35:00.123456Z 10 Query SELECT * FROM users WHERE id=1;
2023-12-22T10:35:01.123456Z 11 Query DELETE FROM users WHERE id=1;

Solution:

Identify suspicious users based on operation history and restrict access.
Review and update permission settings if necessary.

Performance Tuning

Using the Slow Query Log

Situation: A specific page loads slowly.
Check the slow query log:

sudo tail -n 20 /var/log/mysql/slow.log

Log example:

# Time: 2023-12-22T10:40:00
# Query_time: 12.345  Lock_time: 0.001  Rows_sent: 1000  Rows_examined: 20000
SELECT * FROM orders WHERE status='pending';

Analysis and Optimization:

Review the WHERE conditions to reduce unnecessary record reads.
Add indexes if needed.

ALTER TABLE orders ADD INDEX (status);

Using Additional Tools:

pt-query-digest: A tool for analyzing slow query logs.

pt-query-digest /var/log/mysql/slow.log

Security Auditing

Using the Binary Log

Situation: Data tampering due to unauthorized access is suspected.
Check the binary log:

mysqlbinlog /var/lib/mysql/mysql-bin.000001 | grep 'UPDATE users'

Log example:

# at 12345
#2023-12-22T10:45:00 server id 1  end_log_pos 234
UPDATE users SET status='active' WHERE id=1;

Countermeasures:

Review executed queries and trace the source IP address or user ID.
Reevaluate permission management and password policies.
Strengthen firewall settings and add IP restrictions.

Using Additional Tools:

MySQL Enterprise Audit: Enhances log auditing capabilities.
Fail2Ban: Automates detection and blocking of unauthorized access.

Summary

MySQL logs are essential tools for troubleshooting, performance improvement, and security enhancement. Master practical techniques such as diagnosing with error and query logs, optimizing slow queries, and tracking history with binary logs.

6. Important Considerations and Best Practices

Server Load Management Considerations

Impact of Logging on System Load

Logging consumes system resources, and depending on configuration, it may place additional load on the server. Pay special attention to the following settings:

Enabling the General Query Log
Since it records all queries, it can degrade performance in high-load environments. Enable it only when necessary or limit logging to specific queries.
Slow Query Log Threshold Configuration
If the threshold is set too low, a large number of logs may be generated. Proper configuration is essential.

Conducting Load Testing

After modifying log settings, perform load testing to verify the impact on the system.

mysqlslap --user=root --password=password --concurrency=50 --iterations=10 --query="SELECT * FROM test_table;" --create-schema=testdb

Disk Capacity Management Tips

Monitoring Capacity

Log files grow over time, so disk capacity must be monitored regularly.

Check usage:

du -sh /var/log/mysql/

Check available space:

df -h

Rotation and Automatic Cleanup

Configure log rotation to automatically delete old log files and improve capacity management efficiency.

/var/log/mysql/*.log {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql mysql
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}

Security Best Practices

Setting Access Restrictions

Since log files may contain sensitive information, configure appropriate access restrictions.

chmod 640 /var/log/mysql/*.log
chown mysql:mysql /var/log/mysql/*.log

Protection Through Encryption

Encrypting log files reduces the risk of data leakage.

Encryption example:

openssl enc -aes-256-cbc -salt -in /var/log/mysql/general.log -out /var/log/mysql/general.log.enc

Importance of Backup and Recovery Plans

Perform Regular Backups

Back up log files along with databases regularly to ensure recovery in emergencies.

mysqldump -u root -p --all-databases > backup.sql

Test Recovery Procedures

Regularly test whether backups can be restored successfully and document the procedures.

Log Data Archiving Management

Need for Long-Term Storage

For audit purposes or legal requirements, long-term storage of log files may be necessary. In such cases, consider compression and cloud storage solutions.

Compression example:

tar -czvf mysql-logs.tar.gz /var/log/mysql/

Cloud storage transfer example:

aws s3 cp mysql-logs.tar.gz s3://your-bucket-name/

Summary

In MySQL log management, managing server load, disk capacity, security measures, and backup and recovery plans is critical. By implementing these best practices, you can achieve stable operations and stronger security.

7. Conclusion

Reaffirming the Importance of Log Management

MySQL logs play an important role in many areas, including the following:

Troubleshooting
Using error logs, you can identify the causes of server startup errors and configuration mistakes.
Performance Tuning
By utilizing the slow query log, you can identify queries that cause performance degradation and implement improvements.
Security Auditing
Using the binary log, you can strengthen monitoring against unauthorized access and data tampering.

Practical Log Management Tips

Verify Basic Configuration

Properly configure the storage locations for error logs and query logs.
Optimize log output levels according to system requirements.

Log Rotation and Compression Management

Use automatic rotation settings to manage disk capacity while archiving old logs.

Security Measures and Backups

Strengthen log file protection through proper permission management and encryption.
Perform regular backups and restoration tests to ensure quick recovery during failures.

How to Verify After Configuration

Configuration Checklist

The following checklist helps you confirm your configuration:

[ ] Have you enabled and configured the error log, general query log, slow query log, and binary log?
[ ] Are the log file storage locations properly configured and access permissions correctly managed?
[ ] Is log rotation functioning properly and disk capacity being managed?
[ ] Have security measures such as encryption and access restrictions been implemented?
[ ] Have backup and recovery procedures been tested in advance and verified to work correctly?

Use this checklist to prevent configuration oversights and issues.

Future Operations and Improvement Points

Regular Monitoring and Improvement

Regularly review log file contents to monitor for anomalies and errors.
Leverage new tools and security features to enhance the accuracy and efficiency of log management.

Adapting to New Features

Stay updated on new features and configuration options introduced in MySQL version upgrades, and update settings as necessary.

Final Thoughts and Next Steps

MySQL log management is both a fundamental element of reliable database operations and a powerful, flexible tool that can be customized to meet your needs. Use this article as a reference to improve your real-world operations.

Next Steps

Consider introducing log analysis tools and monitoring systems to further optimize configuration and deepen log analysis.
Apply log configuration practices in real projects and systems to enhance troubleshooting and performance optimization.

We hope this guide helps you deepen your understanding of MySQL log management and supports stable, secure database operations.