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
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, ensure that the slow query log is enabled in your MySQL configuration file (my.cnf
or my.ini
):
1 2 3 |
slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 2 |
The above configuration enables the slow query log and specifies the file where the logs are written. It also sets the threshold for slow queries (long_query_time
) to 2 seconds.
2. Create a Logrotate Configuration: Create a new logrotate configuration file, such as /etc/logrotate.d/mysql-slow
.:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
/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. Thekill -s USR1
command sends a signal to MySQL to flush the logs.
3. Testing Your Logrotate Configuration: You can test your logrotate configuration by using the following command:
1 |
logrotate -dv /etc/logrotate.d/mysql-slow |
The -d
flag instructs logrotate
to run in debug mode, while the -v
flag enables verbose mode. Using these flags won’t rotate the logs but will instead show you the actions that would be taken if the command were to run.
4. Automating with Cron:logrotate
is typically configured to run automatically via cron
. Depending on your system, there may already be a cron job set up for logrotate
. You can check the existing crontab by using the following command:
1 |
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.
1 |
crontab -e |
And add the following to run logrotate daily:
1 |
@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.