Configuring efficient MySQL Logrotate

I am a Junior DBA at Mydbops. This is my first blog professionally, I would like to brief my encounter with Log-rotate in first few weeks of my work,  Hope it will help other beginners as well. This Blog will cover the following sections.

• Introduction to Log-rotate

• Issues Faced

• Solutions (Fix for the above issues)

• Best practices

  • How to configure the Log-rotate

  • Operation of Log-rotate

  • Files responsible for the Log_rotate utility.

1.0. Introduction to Log-rotate:

• Log-rotate is a utility and it plays an important role in retaining the right information generated by process. We have several log files in our system and it keeps growing day by day.

• As an administrator, We have to properly manage the log files with customised configurations and retention policies depending upon the use cases. To avoid un-necessary disk occupancy.

• After configuring this Log-rotate, We can Overview the log files easily as the current file will be less size and readable ( with split ). We can also easily track the pattern of logging by the volume of logs generated.

2.0 Issues Faced:

Below are the two main problems related to MySQL Logs we usually face on the servers managed.

• Improper Configuration of MySQL Audit logs.

The scenario is, Audit logs are internally rotated by MySQL(MariaDB Plugin) and a minimum of 2-3 logs will be generated each day. So standard Log-rotate configuration with stating name won’t work. So They had to use the wildcard operator at the end of the file name (*). Which made all the logs to repeatedly compress again and again.These Audit logs were ended up in corruption. We have to re-configure the log-rotate with optimal configuration.

• MySQL error logs are not rotated properly.

Another problem is, Though the MySQL error log is rotated, MySQL kept writing to the old file while new file being empty. This can cause confusion and delay in troubleshooting issues at times

3.0. Solutions:

Herewith, I have shared the fixes made for the above 2 issues.

• Fix 1: Improper Configuration of MySQL Audit logs.

  • Using olddir option, We can move all the Old logs into that mentioned directory with compression.

  • Note: The directory given in the olddir must be already there.

/data/audit-log/mysql-audit.log/* {
create 600 mysql mysql
notifempty
daily
rotate 365
dateext
missingok
compress
copytruncate
olddir /data/audit/old_logs
}

• Fix 2: MySQL error logs are not rotated properly.

  • In MySQL, after every rotation, either the MySQL have to flush the logs or we have to use the copytruncate method. Using this copytruncate option, the Log-rotate daemon will copy the file to another new file and truncate the old file. So this will solve the problem.

  • Note: There is the possibility to lose some milliseconds of logs when using copytruncate. The suggestion is schedule the non-traffic time for rotation in “/etc/cron.d/”

/var/lib/mysql/mysqld.log {
   su mysql mysql
       create 600 mysql mysql
       notifempty
       daily
       rotate 5
       missingok
       compress
   copytruncate
    postrotate
       # just if mysqld is really running
       if test -x /usr/bin/mysqladmin && \
          /usr/bin/mysqladmin ping &>/dev/null
       then
          /usr/bin/mysqladmin flush-logs
       fi
    endscript
}

• For better understanding about the Log-rotate, operation and how to configure the Log-rotate, Check the below section also.

4.0. Best Practices:

4.1. Configuration file:

• The Log-rotate can be configured in the 2 different places.

  • One is within “/etc/logrotate.conf” file

  • Other is create a Log-rotate file inside the “/etc/logrotate.d/” directory.

• Let’s see one simple example- Rotate the slow log of MySQL

• This file is placed at /etc/logrotate.d/mysql

/var/lib/mysql/slow_log {
    create 640 mysql mysql
    hourly
    dateext
    rotate 5
    compress
    olddir /var/lib/mysql/old_slowlogs
postrotate
      # just if mysqld is really running
      if test -x /usr/bin/mysqladmin && \
         /usr/bin/mysqladmin ping &>/dev/null
      then
         /usr/bin/mysqladmin flush-logs
      fi
    endscript
}

• The definitions for the above options are explained in the operation section.

4.2. Operations:

• Normally, Log-rotate systematically rotates and archive the system logs.

• By default, it will rotate once a day using a cron scheduler ‘/etc/cron.daily/logrotate’ (or) else it will rotate depending on which option is mentioned in the configuration, it is actually a shell script.

• Some of the configuration options are given below,

  • Daily – Rotate the file daily.

  • Hourly – Rotate the file hourly. [Other options – Weekly, Yearly]

  • Size – Log file will Rotate when it reached the mentioned size.

  • Dateext – Rotated files will get the extension of the date [yyyymmdd]

  • Rotate <n> – ‘n’ number of files to keep in the server

  • Create perm – Create the new file in the mentioned permission

  • Compress – Compress the rotated files. [uses gzip compression]

  • Copytruncate – Copy the active file to another one and truncate the active file.

  • Olddir <dir> – The rotated files will be sent to the mentioned directory if that directory is already present.

  • Postrotate – Actions mentioned inside this section will execute after the each and every rotate takes place

  • Endscript – Used to end the Postrotate, Last action etc…

• For the MySQL slow log above configuration, the log file will rotate hourly. Rotate will keep 5 Log files. The new file will be created in the name of ‘slow_log’, the old file will be converted to ‘slow_log.1’.

• This process will continue and the old file changes its name by ‘slow_log.2’, ‘slow_log.3’, . . . etc.

• Let’s see the output of the above Log-rotate for slow-log configuration

-rw-r-----. 1 mysql mysql 371 Jan 21 13:01 slow_log-2019012114.gz
-rw-r-----. 1 mysql mysql 361 Jan 21 14:01 slow_log-2019012115.gz
-rw-r-----. 1 mysql mysql 372 Jan 21 15:01 slow_log-2019012116.gz
-rw-r-----. 1 mysql mysql 360 Jan 21 16:01 slow_log-2019012117.gz
-rw-r-----. 1 mysql mysql 369 Jan 21 17:01 slow_log-2019012118.gz
-rw-r-----. 1 mysql mysql 596 Jan 21 18:01 slow_log

• Since the dateext option was used, the rotated files get the extension of date format and compress option is to compress the rotated file with gzip and It keeps 5 slow_logs.

• The “/var/lib/logrotate/status” file will be created, when the /etc/cron.daily/logrotate runs. It records the date and time when the log files are rotated. (i.e) logs for Log-rotate.

• The executable file of Log-rotate is “/usr/sbin/logrotate”.

• Log-rotate has many configurations. So we can customize it to get the desired rotated output.

• Actions to be taken while you using hourly in the config file are clearly explained the below section.

Changes have to done while using the hourly option:

Steps To be Followed:

1. Configure logrotate config at /etc/logrotate.d/
2. Configure the logrotate with the above config in /etc/cron.hourly
3. Setup the cron.hourly in /etc/cron.d/

Step 1:

• We have already completed Step 1, /etc/logrotate.d/mysql is saved as given in example above.

Step 2:

• By default, Log-rotate will happen daily. But if you are using hourly in your Log-rotate, below actions have to be taken.

• Copy Log-rotate file from cron.daily to cron.hourly

cp /etc/cron.daily/logrotate /etc/cron.hourly/

• Inside the /etc/cron.hourly/logrotate, specify the Log-rotate file in the below-highlighted area

!/bin/sh
/usr/sbin/logrotate -s /var/lib/logrotate/logrotate.status /etc/logrotate.d/mysql

 EXITVALUE=$?
if [ $EXITVALUE != 0 ]; then
/usr/bin/logger -t logrotate "ALERT exited abnormally with [$EXITVALUE]"

fi
exit 0

Step 3:

• Save the rotate time below in the file at cron.d. say, /etc/cron.d/0hourly

01 * * * * root /etc/cron.hourly

• After all the modifications done, restart the cron service.

sudo systemctl restart crond

The above steps can also be applicable for the weekly or yearly options.

5.0. Conclusion:

Proper configuration of Log-rotate for log files helps us to keep up the database hygiene, It will also provide better readability when troubleshooting the issue.

For better utilization of logs, We can use log pull and aggregation tools such as using Logstash, Datadog etc.

Advertisement