Have you experienced the MySQL slow.log file that has been accumulating for some time?
Don’t you worry, you can use log rotation in this case.
What is log rotation?
“Logrotate is designed to ease administration of systems that generate large numbers of log files. It allows automatic rotation, compression, removal, and sending log files via email. Each log file is handled on a daily, weekly, monthly, or when it becomes too large.”
It’s a way to compress or delete a large amount of log files so as not to overwhelm disk space.
Goal
In this article, I'm going to compress a slow log that has a MySQL slow query.
The setting is the following below.
Setting details | Value |
Log | /var/lib/mysql/localhost-slow.log |
Rotation period | daily |
Retention count | 7 |
Slow log determination time | 1.0(second) |
Environment
OS/Package | Version |
Karnel | 3.10.0 |
Cent OS | 7.2.1 |
MySQL | 8.0.31 |
logrotate | 3.8.6 |
Steps
Enable MySQL slow log query output
*I omitted how to install MuSQL in this step.
Check the default setting
Login to MySQL and then type the following command.
SHOW VARIABLES LIKE "%slow%";Example of output
+-----------------------------+-----------------------------------+
| Variable_name | Value |
+-----------------------------+-----------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_replica_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+-----------------------------+-----------------------------------+
7 rows in set (0.31 sec)By changing to “ON” for “show_query_log”, it outputs slow logs to /var/lib/mysql/localhost-slow.log.
Configuration of determination time as slow query
SET GLOBAL long_query_time = 1.0;Make sure the change is applied using the following command.
SHOW GLOBAL VARIABLES LIKE '%query_time%';The result output
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.01 sec)Turning on slow query log output setting
SET GLOBAL slow_query_log = 'ON';It is correct if the output of the following command looks like the example output result.
SHOW GLOBAL VARIABLES LIKE '%slow_query%';The result output
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.03 sec)Create a user for FLUSH LOG
create user 'flush'@'localhost' identified by '[arbitrary password]';To confirm if the user is successfully created, check the output of the following command and try logging into the user.
select user,host from mysql.user;Give permissions to Flash LOG user
GRANT RELOAD ON *.* TO 'flush'@'localhost';Check output of the following command to confirm if the permission is given.
SHOW GRANTS FOR 'flush'@'localhost';The result output
+-----------------------------------------------------+
| Grants for flush@localhost |
+-----------------------------------------------------+
| GRANT RELOAD, PROCESS ON *.* TO `flush`@`localhost` |
+-----------------------------------------------------+
1 row in set (0.04 sec)Adjust permissions for created flush user when login
vi /var/lib/mysql/.my.cnfContents to edit
[client]
user=flush
password=[the password previously chosen]Re-open the log file
mysql --defaults-extra-file=/var/lib/mysql/.my.cnf -e "FLUSH SLOW LOGS;"It’s done if there’s no error.
log rotate configuration
Edit the slow.log rotate setting file
Test execution
Edit the slow.log rotate setting file
Go to the working directory.
cd /etc/logrotate.d/Configuration file description.
*Do this as a root user.
(The requirements are 1. file owner/group must be root:root 2. file permissions must be 644)
vi mysql↓Configuration contents
/var/lib/mysql/*slow.log {
daily
rotate 7
dateext
missingok
notifempty
sharedscripts
delaycompress
copytruncate
compress
postrotate
# just if mysqld is really running
if test -x /usr/bin/mysqladmin
/usr/bin/mysqladmin --defaults-extra-file=/var/lib/mysql/.my.cnf ping &> /dev/null
then
mysql --defaults-extra-file=/var/lib/mysql/.my.cnf -e "FLUSH SLOW LOGS;"
fi
endscript
}
Details
I’m going to explain the details of the setting above.
*You can also look up more options. It can be customized to the conditions and situations you wish to execute.
1.daily (The default is weekly)
Run rotate every day.
2.rotate[arbitrary number] *this time 7
Retention count number you want.
3.misssingok
Continue processing without displaying errors even when the log file does not exist.
4.notifempty
No rotation if the log file is empty.
5.delaycompress *use with “compress”
Delay log compression until the next rotation, specified with compress.
6.copytruncate
Copy log files and delete the original files.
7.compress
Compress the rotated log with gzip
8.postrotate - endscript
Execute the command described in the middle after the log rotation.
(In this example, the slow.log file is reloaded while ping communication is available to mysql.)
Test execution
Crossing my fingers that there’s no error.
logrotate -dv /etc/logrotate.d/mysqlIt displays execution information. As long as there’s no error message, it’s all set.
Summary
I introduced a way to rotate slow.log files in MySQL.
When the service is running, inevitably, logs will accumulate.
It’s good to keep your log file rotated to save your disk space!
This blog post is translated from a blog post written by Hoto on our Japanese website Beyond Co..
Commentaires