In this blog i will show how to install and use the percona audit plugin.
Percona Audit Log Plugin provides monitoring and logging of connection and query activity that were performed on specific server.
All the information can be stored in the XML log file,CSV,JSON formats where each event will have its NAME field, its own unique RECORD_ID field and a TIMESTAMP field and information about query.
Along with percona server audit plugin is available , but it is not installed by default. We need to install the plugin to enable it.
We can check with below query if audit log is enabled or not.
To install the plugin run the below query on mysql command line.
Before executing the above command it is a good approach to add the audit log related configuration on the mysql configuration file (my.cnf). Adding these variables will not make you to restart the mysql service or else you will require a mysql restart for the audit log to work.
Below is the entry of audit log file , how it looks.
Now we have enabled the audit log for our mysql database.
IN percona audit log there are variables which can control what type of queries to log and not to log.
If this variable is set, then audit_log_exclude_commands must be unset, and vice versa.
If this variable is set, then audit_log_include_commands must be unset, and vice versa.
Either we can use one variable in our audit log.
For example if you want to log all the DML queries expect SELECT your audit_log_include_commands variable should look like below.
The above will make sure to log only the insert, delete and updates.
You can set it globally too
I will be executing some insert, delete update and select to check what is being logged in the log file.
From the above logs you can see i have executed select queries too, but they are not logged. In this way you can restrict your audit log file to capture the required events.
Why audit log?
Audit logs are useful to monitor the happenings inside the database server. It is location where to keep the track of who did what to the DB. It captures the user from which host he connected at what time which query was executed.Percona Audit Log Plugin provides monitoring and logging of connection and query activity that were performed on specific server.
All the information can be stored in the XML log file,CSV,JSON formats where each event will have its NAME field, its own unique RECORD_ID field and a TIMESTAMP field and information about query.
Installation
We can check with below query if audit log is enabled or not.
To install the plugin run the below query on mysql command line.
Before executing the above command it is a good approach to add the audit log related configuration on the mysql configuration file (my.cnf). Adding these variables will not make you to restart the mysql service or else you will require a mysql restart for the audit log to work.
plugin-load
it is the socket file for audit logaudit_log_file
To specify the filename that’s going to store the audit logaudit_log_format
To specify the audit log format. The audit log plugin supports four log formats: OLD, NEW, JSON, and CSVaudit_log_policy
To specify which events should be logged. Values are: ALL, LOGINS, QUERIS, NONEaudit_log_handler
To configure where the audit log will be written ie, syslog or normal file
After adding the above variables you can run the command to install audit log.
Below is the entry of audit log file , how it looks.
Now we have enabled the audit log for our mysql database.
IN percona audit log there are variables which can control what type of queries to log and not to log.
audit_log_include_commands
Used to specify the list of commands for which Filtering by SQL command type is applied. The value can be NULL or comma separated list of commands.If this variable is set, then audit_log_exclude_commands must be unset, and vice versa.
audit_log_exclude_commands
Used to specify the list of commands for which Filtering by SQL command type is applied to exclude them ie, those kind of sql queries wont be logged. The value can be NULL or comma separated list of commands.If this variable is set, then audit_log_include_commands must be unset, and vice versa.
Either we can use one variable in our audit log.
For example if you want to log all the DML queries expect SELECT your audit_log_include_commands variable should look like below.
The above will make sure to log only the insert, delete and updates.
You can set it globally too
I will be executing some insert, delete update and select to check what is being logged in the log file.
From the above logs you can see i have executed select queries too, but they are not logged. In this way you can restrict your audit log file to capture the required events.
