Logrotate implementing MySQL Slow Log segmentation

MySQL slow log?

MySQL's slow query log is a log record provided by MySQL, which is used to record a statement in MySQL that has a response time exceeding the threshold, which refers to SQL that runs longer than the Long_query_time value, and is recorded in the slow query log. The default value of Long_query_time is 10, which means to run statements above 10s.
By default, the MySQL database does not start the slow query log, we need to manually set this parameter, of course, if not tuning needs, it is generally not recommended to start this parameter, because the slow query log on more or less will have some performance impact. The slow query log supports writing log records to a file, and also supports writing log records to a database table.

Slow log generation situation?

The first execution time is too long (greater than the set Long_query_time threshold), the second unused index, or an optimal index is not used.

Both logs are turned off by default, especially those that do not use indexes, because there may be a lot of logs in this category, so there is a special switch log_throttle_queries_not_using_indexes to limit the number of logs that do not use indexes per minute.

Slow log output

MySQL slow query log by default is all written to a file, the file will be more and more large, very inconvenient for us to analyze. Use logrotate to cut MySQL slow query log, error log, to facilitate analysis and find problems.

Define the log theory roll strategy　

[Email protected]_db_40 ~]# Cat Mysql-log-rotate
/opt/mysql/data/idc_db_40-slow.log {
Create MySQL MySQL
Dateext
Notifempty
Daily
MaxAge 60
Rotate 30
Missingok
Compress
olddir/opt/mysql/data/
Postrotate
# just if Mysqld is really running
If Test-x/opt/mysql/bin/mysqladmin && \
/opt/mysql/bin/mysqladmin ping-uroot-p123qwe,./-s/tmp/mysql.sock &>/dev/null
Then
/opt/mysql/bin/mysqladmin flush-logs-uroot-p123qwe,./-s/tmp/mysql.sock
Fi
Endscript
}

Set up Scheduled Tasks

* * * */usr/sbin/logrotate-f mysql-log-rotate

#每天23:59 for log splitting

Effect Show

A compressed package is generated every day very convenient management!!!

Logrotate implementing MySQL Slow Log segmentation