Audit log rotation failing setting is enabled audit_log_flush=1, rotations not occurring preventing file closure

MySQL & MariaDB Other MySQL® Questions
,
1

Audit log rotation isn’t functioning as expected. With audit_log_flush enabled (set to 1), the audit log files remain open, preventing the rotation process from completing successfully. I’ve tried manually rotating the logs with “mv audit.log audit.log.1”, but the server continues to write to the moved file, and no new audit.log file is created.

I’m currently using Percona Server 8.0.36-28.1.

Thanks in advance!

2

Hello Harveer,

Check how, by using inotify, we can see how the file descriptor is closed and opened again when issuing “set global audit_log_flush=1;”.

In session 1, I leave inotifywait monitoring our audit.log:

$ inotifywait -m -e open,close ./audit.log
Setting up watches.
Watches established.

In session 2 I issue:
mysql> set global audit_log_flush=1;

Now in session 1 we see:

$ inotifywait -m -e open,close ./audit.log
Setting up watches.
Watches established.
./audit.log CLOSE_WRITE,CLOSE
./audit.log OPEN

We can see clearly how the file descriptor was closed and opened again.
Also check:
$ mv audit.log audit.log.1
Now I issue:
mysql> set global audit_log_flush=1;
And I can see the new audit.log

$ ls -la audit.log*
-rw-r-----. 1 matias.rollan percona  334 Mar 25 15:53 audit.log
-rw-r-----. 1 matias.rollan percona 4644 Mar 25 15:53 audit.log.1

Best,
Matias

3

Hi Matías,

Thanks again for your help. I wanted to provide an update on my end. I’ve upgraded the Percona Server image to 8.0.41-32.1, which now includes the LogRotate binary. However, the audit log rotation issue persists even with this upgrade.

My current configuration is:

[mysqld]
audit_log_format=JSON
audit_log_file=/var/log/mysql/audit.log
audit_log_rotations=10
audit_log_flush=ON

I also tried SELECT audit_log_rotate(), but that function doesn’t exist. I’m starting to suspect a configuration issue or a bug. Do you have any ideas on how to further troubleshoot this?

Running the following command doesn’t do anything, is this because the flag is set to on in the configd?

mysql> set global audit_log_flush=1;
Query OK, 0 rows affected (0.00 sec)

Any insight would be greatly appreciated.
Thanks again!

4

Also forgot to mention that the linux image (percona-xtradb-cluster:8.0.41-32.1) doesn’t contain the ‘inotifywait’ binary so I can’t test with that.

5

The test I showed you was using PS 8.0.36, and it worked as expected.
I tried using your config, and I can recreate the issue of “not closing and re-opening the file descriptor” when issuing set global audit_log_flush=1 with your config file.
You should remove the following line from your my.cnf, and it worked on my test:

audit_log_flush=ON

The default value of audit_log_flush should be off, and whenever you want to manually close() and open() the file descriptor used for the file, you can do it manually by just running:

mysql> set global audit_log_flush=1;

You can use the inotifywatch utility from the inotify-tools package to monitor a file and understand what it does “in the background” (MODIFY, CLOSE, OPEN,etc). You can monitor it by running:$ inotifywatch -m /var/log/mysql/audit.log and set the global variable audit_log_flush to 1 (after removing it from your my.cnf). It works as expected on my test on 8.0.36

Best,
Matias

6

Thanks again Matias for getting back to me quickly:

I’ve removed the ON for audit_log_flush, Here’s the output of SHOW VARIABLES LIKE '%audit%';:

+-----------------------------+--------------------------+
| Variable_name | Value |
+-----------------------------+--------------------------+
| audit_log_buffer_size | 1048576 |
| audit_log_exclude_accounts | |
| audit_log_exclude_commands | |
| audit_log_exclude_databases | |
| 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 | 0 |
| audit_log_rotations | 10 |
| audit_log_strategy | ASYNCHRONOUS |
| audit_log_syslog_facility | LOG_USER |
| audit_log_syslog_ident | percona-audit |
| audit_log_syslog_priority | LOG_INFO |
+-----------------------------+--------------------------+
18 rows in set (0.00 sec)

I’m monitoring the audit.log file and observe that when I execute the set global audit_log_flush=1; command, the file is not closed and nor is a new one created, it’s still writing to the original file. Here’s an example of the output from ls -lh:

total 1.2M
-rw-r----- 1 mysql mysql 1.2M Mar 25 17:02 audit.log

as for the inotifywatch utility this is not found on my system. It doesn’t appear in the /usr/bin/ directory.

bash-5.1$ inotifywatch
bash: inotifywatch: command not found

Does the audit_log_flush command have any dependencies on external utilities to complete its action? I’m wondering if that might be why I’m not seeing the expected file events.

Also for more context when interacting with the bash terminal I have mysql user access not root. so I can’t install utilities, this is the base image I get from docker essentially.

when interacting with mysql server e.g. mysql -u root -p I am using root user so I have the correct permissions there when executing the set global audit_log_flush=1; command.

Thanks again for your help!

7

Did you move the file? Otherwise it will close() and open() the same file again.
I move the current audit.log
$ mv audit.log audit.log.old
I confirm it does not exist anymore.

$ ls -la audit.log
ls: cannot access 'audit.log': No such file or directory

I flush the audit.log and check that the new file exists now.

$ ../use -u root -pmsandbox -e 'set global audit_log_flush=1;' && ls -la audit.log
mysql: [Warning] Using a password on the command line interface can be insecure.
-rw-r-----. 1 matias.rollan percona 0 Mar 25 19:25 audit.log

I am using 8.0.41 in this test.

$ ../use -e "show global variables like 'audit_log%'; select @@version;"
+-----------------------------+---------------+
| Variable_name               | Value         |
+-----------------------------+---------------+
| audit_log_buffer_size       | 1048576       |
| audit_log_exclude_accounts  |               |
| audit_log_exclude_commands  |               |
| audit_log_exclude_databases |               |
| audit_log_file              | ./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    | 0             |
| audit_log_rotations         | 10            |
| audit_log_strategy          | ASYNCHRONOUS  |
| audit_log_syslog_facility   | LOG_USER      |
| audit_log_syslog_ident      | percona-audit |
| audit_log_syslog_priority   | LOG_INFO      |
+-----------------------------+---------------+
+-----------+
| @@version |
+-----------+
| 8.0.41-32 |
+-----------+