Codership first released a version of MySQL 5.7 with the audit log plugin back when Galera Cluster for MySQL 5.7.30 was released back in June 2020. More recently, we also added the audit log plugin to Galera Cluster for MySQL 5.6.51 in April 2021, but I guess the most important was that we also started including it in Galera Cluster for MySQL 8.0.21 too. We also started supporting it across various distributions, including Debian. For today’s exercise, we will use Galera Cluster for MySQL 8.0.23 on CentOS 7 (compatible with Red Hat Enterprise Linux 7).
Why do you want to use the audit plugin? For security and compliance, to inspect every transaction that your database performs. The audit plugin works for all kinds of transactions (DDL/DML), and will help with your compliance requirements (be it PCI DSS, GDPR, HIPAA). We have enabled the Percona Audit plugin within Galera Cluster for MySQL.
Remember to install a Galera Cluster as you would normally, and then check to see if the audit plugin is loaded (by default, it is not, but you can verify it by the following):
select * from information_schema.plugins where plugin_name like '%audit%';
Find your plugin directory by doing:
show variables like 'plugin%';
You should see output similar to:
mysql> show variables like 'plugin%';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| plugin_dir | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.01 sec)
Then confirm the audit log plugin exists at: /usr/lib64/mysql/plugin/audit_log.so
Now you can install it just by executing:
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
Check to see if the plugin is now active:
mysql> select * from information_schema.plugins where plugin_name like '%audit%'
\G
*************************** 1. row ***************************
PLUGIN_NAME: audit_log
PLUGIN_VERSION: 0.2
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUDIT
PLUGIN_TYPE_VERSION: 4.1
PLUGIN_LIBRARY: audit_log.so
PLUGIN_LIBRARY_VERSION: 1.10
PLUGIN_AUTHOR: Percona LLC and/or its affiliates.
PLUGIN_DESCRIPTION: Audit log
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
1 row in set (0.00 sec)
And now, you can see what the variables look like:
mysql> 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 | audit.log | | audit_log_flush | OFF | | audit_log_format | OLD | | 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 | 0 | | 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)
Let us unpack what the above means. For one, the location of the audit log is: /var/lib/mysql/audit.log. It is read/write capable by the mysql user and read capable by the mysql group, and no one else. In your Galera Cluster, you will notice that you will have it enabled, on all nodes, the moment you turn it on, on any node. The audit.log will differ across all your nodes though – since the audit log is for each individual node. There will of course be minimal overhead on all the nodes while the audit log plugin is enabled.
We document every parameter and variable at the Audit Log Plugin portion of our documentation, but it is worth noting that the audit_log_strategy is set to ASYNCHRONOUS which means it is logged to a memory buffer and no messages are dropped if the buffer is full; this can be changed, but you might want to avoid SYNCHRONOUS if you are worried about performance! You can also change the audit.log location (in fact, you can choose to change audit_log_handler to go straight to syslog instead of a file. You have options to change the audit_log_format which by default is set to OLD (this is the XML format that MySQL Enterprise uses), but you can switch this to JSON for consumption by other tools, too.
A sensible configuration for the audit log in your my.cnf might look like the following:
audit_log_policy=ALL
audit_log_format=JSON
audit_log_file=/var/lib/mysql/audit.log
audit_log_rotate_on_size=1024M
audit_log_rotations=10
An example of a user connecting to the database is excerpted below in the OLD (XML) format:
<AUDIT_RECORD
NAME="Connect"
RECORD="2_2021-06-29T05:58:28"
TIMESTAMP="2021-06-29T06:44:53Z"
CONNECTION_ID="12"
STATUS="0"
USER="root"
PRIV_USER="root"
OS_LOGIN=""
PROXY_USER=""
HOST="localhost"
IP=""
DB=""
/>
audit_log_format is not a dynamic variable, so will require a server restart. When doing this in a Galera Cluster, be mindful to do this node by node, and not all together (you do not want to reach a situation where you will have to re-bootstrap the entire cluster). Note that until all the servers have the same configuration as above, the old log file will still be there, so it is possible to have a cluster running with audit_log_format with OLD and JSON (as stated, this is node based, after all).
An example of an audit log entry that is in JSON:
{“audit_record”:{“name”:“Connect”,“record”:“3580_2021-06-29T07:05:31”,“timestamp”:“2021-06-29T07:05:44Z”,“connection_id”:“12”,“status”:0,“user”:“root”,“priv_user”:“root”,“os_login”:””,“proxy_user”:””,“host”:“localhost”,“ip”:””,“db”:””}}
JSON audit logs can then be sent to the ELK stack, and here is an example of how to do it: Finding the Unknowns in Database Audit Logs.
We hope you enjoy using the audit log plugin that exists in all versions of Galera Cluster for MySQL 5.6/5.7/8.0, which we have also included on the distributions that we support.