How to separate MySQL Slow Log Using "Logrotate" in Ubuntu?

Logrotate is designed to ease administration of systems that generate large numbers of log files. It allows automatic rotation, compression, removal, and mailing of log files. Each log file may be handled daily, weekly, monthly, or when it grows too large.

Normally, logrotate is run as a daily cron job. It will not modify a log multiple times in one day unless the criterion for that log is based on the log's size and logrotate is being run multiple times each day, or unless the -f or --forceoption is used.

Server environment:

$ cat /etc/issue
Ubuntu 16.04.2 LTS
$ logrotate --version
logrotate 3.8.7

Create logrotate config file

Create file /etc/logrotate.d/3306_error.conf

$ vim /etc/logrotate.d/3306_error

/var/log/mysql/error.log { # log absolute path 
 monthly # cut once a month
 rotate 13 # save 13 times then rotate, the thirteenth will cover the first file
 dateext # log file named as "origin file name + 20170821"
 compress # using gzip to compress 
 delaycompress # the last file without compress (easy to analyze)
 missingok # during log rotation, any errors will be ignored,such as "File no found" 
 notifempty # it will not be split if no new log after last split

 postrotate # command will be executed between label 'postrotate' and 'endscript' 
 mysql -h127.0.0.1 -uroot -puman73 --login-path=3306 -e 'flush error logs;' # mysql flush the error logs
 endscript
}

$ vim /etc/logrotate.d/3306_slow

/var/log/mysql/slow-queries.log { # log absolute path
 daily # cut once a month
 rotate 13 # save 13 times then rotate, the thirteenth will cover the first file
 dateext # log file named as "origin file name + 20170821" 
 compress # using gzip to compress
 delaycompress # the last file without compress (easy to analyze)
 missingok # during log rotation, any errors will be ignored,such as "File no found"
 notifempty # it will not be split if no new log after last split

 postrotate # command will be executed between label 'postrotate' and 'endscript'
 mysql -h127.0.0.1 -uroot -puman73 --login-path=3306 -e 'flush slow logs;'
 endscript
}

Manual cutting the log

logrotate -f /etc/logrotate.d/3306_error

Check Result

$ ls -lh /var/log/mysql/error.log*
 -rw-r----- 1 mysql adm 0 Aug 21 21:06 /var/log/mysql/error.log
 -rw-r----- 1 mysql adm 107K Aug 21 21:05 /var/log/mysql/error.log-20171227