Activities

February 2017
M T W T F S S
« Jan   Apr »
 12345
6789101112
13141516171819
20212223242526
2728  

How Do enable the MySQL audit using MariaDB Audit Plugin

We are using Oracle MySQL community 5.7 version which does not have capability of auditing user activities. There will be some option to audit the MySQL statements by enabling General log ( for whole sql activity) and Slow log ( to identify the culprit sql which slow down the system.

Neither of above does not help top give you a complete solution if somebody change the table values with or without a proper approval like accidental data deletion or query execution against a wrong window.

My Best advise is, ever ever use a unique database name or user name on other environments to avoid accidental disaster or data loss. Our team has already found McAfee MySQL Audit Plugin become useful to track down the users activity. See the how to link,

https://github.com/mcafee/mysql-audit/wiki/Installation. But this plugin does not have certain feature what we expect like readability of output file and identifying fail statements. Our prime importance is to identify the broken sql statements prior to the up-gradation from MySQL 5.5 to 5.7. Once we able to record all the ‘failed statements‘ then we could operate and work with equivalent sql statements on MySQL 5.7. FAILED denotes the connection error and 1045 is error code.

Download the MariaDB plugins from https://downloads.mariadb.com/Audit-Plugin/MariaDB-Audit-Plugin/ link which is not been published.

Install MariaDB-Audit-Plugin

root@Db01#wget https://downloads.mariadb.com/Audit-Plugin/MariaDB-Audit-Plugin/server_audit-1.4.0.tar.gz
 root@Db01#tar -zxvf server_audit-1.4.0.tar.gz
 root@Db01#cd server_audit-1.4.0
 root@Db01# cd linux-x86-64

Next you need to find the MySQL plugin location using the below command.

1. Install Plugins

mysql> SHOW GLOBAL VARIABLES LIKE 'plugi%';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)

Then copy the plugin file to plugin_install location.

 root@Db01#cp server_audit.so /usr/local/mysql/lib/plugin/

2. Active the plugins at runtime

install plugin server_audit SONAME "server_audit.so";

3. How to test the plugin installation

-4.1# mysql -e "show plugins;" | grep "SERVER"
SERVER_AUDIT    ACTIVE  AUDIT   server_audit.so GPL
-bash-4.1#
mysql> SHOW  VARIABLES LIKE 'SERVER%';
+-------------------------------+--------------------------------------+
| Variable_name                 | Value                                |
+-------------------------------+--------------------------------------+
| server_audit_events           |                                      |
| server_audit_excl_users       |                                      |
| server_audit_file_path        | server_audit.log                     |
| server_audit_file_rotate_now  | OFF                                  |
| server_audit_file_rotate_size | 1000000                              |
| server_audit_file_rotations   | 9                                    |
| server_audit_incl_users       |                                      |
| server_audit_loc_info         |                                      |
| server_audit_logging          | OFF                                  |
| server_audit_mode             | 1                                    |
| server_audit_output_type      | file                                 |
| server_audit_query_log_limit  | 1024                                 |
| server_audit_syslog_facility  | LOG_USER                             |
| server_audit_syslog_ident     | mysql-server_auditing                |
| server_audit_syslog_info      |                                      |
| server_audit_syslog_priority  | LOG_INFO                             |
| server_id                     | 0                                    |
| server_id_bits                | 32                                   |
| server_uuid                   | 045804e0-eec9-11e6-9146-90e2ba073ca0 |
+-------------------------------+--------------------------------------+
19 rows in set (0.00 sec)
mysql>

4. How to activate the audit plugin

mysql> set GLOBAL server_audit_logging=On;
Query OK, 0 rows affected (0.00 sec)
mysql>

Happy

2 comments to How Do enable the MySQL audit using MariaDB Audit Plugin

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>