Logrotate implements MySQL slow log Segmentation

Logrotate implements MySQL slow log Segmentation

MySQL slow log?

The slow query log of MySQL is a log record provided by MySQL. It is used to record the statements whose response time exceeds the threshold value in MySQL, specifically the SQL statement whose running time exceeds the long_query_time value, the log is recorded in the slow query log. The default value of long_query_time is 10, which means to run a statement over 10 s.
By default, the MySQL database does not start slow query logs. We need to manually set this parameter. Of course, if it is not required for optimization, it is generally not recommended to start this parameter, because enabling slow query logs may affect performance. Slow query logs support writing log records to files and writing log records to database tables.

Slow log generation?

The first execution takes too long (greater than the configured long_query_time threshold). The second operation does not use an index or use the optimal index.

These two types of logs are disabled by default, especially those without indexes, because these types of logs may have many, therefore, log_throttle_queries_not_using_indexes is used to limit the number of logs with no indexes output per minute.

Slow log output

By default, MySQL slow query logs are all written into a single file, which will become larger and larger, making it very inconvenient for us to analyze. Logrotate is used to cut MySQL slow query logs and error logs for analysis and problem searching.

Define a log rollback Policy

[Root @ IDC_db_40 ~] # Cat mysql-log-rotate
/Opt/mysql/data/IDC_db_40-slow.log {
Create 600 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 scheduled tasks

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

# Log segmentation at every day

Effect display

A compressed package is generated every day for easy management !!!