How to Enable audit_logs on a Specific MySQLv2 service instance

Products

• VMware Tanzu MySQL

Issue/Introduction

Enabling audit logs will help you get more information on events such as client connect, the client disconnects and executed SQL statements that is being processed by your service instance.  Audit log helps in investigating performance issues only on a specific service instance.

Environment

Resolution

 Here are the steps to enable audit_log with bosh:

Important note: These steps will cause the mysql server job on the service instance to restart. The service instance may be unavailable during bosh deploy.

1. From the Operations Manager VM, run this command and make sure to replace the service instance name with the appropriate name

bosh deploy -d service-instance_$(cf service <your service instance name> --guid) <(bosh -d service-instance_$(cf service <your service instance name> --guid) manifest) -o <(echo '[{ "type": "replace", "path": "/properties/audit_log?/enabled?", "value": true }]') --no-redact

2. After executing this command, this will prompt you the changes that you made. See the example below. Please  make sure there is no other changes introduced before confirming:

properties: 

audit_log: 
- enabled: false
+ enabled: true

Continue? [yN]: Y

After deployment finishes, it will have the audit_log enabled. Audit logs are located on the MySQL server VMs at  /var/vcap/sys/log/mysql/mysql-audit-log.