Overview
I find far too often that MySQL error and slow query logs are unaccounted for. Setting up log rotation helps make the logs manageable in the event that they start to fill up and can help make your troubleshooting of issues more efficient.
Setup
All steps in the examples below are run as the root user. The first step is to setup a user that will perform the log rotation. It is recommended to only give enough access to the MySQL user for the task that it is performing.
Create Log Rotate MySQL User
mysql > CREATE USER 'log_rotate'@'localhost' IDENTIFIED BY '<ENTER PASSWORD HERE>'; mysql > GRANT RELOAD,SUPER ON *.* to 'log_rotate'@'localhost'; mysql > FLUSH PRIVILEGES;</pre>
The next step is to setup the MySQL authentication config as root. Here are two methods to set this up. The first method will be the more secure method of storing your MySQL credentials using mysql_config_editor as the credentials are stored encrypted. But this first method is only available with Oracle MySQL or Percona MySQL client greater than 5.5. It is not available with Maria DB MySQL client. Method 2 can be used with pretty much any setup but is less secure as the password is stored in plain text.
Method 1
bash # mysql_config_editor set \ --login-path=logrotate \ --host=localhost \ --user=log_rotate \ --port 3306 \ --password
Method 2
bash # vi /root/.my.cnf [client] user=log_rotate password='<ENTER PASSWORD HERE>' bash # chmod 600 /root/.my.cnf
Now we will test to make sure this is working as expected
Method 1
bash # mysqladmin --login-path=logrotate ping
Method 2
bash # mysqladmin ping
The paths for the error and slow query log will need to be gathered in order to place them in the logrotate config file
Method 1
bash # mysql --login-path=logrotate -e "show global variables like 'slow_query_log_file'" bash # mysql --login-path=logrotate -e "show global variables like 'log_error'"
Method 2
bash # mysql -e "show global variables like 'slow_query_log_file'" bash # mysql -e "show global variables like 'log_error'"
Finally we will create the log rotation file with the following content. Make sure to update your error and slow query log paths to match the paths gathered in previous steps. Start by opening up the editor for a new mysql logrotate file in the /etc/logrotate.d directory.
Method 1 Content
bash # vi /etc/logrotate.d/mysql /var/lib/mysql/error.log /var/lib/mysql/slow.queries.log { create 600 mysql mysql daily rotate 30 missingok compress sharedscripts postrotate if test -x /usr/bin/mysqladmin && env HOME=/root /usr/bin/mysqladmin --login-path=logrotate ping > /dev/null 2>&1 then env HOME=/root/ /usr/bin/mysql --login-path=logrotate -e 'select @@global.long_query_time into @lqt_save; set global long_query_time=2000; set global slow_query_log=0; select sleep(2); FLUSH ERROR LOGS; FLUSH SLOW LOGS;select sleep(2); set global [email protected]_save; set global slow_query_log=1;' > /var/log/mysqladmin.flush-logs 2>&1 fi endscript }
Method 2 Content
bash # vi /etc/logrotate.d/mysql /var/lib/mysql/error.log /var/lib/mysql/slow.queries.log { create 600 mysql mysql daily rotate 30 missingok compress sharedscripts postrotate if test -x /usr/bin/mysqladmin && env HOME=/root /usr/bin/mysqladmin ping > /dev/null 2>&1 then env HOME=/root/ /usr/bin/mysql -e 'select @@global.long_query_time into @lqt_save; set global long_query_time=2000; set global slow_query_log=0; select sleep(2); FLUSH ERROR LOGS; FLUSH SLOW LOGS;select sleep(2); set global [email protected]_save; set global slow_query_log=1;' > /var/log/mysqladmin.flush-logs 2>&1 fi endscript }
Validation
For final validation force a log rotate. Update the path in the ls command to match the path of the logs gathered earlier.
bash # logrotate --force /etc/logrotate.d/mysql bash # ls -al /var/lib/mysql
1 Comment. Leave new
Great post Kevin! also one thing to keep in mind if using GTID is make sure to set session sql_log_bin=0 before running the flush commands to avoid generating errant transactions.