Tuesday, 12 November 2013

MySQL Auditing

I got a task of auditing user activities for some sensitive database in one of our MySQL database servers. Auditing user activity was a tough task with earlier version of MySQL. We had to go through the slow query log or general log and find out our required data from these two files by scanning through lots of data. Which is obviously not a trivial task. But MySQL started supporting plugin API since MySQL 5.1 version and that changed the game. That leads to the arrival of MySQL AUDIT Plugin, which is a MySQL plugin from McAfee and this plugin provides audit capabilities for MySQL.

So in this example I will show you how to audit user activities like update,delete,drop,truncate for a particular database say okcl_sets_app. For this example I have used Ubuntu 12.04 with MySQL 5.5.24.

First I will show you how to install the plugin and then auditing some user activity without restarting the MySQL server.

Download the MySQL Audit plugin for your version of MySQL from the links provided in the page


We have to copy the plugin file into MySQL’s plugin directory. To find the location of the plugin directory we can use the following command:


The plugin file is available in the zip binary distribution. Extract the zip file

# unzip

The actual plugin file is inside of the lib folder of the extracted zip folder. Copy the plugin file ( into MySQL’s plugin directory.

# cp ./audit-plugin-mysql-5.5/lib/ /usr/lib/mysql/plugin

Once the plugin file is copied, we can install the plugin using the following command:


Note: The above command requires INSERT privilege for the mysql.plugin table.

The INSTALL PLUGIN command loads and initializes the plugin and makes the plugin available for use. So there is no need to restart the MySQL server.

We may also install this plugin by inserting the following line

in the [mysqld] section of MySQL configuration file. But this will require MySQL server restart to load the plugin.

To check whether the plugin has been installed and loaded successfully, we can use the SHOW PLUGINS command and check the line for AUDIT plugin.


We can find the version of our loaded audit plugin using the following command:


Our audit plugin is installed and loaded successfully, now we can see the default values for the configuration system variables of the audit plugin:


Audit plugin writes the auditing activities in JSON format. It supports writing auditing activities directly to a file, or to a unix socket.

Now I will enable JSON file auditing using the dynamic system variable.
audit_json_file: json log file Enable|Disable (1|0)


By default the plugin creates mysql-audit.json file inside MySQL datadir and writes audit trail to this file. We can change the file name and location by changing the audit_json_log_file system variable.

After enabling auditing, the plugin starts auditing all the user activities on the server, which will be large amount of data. We may restrict auditing data by specifying the commands that are to be audited and also we can specify the database/table that we need to audit.

As per my requirement I have to audit update,delete,drop,truncate for the database  okcl_sets_app

For that I will first specify the commands that are to be audited by changing the audit_record_cmds system variable.


Next I will specify the object(s) that I need to audit by changing the audit_record_objs system variables.


All done, we can check the audit settings whether everything is changed as per our requirement


Note: To make our audit configurations to persist across MySQL restart, add the required audit plugin system variables into the [mysqld] section of MySQL configuration file.

All the Audit Plugin’s system variables are available in this page:

Now I will update the table named TableName1 in okcl_sets_app database and lets see what we get in the Audit log file


Wow, wealth of information that we can collect transparently Smile .


Selva Sarashwathi said...

Thank you so much..

mohammed nizamuddin said...
This comment has been removed by the author.
mohammed nizamuddin said...

Thanks a lot !! i want to log various parameters such as user login , failed logine attempt . Just the way u had to log update,delete,drop, can i find such commands with their function? thanks in advance

Pranab Sharma said...

Hi Mohammed thanks for writing.
I have not tried, but you can try
mysql>set global audit_record_cmds='Connect,Failed Login';

Also you can check the MySQL login audit plugin (I have not tested this one)

mohammed nizamuddin said...

Many thanks for the promt reply mate ....
I have the plugin , just wanted to know where can I find different options for auditing .like in the Microsoft sql server studio there are quite a few auditing options , I wanted to know similar commands which can be used in the terminal

Post a Comment