How to use logrotate for log rotation in MariaDB
Logrotate takes care of log files generated by the systems through automatic rotation, compression, removal and mailing of log files; Most of the UNIX and Linux distribution offer logrotate utility. In this post we have explained how to configure log rotation for the error log, general query log, and the slow query log. You can use logrotate to handle log files daily / weekly / monthly / when it grows too large. We usually schedule logrotate as daily cron job, The logrotate utility needs to be able to authenticate with MariaDB in order to flush the log files. The easiest way to allow the logrotate utility to authenticate (in MariaDB 10.4) with MariaDB is to configure the root@localhost user account to use unix_socket authentication. In MariaDB 10.3 and before, you need to install the unix_socket plugin before you can configure the root@localhostuser account to use it. For example:
INSTALL SONAME 'auth_socket';
After the plugin is installed, the root@localhost user account can be configured to use unix_socket authentication.
Starting with MariaDB 10.2
ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket;
MariaDB until 10.1
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION;
Configuring MariaDB Log Files and Locations
To keep log rotation configuration simple, please accommodate MariaDB logs in a dedicated log directory. We have explained below the configuration of same below:
- error_log – The error log filename and location
- general_query_log – The general query log filename and location
- slow_query_log – The slow query log filename and location
We have have accommodated MariaDB logs in /var/log/mysql , then we could configure the following:
[mariadb] ... log_error=/var/log/mysql/mariadb.err general_log general_log_file=/var/log/mysql/mariadb.log slow_query_log slow_query_log_file=/var/log/mysql/mariadb-slow.log long_query_time=1
We will also need to create the relevant directory:
sudo mkdir /var/log/mysql/ sudo chown mysql:mysql /var/log/mysql/ sudo chmod 0770 /var/log/mysql/
In case if you are using SELinux, then you may also need to set the SELinux context for the directory:
sudo semanage fcontext -a -t mysqld_log_t "/var/log/mysql(/.*)?" sudo restorecon -Rv /var/log/mysql
Configuring Logrotate
On many systems, the primary logrotate configuration file is located at the following path:
- /etc/logrotate.conf
And the logrotate configuration files for individual services are located in the following directory:
- /etc/logrotate.d/
We can create a logrotate configuration file for MariaDB with the following command:
sudo tee /etc/logrotate.d/mariadb <<EOF
/var/log/mysql/* {
missingok
create 660 mysql mysql
notifempty
daily
minsize 1M # only use with logrotate >= 3.7.4
maxsize 100M # only use with logrotate >= 3.8.1
rotate 30
# dateext # only use if your logrotate version is compatible with below dateformat
# dateformat .%Y-%m-%d-%H-%M-%S # only use with logrotate >= 3.9.2
compress
delaycompress
sharedscripts
olddir archive/
createolddir 770 mysql mysql # only use with logrotate >= 3.8.9
postrotate
# just if mysqld is really running
if test -x /usr/bin/mysqladmin && \
/usr/bin/mysqladmin ping &>/dev/null
then
/usr/bin/mysqladmin --local flush-error-log \
flush-engine-log flush-general-log flush-slow-log
fi
endscript
EOF
Each specific configuration directive does the following:
| Logrotate configuration option | Description |
|---|---|
| missingok | This directive configures it to ignore missing files, rather than failing with an error. |
| create 660 mysql mysql | This directive configures it to recreate the log files after log rotation with the specified permissions and owner. |
| notifempty | This directive configures it to skip a log file during log rotation if it is empty. |
| daily | This directive configures it to rotate each log file once per day. |
| minsize 1M | This directive configures it to skip a log file during log rotation if it is smaller than 1 MB. This directive is only available with logrotate 3.7.4 and later. |
| maxsize 100M | This directive configures it to rotate a log file more frequently than daily if it grows larger than 100 MB. This directive is only available with logrotate 3.8.1 and later. |
| rotate 30 | This directive configures it to keep 30 old copies of each log file. |
| dateext | This directive configures it to use the date as an extension, rather than just a number. This directive is only available with logrotate 3.7.6 and later. |
| dateformat .%Y-%m-%d-%H-%M-%S | This directive configures it to use this date format string (as defined by the format specification for strftime) for the date extension configured by the dateext directive. This directive is only available with logrotate 3.7.7 and later. Support for %H is only available with logrotate 3.9.0 and later. Support for %M and %S is only available with logrotate 3.9.2 and later. |
| compress | This directive configures it to compress the log files with gzip. |
| delaycompress | This directive configures it to delay compression of each log file until the next log rotation. If the log file is compressed at the same time that it is rotated, then there may be cases where a log file is being compressed while the MariaDB server is still writing to the log file. Delaying compression of a log file until the next log rotation can prevent race conditions such as these that can happen between the compression operation and the MariaDB server's log flush operation. |
| olddir archive/ | This directive configures it to archive the rotated log files in /var/log/mysql/archive/. |
| createolddir 770 mysql mysql | This directive configures it to create the directory specified by the olddir directive with the specified permissions and owner, if the directory does not already exist. This directive is only available with logrotate 3.8.9 and later. |
| sharedscripts | This directive configures it to run the postrotate script just once, rather than once for each rotated log file. |
| postrotate | This directive configures it to execute a script after log rotation. This particular script executes the mysqladmin utility, which executes the FLUSH statement, which tells the MariaDB server to flush its various log files. When MariaDB server flushes a log file, it closes its existing file handle and reopens a new one. This ensure that MariaDB server does not continue writing to a log file after it has been rotated. This is an important component of the log rotation process. |
If our system does not have logrotate 3.8.9 or later, which is needed to support the createolddir directive, then we will also need to create the relevant directory specified by the olddir directive:
sudo mkdir /var/log/mysql/archive/ sudo chown mysql:mysql /var/log/mysql/archive/ sudo chmod 0770 /var/log/mysql/archive/
How to test and confirm logrotate is working ?
We can test log rotation by executing the logrotate utility with the –force option. For example:
sudo logrotate --force /etc/logrotate.d/mariadb
Keep in mind that under normal operation, the logrotate utility may skip a log file during log rotation if the utility does not believe that the log file needs to be rotated yet. For example:
- If you set the notifempty directive mentioned above, then it will be configured to skip a log file during log rotation if the log file is empty.
- If you set the daily directive mentioned above, then it will be configured to only rotate each log file once per day.
- If you set the minsize 1M directive mentioned above, then it will be configured to skip a log file during log rotation if the log file size is smaller than 1 MB.
However, when running tests with the –force option, the logrotate utility does not take these options into consideration.
References
- logrotate- https://linux.die.net/man/8/logrotate
- MariaDB logrotate – https://mariadb.com/kb/en/rotating-logs-on-unix-and-linux/
