top of page

Posts

How to rotate the MySQL slow.log file in log rotation




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.cnf

Contents 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


  1. Edit the slow.log rotate setting file

  2. 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/mysql

It 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..


Recent Posts

See All

Comments


bottom of page