Peronca Audit Log fails after Logrotate.d

MySQL & MariaDB
1

Hi, first post and im hoping a relatively straight forward fix.

Im currently trying to use the audit log plugin across 3 servers and have this information feed to a kibana dashboard which has been successful on 2/3 servers. All these servers as are setup the same as they are used for replication so there shouldn’t be any glaringly obvious reason that it shouldn’t work.

A bit more background is that our logrotate config that is setup on these servers, runs at 6am every morning and the logging works just fine up until that point. After logrotate runs, audit.log no longer is being logged too until MySQL is restarted (This needs to be done manually).

I have tried all manner of permissions and settings but for some reason there is just something about MySQL not being restarted that is causing it to have an issue but i cant seem to find out what it is.

At first i thought this may be something with logrotate and the audit_log_rotations functionality that is built into the plugin conflicting with each other but this will surely affect functionality across the remaining servers?

Below are the current settings that i have for audit.log and our current logrotate config.

| audit_log_file              | /var/log/mysql/audit.log                                                                                                                                                                                                                                                                                         |
| audit_log_flush             | OFF                                                                                                                                                                                                                                                                                                              |
| audit_log_format            | JSON                                                                                                                                                                                                                                                                                                             |
| audit_log_handler           | FILE                                                                                                                                                                                                                                                                                                             |
| audit_log_include_accounts  |                                                                                                                                                                                                                                                                                                                  |
| audit_log_include_commands  |                                                                                                                                                                                                                                                                                                                  |
| audit_log_include_databases |                                                                                                                                                                                                                                                                                                                  |
| audit_log_policy            | ALL                                                                                                                                                                                                                                                                                                              |
| audit_log_rotate_on_size    | 20480                                                                                                                                                                                                                                                                                                            |
| audit_log_rotations         | 2                                                                                                                                                                                                                                                                                                                |
| audit_log_strategy          | ASYNCHRONOUS                                                                                                                                                                                                                                                                                                     |
| audit_log_syslog_facility   | LOG_USER                                                                                                                                                                                                                                                                                                         |
| audit_log_syslog_ident      | percona-audit                                                                                                                                                                                                                                                                                                    |
| audit_log_syslog_priority   | LOG_INFO

/var/log/mysql/*log {
        daily
        rotate 7
        missingok
        create 640 mysql adm
        compress
        postrotate
          # run if mysqld is running
          if test -n "`ps acx|grep mysqld`"; then
          /usr/bin/mysqladmin flush-logs
          fi
        endscript
}

Am i simply missing something that is causing this to stop working? As far as i observe, lograte is rotating audit.log and calling it audit.log.1.gz and peronca is coming in and creating a new audit.log file but it just isn’t passing any information to it until MySQL is restarted.

Any help or information would be perfect!

2

Hi, flush logs won’t help here. You need to set audit_log_flush in your rotation script

2 Likes
4

Hi Ivar

Im sorry but I dont know if my post is clear. Audit logging is running fine all day until it hits 6am when logrotate runs and then it no longer logs to the new audit.log that Percona creates in place of the now rotated one. That is until i restart MySQL and it works again just fine.

Do you think this could be something with Flush logs being run in logrotate.d?

When i check, the thing that audit log last recorded is flush-logs and then nothing afterwards.

This is the same on my other servers and that works just fine?

1 Like
5

Hi, please read the link I posted. You need to call SET audit_Log_flush=1 as part of your log rotate script, to reopen the log file after rotation. Flush logs doesn’t apply to the audit log. See also How to rotate audit log manually with audit_log plugin in Percona MySQL 8.0.20?

1 Like
6

Hi, I dont think that seems to have fixed my issue either. I have tried to incorporate what you said into my logrotate script (I have also tried it out of the if statement) Is this what you meant by incorporating?:

/var/log/mysql/*log {
        daily
        rotate 7
        missingok
        create 640 mysql adm
        compress
        postrotate
          # run if mysqld is running
          if test -n "`ps acx|grep mysqld`"; then
          /usr/bin/mysqladmin flush-logs
          audit_Log_flush=1
          fi
        endscript
}

I have also tried setting it to 1 in the audit.log variables and the instigating log rotate but to no avail.

| audit_log_file              | /var/log/mysql/audit.log                                                                                                                                                                                                                                                                                         |
| audit_log_flush             | ON                <<<<<<<<<                                                                                                                                                                                                                                                                                               |
| audit_log_format            | JSON

Steps to replicate

1 - rm * /var/log/mysql

2 - sudo service mysql restart
output (Note that information is in audit.log as soon as Mysql is restarted

drwxr-x---  2 mysql adm      62 Dec  1 14:46 ./
drwxrwxr-x 15 root  syslog 4096 Dec  1 06:25 ../
-rw-r-----  1 mysql mysql   180 Dec  1 14:46 audit.log
-rw-r-----  1 mysql mysql  2479 Dec  1 14:46 error.log
-rw-r-----  1 mysql mysql   249 Dec  1 14:46 mysql-slow.log

3 - Instigate Logrotate - logrotate -f /etc/logrotate.d/mysql-server
output (Note that once rotate there is no information in audit.log)

drwxr-x---  2 mysql adm     133 Dec  1 14:48 ./
drwxrwxr-x 15 root  syslog 4096 Dec  1 06:25 ../
-rw-r-----  1 mysql adm       0 Dec  1 14:48 audit.log
-rw-r-----  1 mysql mysql   149 Dec  1 14:48 audit.log.1.gz
-rw-r-----  1 mysql adm       0 Dec  1 14:48 error.log
-rw-r-----  1 mysql mysql   892 Dec  1 14:48 error.log.1.gz
-rw-r-----  1 mysql adm     249 Dec  1 14:48 mysql-slow.log
-rw-r-----  1 mysql mysql   236 Dec  1 14:48 mysql-slow.log.1.gz

4 Sudo service mysql restart
output (Note that one restarted, information is no being logged again but ONLY once restarted)

drwxr-x---  2 mysql adm     133 Dec  1 14:53 ./
drwxrwxr-x 15 root  syslog 4096 Dec  1 06:25 ../
-rw-r-----  1 mysql adm     180 Dec  1 14:54 audit.log
-rw-r-----  1 mysql mysql   150 Dec  1 14:53 audit.log.1.gz
-rw-r-----  1 mysql adm    2805 Dec  1 14:54 error.log
-rw-r-----  1 mysql mysql   892 Dec  1 14:53 error.log.1.gz
-rw-r-----  1 mysql adm     498 Dec  1 14:54 mysql-slow.log
-rw-r-----  1 mysql mysql   234 Dec  1 14:53 mysql-slow.log.1.gz

Any further advice would be helpful!

1 Like
7

Hi, set audit_Log_flush=1 needs to be run from MySQL shell which is missing from your script. This causes the audit log file to be reopened. You have to run this inside the logrotate script, don’t add it to my.cnf file.

1 Like
8

Hi,

Sorry for late confirmation but it looks as though that has fixed it!

A side note to anyone who is experiencing this issue is that if you set the audit_log_rotate_on_size and it rotates itself before logrotate has a chance to, this will also break it!

Logrotate and audit_log_rotate_on_size cannot seemingly be set at the same time as it causes conflict.

See below for correct config:

/var/log/mysql/*log {
        daily
        rotate 7
        missingok
        create 640 mysql adm
        compress
        postrotate
          # run if mysqld is running
          if test -n "`ps acx|grep mysqld`"; then
          mysql -e "set global audit_log_flush=1;" <<<<<<<<<
          /usr/bin/mysqladmin flush-logs
          fi
        endscript
}
1 Like