Auditing your database means tracking access and changes to your data and db objects. The Audit Log Plugin has been shipped with Percona Server since 5.5.37/5.6.17, for a little over 12 months. Prior to the Audit Log Plugin, you had to work in darker ways to achieve some incarnation of an audit trail.
We have seen attempts at creating audit trails using approaches such as ‘sniffing the wire’, init files, in-schema ‘on update’ fields, triggers, proxies and trying to parse the traditional logs of MySQL (slow, general, binary, error). All of these attempts miss a piece of the pie, i.e. if you’re sniffing tcp traffic you’ll miss local connections, parsing binary logs you’re missing any reads. Your reasons for audit logging might be down to compliance requirements (HIPAA, PCI DSS) or you may need a way to examine database activity or track the connections incoming.
Over the past months I’ve met many support requests with the answer ‘install an audit plugin’. These requests have been varied but they have ranged from; finding out if a user is still active and if the impact of decommissioning it, the frequency of specific queries and checking if a slave is being written to name but a few.
So then, lets look at installation. In general we desire installation of the Audit Plugin on an existing instance. We discussed in previous Percona Blog posts, the installation of the plugin is trivial but lets recap. Lets perform a couple of basic checks before we run the install command from the client. First, query MySQL for the location of the plugins directory;
mysql> show global variables like 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| plugin_dir | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)
Once that’s known we’ll check that the audit log plugin shared library is present;
[moore@randy ~]$ ls -l /usr/lib64/mysql/plugin/audit*
-rwxr-xr-x. 1 root root 42976 Jul 1 09:24 /usr/lib64/mysql/plugin/audit_log.so
Great, we are in good shape to move to the client and install;
mysql> install plugin audit_log soname 'audit_log.so';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.plugin;
+-------------------------------+--------------+
| name | dl |
+-------------------------------+--------------+
| audit_log | audit_log.so |
...
+-------------------------------+--------------+
8 rows in set (0.00 sec)
Voila! It’s that simple. So, what does that provide us? Well now thanks to our default variables we’ve got the following options set;
mysql> show global variables like 'audit%';
+---------------------------+---------------+
| Variable_name | Value |
+---------------------------+---------------+
| audit_log_buffer_size | 1048576 |
| audit_log_file | audit.log |
| audit_log_flush | OFF |
| audit_log_format | OLD |
| audit_log_handler | FILE |
| 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 |
+---------------------------+---------------+
12 rows in set (0.00 sec)
So what we can tell from that output is that our audit plugin is enabled, it’s logging out to the default location ({datadir}/audit.log) and we’re grabbing all events (ALL) on the server and sending the output in XML format (OLD). From the list of variables above we’ve only got one dynamic variable. This means to change the logfile location or the format we need to put these options into our my.cnf and restart the instance. Not very convenient. Personally, it’s my preference to store the audit.log file away from my datadir.
I also dislike the XML formats in favour of the JSON log format. It is also advised, especially on busier systems, to enable the rotation options, audit_log_rotate_on_size
and audit_log_rotations
so that you don’t end up filling your disk with a huge audit log. Restarting your production instances isn’t extremely convenient but you’ll be happy to learn there is another way.
Let’s rewind to before we installed the plugin. We had checked the existence of our plugin shared library and were itching to run the install command. Now we can open our my.cnf file and add our preferred options prior to installation. Whilst it’s far from a secret, not many will know that the in the plugin installation phase, MySQL will re-read the my.cnf file to check for configuration relevant to the plugin. So let’s add some variables here;
## Audit Logging ##
audit_log_policy=ALL
audit_log_format=JSON
audit_log_file=/var/log/mysql/audit.log
audit_log_rotate_on_size=1024M
audit_log_rotations=10
A quick review of the above. I intend to log all events in JSON format to the /var/log/mysql location. I will rotate each time the active log file hits 1G and this will circulate 10 files meaning I will not have more than 10G of audit logs on my filesystem.
Now with our predefined configuration in the my.cnf we can install the plugin from cold and begin with our preferred options;
mysql> show global variables like 'audit%';
Empty set (0.00 sec)
mysql> install plugin audit_log soname 'audit_log.so';
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'audit%';
+---------------------------+--------------------------+
| Variable_name | Value |
+---------------------------+--------------------------+
| audit_log_buffer_size | 1048576 |
| audit_log_file | /var/log/mysql/audit.log |
| audit_log_flush | OFF |
| audit_log_format | JSON |
| audit_log_handler | FILE |
| audit_log_policy | ALL |
| audit_log_rotate_on_size | 1073741824 |
| 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 |
+---------------------------+--------------------------+
12 rows in set (0.00 sec)
Something to remember; if you add these variables before installation of the plugin and you restart your instance or suffer a crash, your instance will not start.
[moore@randy ~]$ sudo systemctl restart mysql
[moore@randy ~]$ sudo egrep 'ERROR' /var/log/mysqld.log
2015-09-02 11:55:16 8794 [ERROR] /usr/sbin/mysqld: unknown variable 'audit_log_policy=ALL'
2015-09-02 11:55:16 8794 [ERROR] Aborting
When all up and running we can check that the content is finding it’s way to our log file by opening it up and taking a look. Our JSON output will store a new line of JSON per event, here’s an example:
{"audit_record":{"name":"Query","record":"1067824616_2015-09-02T10:04:26","timestamp":"2015-09-02T10:54:53 UTC","command_class":"show_status","connection_id":"6","status":0,"sqltext":"SHOW /*!50002 GLOBAL */ STATUS","user":"pct[pct] @ localhost [127.0.0.1]","host":"localhost","os_user":"","ip":"127.0.0.1"}}
compare that with the ‘OLD’ XML output format that spans multiple lines making parsing a more difficult task:
<AUDIT_RECORD
NAME="Query"
RECORD="2745742_2015-09-02T21:12:10"
TIMESTAMP="2015-09-02T21:12:22 UTC"
COMMAND_CLASS="show_status"
CONNECTION_ID="8"
STATUS="0"
SQLTEXT="SHOW /*!50002 GLOBAL */ STATUS"
USER="pct[pct] @ localhost [127.0.0.1]"
HOST="localhost"
OS_USER=""
IP="127.0.0.1"
/>
Cost
One of the common assumptions of invoking the Audit Plugin is that it’s going to take an almighty hit on load. Logging all connections, queries and admin statements…surely? Well not so true. I spent some time observing the impact to the resources on a humbly specc’d home server. A small machine running quad core Xeon, 32G of RAM and a Samsung PRO SSD with a 72k rpm disk for the logs. Here are a collection of the graphs to illustrate that the impact of turning on the Audit Logging in asynchronous mode, as you will see the results are encouragingly showing little impact on activation of full logging. In each image, audit logging was set off and subsequently on.



Summary
We can install the Percona Audit plugin with our preferred options on a running system without interrupting it by adding our variables to the my.cnf. By performing this prior to the installing the plugin gives us best practice options without needing to restart the instance for static variables to take effect. Due to the lightweight nature of the audit plugin you can add this new log file to track access and changes to the data without the performance hit of the slow or general log. The audit log is a great aid to debugging and can serve as a security measure and malpractice deterrent.
The post Percona Server audit log plugin best practices appeared first on MySQL Performance Blog.