[Quick Explanation] How to Rotate MySQL's slow.log File

Hello

I'm currently working in the Systems Solutions Department as an apprentice engineer

 

Have you ever had a time like this?

"Well, I'm bored, so I'll try typing in the df command."

"What? The / directory is at 95% usage? Why?!"

"Let's see, what's there more of?"

"W-W-When did this happen?! How did so many MySQL slow.log files pile up?!"

"I have to do something... but what should I do?!"

In such cases, log rotation is the solution

What is Log Rotation?

First, let's start with the meaning of the word

log..."record"

rotate... "to circulate, to take turns"

Based on the above, rotating records means "rotating log files."

The man command output also says:

Logrotate is designed to ease the management of systems that generate large numbers of log files.
It can automatically rotate, compress, delete, and mail log files.
It can automatically rotate, compress, delete, and mail log files. Each log file is processed daily, weekly, monthly, or when it becomes too large.
Each log file is processed daily, weekly, monthly, or when it becomes too large.

In other words, it is a way to prevent disk space from being overwhelmed by compressing or deleting large amounts of log files

the goal

In this article, we will compress the slow log, which stores MySQL slow queries

The settings are as follows:

environment

Specific process

MySQL slow query log output settings

* MySQL installation is omitted

Checking the initial settings

Once you are logged in to MySQL, enter the following command:

SHOW VARIABLES LIKE "%slow%";

○Example of output result

+--------------------------+-----------------------------------+ | 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 | /var/lib/mysql/localhost-slow.log | +-----------------------------+-----------------------------------+ 7 rows in set (0.31 sec)

By turning on slow_query_log, the slow log will be output to /var/lib/mysql/localhost-slow.log

 

Set the time to determine whether a query is a slow query

SET GLOBAL long_query_time = 1.0;

Verify that the changes have been made with the following command:

SHOW GLOBAL VARIABLES LIKE '%query_time%';

○ Output results

+-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+ 1 row in set (0.01 sec)

 

Turn on the slow query log output setting

SET GLOBAL slow_query_log = 'ON';

If the output of the command below looks like the example output, then it's OK

SHOW GLOBAL VARIABLES LIKE '%slow_query%';

○Example of output result

+---------------------+-----------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------+ | slow_query_log | ON |

 

 

Create a user for FLUSH LOG

create user 'flush'@'localhost' identified by '[any password]';

To confirm whether the user has been created, check that the user exists in the output of the command below, and if logging in is successful, it is OK

select user,host from mysql.user;

 

 

Grant permission to the user for FLUSH LOG

GRANT RELOAD ON *.* TO 'flush'@'localhost';

To check if the creation was successful, check the output of the following command to see if the intended permissions have been granted

SHOW GRANTS FOR 'flush'@'localhost';

○Example of output result

+----------------------------------------------------+ | Grants for flush@localhost | +----------------------------------------------------+ | GRANT RELOAD, PROCESS ON *.* TO `flush`@`localhost` | +----------------------------------------------------+ 1 row in set (0.04 sec)

Adjust permissions when the created flush user logs in

vi /var/lib/mysql/.my.cnf

Editing content

[client] user=flush password=[password entered in 4.1.4.]

 

Reopen the log file

mysql --defaults-extra-file=/var/lib/mysql/.my.cnf -e "FLUSH SLOW LOGS;"

If no errors are output, it's a success!

Log Rotate Settings

1. Editing the slow.log rotation configuration file
2. Test Run

Editing the slow.log rotation configuration file

Change to the working directory

cd /etc/logrotate.d/

Writing the configuration file

*Please create it as the root user
(requirements are: 1) the file owner/group is root:root 2) the file permissions are 644)

vi mysql

↓Description

/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 }

※Explanation

We will explain using the above example settings.
*There are many more options, so please check them out if you are interested. You can adjust them to suit the conditions and circumstances you want to execute.

1. daily (default weekly) -
   Rotation occurs weekly
2. rotate [any number] *In this case, 7.
   Decide the number of generations you want to keep.
3. missingok
   Continue processing without error even if the log file does not exist
4. notifempty
   Do not rotate log files if they are empty
5. delaycompress *Used in conjunction with compress.
   Delays log compression until the next rotation. Specify with compress.
6. copytruncate
   Copies the log file and deletes the contents of the source
7. compressCompress
   rotated logs with gzip
8. 
   The commands written between
   postrotate and endscript (In this example, the slow.log file is reloaded if ping communication to mysql is successful.)

Test Run

Now, all we can do is hope that no errors occur

logrotate -dv /etc/logrotate.d/mysql

The execution details will be output, so it's OK as long as there are no error messages

Finally

This time, we introduced how to rotate the MySQL slow.log file!

When a service is running, logs inevitably accumulate

Not everyone can expand their disk capacity infinitely, so make sure to rotate it wisely and live a frugal life!