Automating MySQL Slow Query Log Management with Logrotate

Efficiently archive and maintain your MySQL slow query logs with logrotate, ensuring optimal performance and a well-managed database audit trail for performance analysis

MinervaDB MySQL Maintenance Operations Teams

MySQL Slow Query Log Logrotate

To archive the slow query log in MySQL using Logrotate , you usually need to create a configuration file for logrotate. This file specifies the frequency of log rotation, the number of rotations to retain, and any other actions to perform during rotation.

Here is a step-by-step guide with detailed scripts:

1. Locate or Set Up the Slow Query Log in MySQL: 
First, enable the slow query log in your MySQL configuration file (my.cnf or my.ini). Set the following options:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

This configuration enables the slow query log, specifies its location, and sets a threshold of 2 seconds for slow queries.

2. Create a Logrotate Configuration: 
Next, Create a new logrotate configuration file, such as /etc/logrotate.d/mysql-slow.:

/var/log/mysql/mysql-slow.log {
    weekly
    rotate 4
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql mysql
    postrotate
        # The following line assumes you're using systemd. If you're not, you might use something like 'invoke-rc.d mysql reload > /dev/null'
        /bin/systemctl kill -s USR1 mysqld.service
    endscript
}

Explanation of the directives:

• weekly: This directive rotates the logs each week.
• rotate 4: It keeps 4 weeks of backlogs.
• compress: This directive compresses (gzip) the log files on rotation.
• delaycompress: Delay the compression until the next log rotation cycle.
• missingok: It does not output an error if the log file is missing.
• notifempty: The log will not be rotated if it is empty.
• create 640 mysql mysql: This directive creates new log files with the specified permissions, user, and group.
• postrotate/endscript: These directives specify the commands to run after the log file is rotated. The kill -s USR1 command sends a signal to MySQL to flush the logs.

3. Testing Your Logrotate Configuration: 
Run the following command to test the logrotate setup:

logrotate -dv /etc/logrotate.d/mysql-slow

The -d flag instructs logrotate to run in debug mode, while the -v flag enables verbose mode. These flags do not rotate the logs but instead display the actions the command would perform.

4. Automating with Cron:
Since logrotate runs automatically through cron, check if a cron job is already in place by running:

crontab -l

Or you can look in /etc/cron.daily, /etc/cron.weekly, and other directories for a logrotate script. If it is not already set up, you can add an entry to your crontab.

crontab -e

And add the following to run logrotate daily:

@daily /usr/sbin/logrotate /etc/logrotate.conf

5. Activate Changes: 
Once you have saved your logrotate configuration and set up the cron job, logrotate will handle the slow query log according to the specified settings.

Make sure that you have the necessary permissions to create and modify these files, and adjust the paths and commands to match your server setup. Additionally, there is no need to restart anything after modifying the logrotate configuration; the changes will take effect during the next cron cycle when logrotate runs.