Schedule Meeting

a

How to set up AWS Audit Log plugin for MySQL 8 Community

by | Aug 3, 2022 | MySQL | 1 comment

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.

Aldo Junior: How to set up AWS Audit Log plugin for MySQL 8

Motivation

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.

Repository

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. 

Configuration

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.

  1. Get the path for plugin_dir:
mysql> select @@plugin_dir;
+————————–-----------------+
| @@plugin_dir             |
+————————–-----------------+
| /usr/lib64/mysql/plugin/ |
+————————–-----------------+

1 row in set (0.00 sec)
  1. Paste the server_audit.so there:
mysql>! ls -lh /usr/lib64/mysql/plugin/ total 8.5M -rw-r–r–. 1 root root 597K Jul 26 18:47 server_audit.so
...

Enable plugin

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

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,,

To get more details on the plugin configuration, read the AWS RDS Plugin documentation.

Conclusions

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.

Useful links

All content in this blog is distributed under the Creative Commons license. You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions: https://creativecommons.org/licenses/by/4.0/

[/et_pb_column]
About Aldo Junior
Aldo is a MySQL and MariaDB consultant at Vettabase. He has a passion for open source database systems. He likes to play guitar and play with your dog in his free time.

Recent Posts

Hints to optimise queries with a LIKE comparison

Hints to optimise queries with a LIKE comparison

In SQL, using the LIKE operator is a powerful way to find strings that match a certain pattern. It's suitable for most use cases, thanks to its two jolly characters: _ means any one character. % means any sequence of zero or more characters. However, many queries out...

Services

1 Comment

Submit a Comment

Your email address will not be published. Required fields are marked *

How to compose strings in MariaDB

How to compose strings in MariaDB

MariaDB supports several ways to compose strings dynamically. Some of them might be well-known, others aren’t familiar to you. This article covers all the ways to compose strings.

read more

How to compose strings in MariaDB

How to compose strings in MariaDB

MariaDB supports several ways to compose strings dynamically. Some of them might be well-known, others aren’t familiar to you. This article covers all the ways to compose strings.

read more