Managing MariaDB Logs with logrotate
We have several MariaDB customers who enable logs for both proactive troubleshooting and statutory audit purposes, We also work for customers with every MariaDB logging mechanism enabled – Error Log, Slow Query Log, MariaDB Audit Log, General Log etc., As you can understand these logs grows in size very aggressively as the business grows. If we don’t manage logs efficiently, They can bring down MariaDB infra. causing database outage (with one this kind of scenario the entire business can go down). How do we at MinervaDB manage these situations ? We look at effective methods of log archiving using logrotate
Table of Contents
What actually logrotate does for you ?
- Rotate the log file( with the date in the filename) when file size reaches a specific size and continue writing on newly created log file.
- Compress the rotated log files to optimally use the available storage space.
- Delete older rotated log file
Configure Authentication to Logrotate MariaDB Log files
To flush the MariaDB log files logrotate utility needs to be able to authenticate with MariaDB Server
MariaDB 10.1 and before
In MariaDB 10.1 and before, the root@localhost user account can be altered to use unix_socket authentication with GRANT statement:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION;
MariaDB starting with 10.2
In MariaDB 10.2 and later, the root@localhost user account can be altered to use unix_socket authentication with the ALTER USER statement:
ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket;
MariaDB 10.3 and before
In MariaDB 10.3 and before, you need to install the unix_socket plugin before you can configure the root@localhost user account to use it:
INSTALL SONAME 'auth_socket';
MariaDB starting with 10.4.
In MariaDB 10.4 and later, the the root@localhost user account is configured to use unix_socket authentication by default, So there is no extra effort or configuration is needed
Configuring Logrotate
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
Logrotate configuration options
| 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. |
How do you test Logrotate working as expected ?
We can test log rotation by executing the logrotate utility with the –force option:
sudo logrotate --force /etc/logrotate.d/mariadb
Confirm logrotate is working after few tests:
$ sudo ls -l /var/log/mysql/archive/ total 21 -rw-rw---- 1 mysql mysql 523 Nov 15 15:31 mariadb.err.11 -rw-rw---- 1 mysql mysql 188 Mar 15 15:30 mariadb.err.17.gz -rw-rw---- 1 mysql mysql 311 Nov 15 15:28 mariadb.err.21.gz -rw-rw---- 1 mysql mysql 982 Nov 15 15:41 mariadb.err.24.gz -rw-rw---- 1 mysql mysql 1719 Nov 16 11:16 mariadb.log.39
