{"id":3015,"date":"2018-03-18T19:21:04","date_gmt":"2018-03-18T15:51:04","guid":{"rendered":"http:\/\/www.serveridol.com\/?p=3031"},"modified":"2018-03-18T19:21:04","modified_gmt":"2018-03-18T15:51:04","slug":"how-do-enable-the-mysql-audit-using-mariadb-audit-plugin","status":"publish","type":"post","link":"https:\/\/afaghhosting.net\/blog\/how-do-enable-the-mysql-audit-using-mariadb-audit-plugin\/","title":{"rendered":"How Do enable the MySQL audit using MariaDB Audit Plugin"},"content":{"rendered":"

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.<\/p>\n

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.<\/p>\n

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,<\/p>\n

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 \u2018failed statements<\/strong>\u2018 then we could operate and work with equivalent sql statements on MySQL 5.7. FAILED denotes the connection error and 1045 is error code.<\/p>\n

Download the MariaDB plugins from https:\/\/downloads.mariadb.com\/Audit-Plugin\/MariaDB-Audit-Plugin\/<\/strong> link which is not been published.<\/p>\n

Install MariaDB-Audit-Plugin<\/strong><\/p>\n

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

Next you need to find the MySQL plugin location using the below command.<\/p>\n

1. Install Plugins<\/strong><\/p>\n

\n
mysql> SHOW GLOBAL VARIABLES LIKE ‘plugi%’;
\n+—————+————————–+
\n| Variable_name | Value \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n+—————+————————–+
\n| plugin_dir \u00a0 \u00a0| \/usr\/lib64\/mysql\/plugin\/ |
\n+—————+————————–+
\n1 row in set (0.00 sec)<\/div>\n<\/div>\n

Then copy the plugin file to plugin_install location.<\/p>\n

\n
\u00a0root@Db01#cp server_audit.so \/usr\/local\/mysql\/lib\/plugin\/<\/div>\n<\/div>\n

2. Active the plugins at runtime <\/strong><\/p>\n

\n
install plugin server_audit SONAME “server_audit.so”;<\/div>\n<\/div>\n

3. How to test the plugin installation<\/strong><\/p>\n

\n
-4.1# mysql -e “show plugins;” | grep “SERVER”
\nSERVER_AUDIT \u00a0 \u00a0ACTIVE \u00a0AUDIT \u00a0 server_audit.so GPL
\n-bash-4.1#
\nmysql> SHOW \u00a0VARIABLES LIKE ‘SERVER%’;
\n+——————————-+————————————–+
\n| Variable_name \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | Value \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n+——————————-+————————————–+
\n| server_audit_events \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| server_audit_excl_users \u00a0 \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| server_audit_file_path \u00a0 \u00a0 \u00a0 \u00a0| server_audit.log \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |
\n| server_audit_file_rotate_now \u00a0| OFF \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| server_audit_file_rotate_size | 1000000 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| server_audit_file_rotations \u00a0 | 9 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| server_audit_incl_users \u00a0 \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| server_audit_loc_info \u00a0 \u00a0 \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| server_audit_logging \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| OFF \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| server_audit_mode \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| server_audit_output_type \u00a0 \u00a0 \u00a0| file \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |
\n| server_audit_query_log_limit \u00a0| 1024 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |
\n| server_audit_syslog_facility \u00a0| LOG_USER \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |
\n| server_audit_syslog_ident \u00a0 \u00a0 | mysql-server_auditing \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| server_audit_syslog_info \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| server_audit_syslog_priority \u00a0| LOG_INFO \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |
\n| server_id \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| server_id_bits \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| 32 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |
\n| server_uuid \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 045804e0-eec9-11e6-9146-90e2ba073ca0 |
\n+——————————-+————————————–+
\n19 rows in set (0.00 sec)
\nmysql><\/div>\n<\/div>\n

4. How to activate the audit plugin<\/strong><\/p>\n

\n
mysql> set GLOBAL server_audit_logging=On;
\nQuery OK, 0 rows affected (0.00 sec)
\nmysql><\/div>\n<\/div>\n

Happy<\/p>\n","protected":false},"excerpt":{"rendered":"

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 …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[34,36],"tags":[],"class_list":["post-3015","post","type-post","status-publish","format-standard","hentry","category-server","category-36"],"_links":{"self":[{"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/posts\/3015","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/comments?post=3015"}],"version-history":[{"count":0,"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/posts\/3015\/revisions"}],"wp:attachment":[{"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/media?parent=3015"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/categories?post=3015"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/afaghhosting.net\/blog\/wp-json\/wp\/v2\/tags?post=3015"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}