[Simplified explanation] How to log rotate MySQL's slow.log file

Hello.

am an apprentice engineer

 

Do you all have times like this too? ?

“Well, now that I have some free time, I might try typing the df command.”

“Um, / directory usage is 95%?? Why?”

“Which one is the most common?”

“I-i, when did this happen!? So many MySQL slow.log files have accumulated!?”

“I have to do something...but what should I do!!!”

In such cases, use log rotation.

What is log rotation?

First, let's look at the meaning of the words.

log…“record”

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

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

The result of the man command also says:

logrotate is designed to ease the management of systems that generate large numbers of log files.
Designed for ease of management. Log files can be automatically rotated, compressed, deleted, and emailed.
Log files can be automatically rotated, compressed, deleted, and mailed. 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 technique to avoid compressing or deleting a large number of log files so as not to overwhelm the disk space.

the goal

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

The settings are as follows.

environment

concrete flow

MySQL slow query log output settings

*MySQL installation is omitted.

Checking the initial settings

Enter the following command while logged in to MySQL.

SHOW VARIABLES LIKE "%slow%";

○Output result example

+--------------------------+------------------- ----------------+ | 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 turning on slow_query_log, slow logs appear to be output to /var/lib/mysql/localhost-slow.log.

 

Set the judgment time as a slow query

SET GLOBAL long_query_time = 1.0;

Verify that the changes have been made using the command below

SHOW GLOBAL VARIABLES LIKE '%query_time%';

○Output result

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

 

Turn on slow query log output settings

SET GLOBAL slow_query_log = 'ON';

It is OK if the output of the command below looks like the example output result.

SHOW GLOBAL VARIABLES LIKE '%slow_query%';

○Output result example

+---------------------+--------------------------- --------+ | Variable_name | Value | +----------------------+------------- -----------------------+ | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/localhost-slow.log | +------ ----------------+-------------------------------- --+ 2 rows in set (0.03 sec)

 

 

Creating a user for FLUSH LOG

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

To check whether it has been created, in addition to the existence of the user in the output of the command below, it is OK if you actually log in and it is successful.

select user,host from mysql.user;

 

 

Grant privileges to user for FLUSH LOG

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

To check whether it has been created, check whether the intended permissions have been granted in the output of the command below.

SHOW GRANTS FOR 'flush'@'localhost';

○Output result example

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

Adjust the privileges of the created flush user when logging in

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

○Edited content

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

 

Reopen log file

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

It is a success if no errors are output!

Log rotation settings

1. Editing the slow.log rotation configuration file
2. test run

Editing the slow.log rotation configuration file

Move to working directory

cd /etc/logrotate.d/

Writing the configuration file

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

vi mysql

↓Description content

/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 the above setting example.
*There are many more options, so please check them out if you are interested. You can adapt it to the conditions and situations you want to execute.

1. daily (default weekly)
   Rotate every week
2. rotate [any number] *This time 7
   Decide the number of generations you want to keep
3. missingok
   Continue processing without error even if log file does not exist
4. notifempty
   Do not rotate log file if it is empty
5. delaycompress *Used in conjunction with compress.
   Delays log compression until the next rotation. Specified with compress
6. copytruncateCopy
   the log file and delete the contents of the source
7. compressCompress
   rotated logs with gzip
8. 
   Execute the command written between
   postrotate ~ endscript (In this example, the slow.log file will be reloaded if ping communication to mysql is established.)

test run

Now, all that's left to do is hope that no errors occur.

logrotate -dv /etc/logrotate.d/mysql

The content to be executed will be output, so if there are no error statements, it is OK.

Finally

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

When a service is running, logs inevitably accumulate.

Not everyone can expand their disk capacity infinitely, so please rotate them wisely and live a frugal life!