How to enable Server Activity log for running MySQL V2 Service Instances in MySQL for VMware Tanzu

Products

• VMware Tanzu SQL

Issue/Introduction

This article will show you how to enable Server Activity Logging for existing MySQL V2 Service Instances. Server Activity Logging in MySQL for VMware Tanzu is used to record who connects to the servers and what queries are processed. 

For information how to enable different types of monitoring and logging available in the MySQL service, refer to the following documentation: https://docs.pivotal.io/p-mysql/2-3/install-config.html#monitoring

Note: Enabling Server Activity Logging requires you to Apply Changes and run the errand "Upgrade all On-demand MySQL Service Instances". Like a single-node service instance, this will cause downtime.

Environment

Product Version: 2.4
OS: Linux

Resolution

The following procedure is about how to manually enable Server Activity Logging.  

1. Login to the MySQL V2 Service Instances.

bosh -d <deployment name> ssh <instance name>

2. Connect to the MySQL DB.

admin$ mysql --defaults-file=/var/vcap/jobs/mysql/config/mylogin.cnf

3. Install audit log plugin which shipped with Percona Server.

mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';

4. Verify the installation of the plugin.

SHOW PLUGINS;

5. Audit log should be listed in the output:

+--------------------------------+----------+--------------------+--------------+---------+
| Name                           | Status   | Type               | Library      | License |
+--------------------------------+----------+--------------------+--------------+---------+
...
| audit_log                      | ACTIVE   | AUDIT              | audit_log.so | GPL     |
+--------------------------------+----------+--------------------+--------------+---------+

5. By default, the audit log doesn't have any rotation rules. We need to manually set it.

The best practice from the Percona website is the following:

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

We can use:

mysql> set global audit_log_rotations=10;

It is important to pay attention to two things:

• The file size is set by Bytes, which means the 1024 MB needs to be converted to 1073741824.
• We need to set the variables after install the plugin.

Installation instructions can be also found here:

• https://www.percona.com/doc/percona-server/5.7/management/audit_log_plugin.html

Best practices can be found here:

• https://www.percona.com/blog/2015/09/10/percona-server-audit-log-plugin-best-practices/