background:
In Linux systems,logrotateIt is a commonly used log file management tool. You can configure it to rotate MySQL's slow query logs, such as rotating them every day or weekly, backing up old log files and regenerating new log files to continue recording!
Create a configuration file
Create a file specifically for configuring MySQL slow query log cutting rules, usually placed/etc//In the directory. Suppose your MySQL slow query log file name is/var/lib/mysql/[hostname]-(Here[Hostname]Need to be replaced with the actual host name, you can usehostnameCommand to view hostname), create the namemysql-slowThe configuration file (file path is/etc//mysql-slow ）
This is the slow log cutting configuration I am currently using, and you can use it directly (replace the password in)
/data/mysql/my3306/log/ {
daily
missingok
rotate 30
compress
notifempty
create 640 mysql mysql
dateext
dateformat -%Y%m%d
sharedscripts
postrotate
/usr/local/mysqlInstall/mysql/bin/mysql -uroot -ppassword -e "SET GLOBAL slow_query_log = OFF; SET GLOBAL slow_query_log = ON;"
endscript
}Key parameter description
1. Rotation frequency and retention strategy
- **
daily**
Check the log file once a day, and the rotation will be triggered if the conditions are met.
- **
rotate 30**
Keep the log files for the last 30 rotations (default is to rotate by day, that is, keep the logs for 30 days).
- **
compress**
Compress old logs as.gzFormat, save disk space.
2. File name and date format
- **
dateext**
Use date as the suffix for the rotating file (e.g.-20231101）。
- **
dateformat -%Y%m%d**
Custom date format:-%Y%m%dexpress- Year, Month, Day(like-20231101）。
3. File permissions and empty file processing
- **
create 640 mysql mysql**
The permissions for the new log file created after rotation are640, the owner ismysqlUsers and groups (must ensure that the MySQL process has write permission).
- **
notifempty**
If the log file is empty, skip the rotation (avoiding the generation of meaningless empty log files).
4. Execute scripts and error handling
- **
sharedscripts**
After all matching log files are rotated,Only execute once postrotateScript (even if there are multiple log files).
- **
postrotate**
After rotation, perform the following operations:
- Temporarily close MySQL slow query log (
slow_query_log = OFF）。 - Restart the slow query log (
slow_query_log = ON), causing MySQL to write to the new log file. -
Notice: Need to be replaced
-p PasswordFor the actual MySQL root password.
5. Others
- **
missingok**
If the log file does not exist, ignore the error and continue execution (avoid task failure due to file loss).
test
After the configuration is completed, it can be run manuallylogrotateCommands to test whether the configuration is correct and whether it works properly
logrotate -vf /etc//mysql-slow
-fParameters indicate that the rotation operation is forced according to the configuration file
Timed task deployment
To avoid repeated execution, remove the logrotate in it
cd /etc/ mv logrotate
crontab deployment
0 2 * * * /usr/sbin/logrotate -vf /etc//mysql-slow
This is the end of this article about how to use logrotete to cut mysql slow logs regularly. For more related content of mysql slow logs, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!