Pythian Blog: Technical Track

MySQL log rotation

by Kevin Markwardt on Mar 22, 2018 12:00:00 AM

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 [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]
Topics: MySQL Technical Track

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.