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.
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
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)
+---------------+--------------------------+
| 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>
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>
Query OK, 0 rows affected (0.00 sec)
mysql>