MySQL common log cleaning strategies

Preface:

MySQL database servers use multiple types of logs to record operations and events, which are very important for troubleshooting, auditing, and performance analysis. However, these log files will continue to grow over time and may take up a lot of disk space. Therefore, it is necessary to clean up these logs regularly. In this article, let's learn how to clean up log files in MySQL.

 Binary Log

The binlog records all DDL (Data Definition Language) and DML (Data Manipulation Language) changes to the database. It is generally recommended to enable binlog. However, be aware that binlog will take up a lot of disk space, especially if your database is very busy. At this time, you need to develop a cleanup strategy.

MySQL 5.7 can set the binlog deletion time through the expire_logs_days parameter. Set the expire_logs_days parameter in the my.cnf configuration file to specify the expiration days of the binary log file. Expired log files will be automatically deleted. In MySQL 8.0, it is recommended to use the binlog_expire_logs_seconds parameter, which also controls the expiration time of binary files in seconds. How long to keep binlog can be determined by disk space. If there is enough disk, you can keep more. It is generally recommended to keep at least 7 days.

In addition to automatic cleaning by setting parameters, binlog can also be cleaned manually using the PURGE BINARY LOGS command. For example, use purge binary logs to 'mysql-bin.000009' to delete log files before mysql-bin.000009, or use purge binary logs before '2024-07-15 00:00:00' to delete log files before a specified time.

General Query Log

MySQL's general_log is a log that records all SQL statements that reach the MySQL server. Since it records all SQL statements, including connection, query, update and other operations, its log volume may grow very quickly. It is usually not recommended to enable this function in a production environment to avoid affecting performance. If your database has general_log enabled for security assessment or other reasons, you must formulate a cleanup strategy in a timely manner.

The official does not provide parameters or commands for cleaning general_log, so cleaning general_log can only be done by individual means. Generally, you can write a shell script to perform the cleaning, such as switching logs every morning and deleting log files from a few days ago. You can also use the logrotate function to configure general_log to rotate and clean up automatically.

Error Log

The error log records the errors and warnings that occur when the MySQL server is started, shut down, and running. It is usually enabled by default, but the error log grows very slowly and usually does not need to be cleaned up frequently. You can clean it up manually or set a regular task to clean up old log files. The error log can be retained for a longer time.

Slow Query Log

The slow log is mainly used to record SQL queries whose execution time exceeds the set threshold. The slow query log is very important for database performance optimization because it can help database administrators and developers identify and optimize queries with low execution efficiency. It is also recommended to enable the slow log.

Normally, we can set the slow SQL threshold according to the system situation, such as 1s or 3s. Generally, the slow log does not grow very fast. As long as SQL optimization is continuously performed, the slow log will become less and less. Usually, the slow log does not need to be cleaned frequently. Generally, we can rename it once a week or a month, and then keep a few copies to formulate a cleaning strategy, which can be automatically executed by a shell script.

Audit Log

MySQL Community Edition does not officially provide audit logs. If you want to enable audit logs, you can only use other audit plugins such as MariaDB or Percona Server. The audit log grows very fast. Generally, audit plugins provide cleanup parameters, such as how many MB the log file reaches to automatically rotate, how many log files to keep, etc. Be sure to set these parameters to prevent a large amount of disk space from being occupied.

Relay Log

Relay logs are temporary log files used during MySQL replication to store binary log events received from the master server. These log files are used by the slave server to apply updates from the master server. Relay logs only exist on the slave server. Relay log files will gradually grow as events are applied, so appropriate cleanup strategies are also required to manage these files.

MySQL officially provides the relay_log_pure parameter, which determines whether the relay log file should be automatically deleted after being fully applied. This parameter has two possible values: ON and OFF. Setting it to ON means that the relay log will be automatically deleted after the application is completed, and OFF means that it will not be automatically deleted. In general, it is recommended to enable this parameter so that the relay log will be cleaned up after application and will not take up a lot of disk space.

If your slave server requires the relay_log_pure parameter to be turned off, for example, in the MHA high-availability architecture, in order to ensure that the relay log can be used for recovery during failover, it is usually necessary to disable the automatic cleanup of relay logs on the slave server. At this time, you have to think of other ways to clean up the relay log. MHA provides a perl script called purge_relay_logs, which can be used with the purge_relay_logs script and cronjob to complete this cleanup task. If the purge_relay_logs script cannot be used, you can only write a shell script yourself. For example, you can set relay_log_pure to ON regularly, and then execute flush relay logs, and then set relay_log_pure to OFF. This operation can generally clean up the relay log. If all else fails, we can also use the find command to find the log files from a few days ago, and then directly rm them to clean them up. However, using find to find and then directly rm them to delete them will cause the relay log recorded in the relay-log.indx index file to not match the actual relay log. So after directly rm-ing the relay log, remember to update the relay-log.indx index file.

 Summarize:

This article briefly introduces six common logs and their cleanup strategies in MySQL. Different cleanup strategies can be adopted in different environments. This article only provides a way of thinking. There are various methods. The important thing is to formulate a reasonable log retention strategy according to the actual situation and ensure that it will not affect the normal operation and backup requirements of the database.