# Peronca Audit Log fails after Logrotate.d

**URL:** <https://forums.percona.com/t/peronca-audit-log-fails-after-logrotate-d/18839>\
**Category:** MySQL & MariaDB\
**Tags:** percona\
**Created:** [November 30, 2022, 2:40pm UTC](https://forums.percona.com/t/peronca-audit-log-fails-after-logrotate-d/18839 "2022-11-30T14:40:11Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![simonnutsey98](https://avatars.discourse-cdn.com/v4/letter/s/db5fbb/32.png) [@simonnutsey98](https://forums.percona.com/u/simonnutsey98)\
**Post date:** [November 30, 2022, 2:40pm UTC](https://forums.percona.com/t/peronca-audit-log-fails-after-logrotate-d/18839/1 "2022-11-30T14:40:11Z")

</div>

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.

```auto
| 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

```

```auto
/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!

---

<div class="post-metadata">

**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\
**Post date:** [November 30, 2022, 3:48pm UTC](https://forums.percona.com/t/peronca-audit-log-fails-after-logrotate-d/18839/2 "2022-11-30T15:48:49Z")

</div>

Hi, flush logs won’t help here. You need to set [audit\_log\_flush](https://docs.percona.com/percona-server/8.0/management/audit_log_plugin.html#audit_log_flush) in your rotation script

---

<div class="post-metadata">

**Author:** ![simonnutsey98](https://avatars.discourse-cdn.com/v4/letter/s/db5fbb/32.png) [@simonnutsey98](https://forums.percona.com/u/simonnutsey98)\
**Post date:** [November 30, 2022, 5:02pm UTC](https://forums.percona.com/t/peronca-audit-log-fails-after-logrotate-d/18839/4 "2022-11-30T17:02:24Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\
**Post date:** [November 30, 2022, 5:15pm UTC](https://forums.percona.com/t/peronca-audit-log-fails-after-logrotate-d/18839/5 "2022-11-30T17:15:04Z")

</div>

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?](https://forums.percona.com/t/how-to-rotate-audit-log-manually-with-audit-log-plugin-in-percona-mysql-8-0-20/9429)

---

<div class="post-metadata">

**Author:** ![simonnutsey98](https://avatars.discourse-cdn.com/v4/letter/s/db5fbb/32.png) [@simonnutsey98](https://forums.percona.com/u/simonnutsey98)\
**Post date:** [December 1, 2022, 2:56pm UTC](https://forums.percona.com/t/peronca-audit-log-fails-after-logrotate-d/18839/6 "2022-12-01T14:56:08Z")

</div>

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?:

```auto
/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.

```auto
| 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

```auto
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)

```auto
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)

```auto
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!

---

<div class="post-metadata">

**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\
**Post date:** [December 1, 2022, 3:33pm UTC](https://forums.percona.com/t/peronca-audit-log-fails-after-logrotate-d/18839/7 "2022-12-01T15:33:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![simonnutsey98](https://avatars.discourse-cdn.com/v4/letter/s/db5fbb/32.png) [@simonnutsey98](https://forums.percona.com/u/simonnutsey98)\
**Post date:** [December 9, 2022, 12:30pm UTC](https://forums.percona.com/t/peronca-audit-log-fails-after-logrotate-d/18839/8 "2022-12-09T12:30:36Z")

</div>

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:

```auto
/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
}

```