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 [code language="sql"] mysql > CREATE USER 'log_rotate'@'localhost' IDENTIFIED BY '<ENTER PASSWORD HERE>'; mysql > GRANT RELOAD,SUPER ON *.* to 'log_rotate'@'localhost'; mysql > FLUSH PRIVILEGES;</pre> [/code] 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 [code language="bash"] bash # mysql_config_editor set \ --login-path=logrotate \ --host=localhost \ --user=log_rotate \ --port 3306 \ --password [/code]
Method 2 [code language="bash"] bash # vi /root/.my.cnf [client] user=log_rotate password='<ENTER PASSWORD HERE>' bash # chmod 600 /root/.my.cnf [/code] Now we will test to make sure this is working as expected
Method 1 [code language="bash"] bash # mysqladmin --login-path=logrotate ping [/code]
Method 2 [code language="bash"] bash # mysqladmin ping [/code] 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 [code language="bash"] 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'" [/code]
Method 2 [code language="bash"] bash # mysql -e "show global variables like 'slow_query_log_file'" bash # mysql -e "show global variables like 'log_error'" [/code] 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 [code language="bash"] 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 long_query_time=@lqt_save; set global slow_query_log=1;' > /var/log/mysqladmin.flush-logs 2>&1 fi endscript } [/code]
Method 2 Content [code language="bash"] 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 long_query_time=@lqt_save; set global slow_query_log=1;' > /var/log/mysqladmin.flush-logs 2>&1 fi endscript } [/code]
Validation
For final validation force a log rotate. Update the path in the ls command to match the path of the logs gathered earlier. [code language="bash"] bash # logrotate --force /etc/logrotate.d/mysql bash # ls -al /var/lib/mysql [/code]
"Kevin Markwardt has twenty years of system administration experience ranging from MySQL, Linux, Windows, and VMware. Over the last six years he has been dedicated to MySQL and Linux administration with a focus on scripting, automation, HA, and cloud solutions. Kevin has lead and assisted with many projects focusing on larger scale implementations of technologies, including ProxySQL, Orchestrator, Pacemaker, GCP, AWS RDS, and MySQL. Kevin Markwardt is a certified GCP Professional Cloud Architect, and a certified AWS Solutions Architect - Associate. Currently he is a Project Engineer at Pythian specializing in MySQL and large scale client projects. One of his new directives is Postgres and is currently supporting multiple internal production Postgres instances."