From this blog post, you’ll learn how to enable an audit log plugin on the Community version of MySQL 8 Server.
There are some options available for MySQL Server 5.7 Community version, but they don’t work correctly with MySQL 8. A significant change in the MySQL API broke the compatibility.
If you have MySQL 5.7 Community version running on your database farm and would like to enable an audit log, you can leverage the options listed below:
However, if you have MySQL 8 Community version on your farm and need to enable an audit log plugin, the above-listed plugins won’t be compatible.
The development team of Amazon RDS for MySQL has forked the MariaDB Audit Log Plugin and ensured compatibility with MySQL 8 API to use it on their RDS for MySQL databases family. Recently, they have made the repository with the code of this project open on GitHub.
The audit plugin for MySQL needs to be compiled with the MySQL Server Community source code to generate the server_audit.so file. The MySQL Server compiling process is well-explained on compiling mysql demystifying from Vinicius Grippa.
I created a fork from the AWS repository and fixed some issues in the plugin found while working with it. I’ve also uploaded the server_audit.so file for test purposes.
- Find the plugin and the file in the Vettabase repository: https://github.com/Vettabase/audit-plugin-for-mysql
To be able to use the audit plugin, you’ll need to paste the server_audit.so file in the plugin_dir of the MySQL database server.
- Get the path for
mysql> select @@plugin_dir; +————————–-----------------+ | @@plugin_dir | +————————–-----------------+ | /usr/lib64/mysql/plugin/ | +————————–-----------------+ 1 row in set (0.00 sec)
- Paste the
mysql>! ls -lh /usr/lib64/mysql/plugin/ total 8.5M -rw-r–r–. 1 root root 597K Jul 26 18:47 server_audit.so ...
mysql>set global server_audit_logging = ON; Query OK, 0 rows affected (0.00 sec)
Enable query events
mysql> set global server_audit_events = ‘QUERY’; Query OK, 0 rows affected (0.00 sec)
Audit log file
mysql> ! ls -lh /var/lib/mysql total 188M -rw-r-----. 1 mysql mysql 1.7K Jul 26 18:50 server_audit.log
mysql> ! cat /var/lib/mysql/server_audit.log 20220726 18:48:35,localhost.localdomain,root,localhost,10,3,QUERY,,’set global server_audit_logging = ON’,0,, 20220726 18:48:56,localhost.localdomain,root,localhost,10,4,QUERY,,’set global server_audit_events = ’QUERY”,0,, 20220726 18:49:02,localhost.localdomain,root,localhost,10,5,QUERY,,’create database test’,0,, 20220726 18:49:04,localhost.localdomain,root,localhost,10,6,QUERY,,’SELECT DATABASE()’,0,, 20220726 18:49:04,localhost.localdomain,root,localhost,10,8,QUERY,,’show databases’,0,, 20220726 18:49:04,localhost.localdomain,root,localhost,10,9,QUERY,,’show tables’,0,, 20220726 18:49:32,localhost.localdomain,root,localhost,10,10,QUERY,,’create table example_table (id int unsigned primary key auto_increment)’,0,, 20220726 18:49:43,localhost.localdomain,root,localhost,10,11,QUERY,,’insert into example_table(null)’,1064,, 20220726 18:49:48,localhost.localdomain,root,localhost,10,12,QUERY,,’insert into sidnei values (null)’,0,, 20220726 18:49:57,localhost.localdomain,root,localhost,10,18,QUERY,,’select * from example_table’,0,,
If are you using MySQL 8 Community version and need to enable the audit log, the plugin from the AWS development team can be a good option. However, you should bear in mind that no AWS support is available for this plugin — they make it clear in their
README.md. In my opinion, it can be useful in cases where the database server is not so critical. If some bug happens in production in a very important database server the bug fix can take time. If you need to use this feature I recommend migrating to MariaDB Server because you can use a very mature plugin with the support of the MariaDB community.