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#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
+—————+————————–+
| 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.
2. Active the plugins at runtime
3. How to test the plugin installation
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
Query OK, 0 rows affected (0.00 sec)
mysql>
Happy