MySQL MariaDB enable audit plug-in configuration details

MySQL Percona MariaDB has its own audit plug-ins. However, MySQL audit plug-ins are only available in the Enterprise edition, and there are also many third-party MySQL audit plug-ins, percona and MariaDB are both GPL auditing plug-ins.
Let's take a look at the use of MariaDB's audit plug-ins, enable plug-ins and adjust parameters.

[Root @ DS-VM-Node127/data/mariadb] # mysql-uroot-pZjUxZGI2ZjcwMmEy
Welcome to the MariaDB monitor. Commands end with; or \ g.
Your MariaDB connection id is 24
Server version: 10.0.25-MariaDB-wsrep MariaDB Server, wsrep_25.13.raf7f02e
 
Copyright (c) 2000,201 6, Oracle, MariaDB Corporation AB and others.
 
Type 'help; 'or' \ H' for help. Type' \ C' to clear the current input statement.
 
MariaDB [(none)]> show variables like '% audit % ';
Empty set (0.00 sec)
 
MariaDB [(none)]> install plugin server_audit SONAME 'server _ audit ';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> set global server_audit_file_rotate_size = 1024*1024*1024;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> set global server_audit_events = 'query, table ';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> set global server_audit_excl_users = '123 ';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> set global server_audit_file_rotate_now = on;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> set global server_audit_logging = 'on ';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> show variables like 'server _ audit % ';
+ ------------------------------- + ----------------------- +
| Variable_name | Value |
+ ------------------------------- + ----------------------- +
| Server_audit_events | QUERY, TABLE |
| Server_audit_excl_users' | 101023161 |
| Server_audit_file_path | server_audit.log |
| Server_audit_file_rotate_now | OFF |
| Server_audit_file_rotate_size | 1073741824 |
| Server_audit_file_rotations | 9 |
| Server_audit_incl_users |
| Server_audit_loc_info |
| Server_audit_logging | ON |
| Server_audit_mode | 0 |
| 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 |
+ ------------------------------- + ----------------------- +
16 rows in set (0.00 sec)
 
MariaDB [(none)]> \ q

[Root @ DS-VM-Node127/data/mariadb] # mysql-uroot-pZjUxZGI2ZjcwMmEy-e "show variables like '% audit % ';"
[Root @ DS-VM-Node127/data/mariadb] # mysql-uroot-pZjUxZGI2ZjcwMmEy-e "install plugin server_audit SONAME 'server _ audit ';"
[Root @ DS-VM-Node127/data/mariadb] # mysql-uroot-pZjUxZGI2ZjcwMmEy-e "set global server_audit_file_rotate_size = 1024*1024*1024 ;"
[Root @ DS-VM-Node127/data/mariadb] # mysql-uroot-pZjUxZGI2ZjcwMmEy-e "set global server_audit_events = 'query, table ';"
[Root @ DS-VM-Node127/data/mariadb] # mysql-uroot-pZjUxZGI2ZjcwMmEy-e "set global server_audit_excl_users = '000000 ';"
[Root @ DS-VM-Node127/data/mariadb] # mysql-uroot-pZjUxZGI2ZjcwMmEy-e "set global server_audit_file_rotate_now = ON ;"
[Root @ DS-VM-Node127/data/mariadb] # mysql-uroot-pZjUxZGI2ZjcwMmEy-e "set global server_audit_logging = 'on ';"
[Root @ DS-VM-Node127/data/mariadb] # mysql-uroot-pZjUxZGI2ZjcwMmEy-e "show variables like 'server _ audit % ';"

[Root @ DS-VM-Node127/data/mariadb] # tail-f/data/mariadb/server_audit.log
20160825 19:05:03, DS-VM-Node127.cluster.com, fpmmm, 127.0.0.1, 36,434, QUERY, mysql, 'Select GROUP_CONCAT (CONCAT (user, "@", host) SEPARATOR ",") AS user FROM mysql. user WHERE user! = "Root" AND grant_priv = "Y" ', 0
# Time, node, user, source, event type, database, statement, and number of affected rows
20160825 19:05:03, DS-VM-Node127.cluster.com, fpmmm, 127.0.0.1, 36,435, READ, mysql, user,
20160825 19:05:03, DS-VM-Node127.cluster.com, fpmmm, 127.0.0.1, 36,435, QUERY, mysql, 'Select GROUP_CONCAT (CONCAT (user, "@", host) SEPARATOR ",") AS user FROM mysql. user WHERE user! = "Root" AND super_priv = "Y" ', 0
20160825 19:05:03, DS-VM-Node127.cluster.com, fpmmm, 127.0.0.1, 36,436, READ, mysql, user,
20160825 19:05:03, DS-VM-Node127.cluster.com, fpmmm, 127.0.0.1, 36,436, QUERY, mysql, 'Select GROUP_CONCAT (CONCAT (user, "@", host) SEPARATOR ",") AS user FROM mysql. user WHERE user! = "Root" AND create_user_priv = "Y" ', 0
20160825 19:05:03, DS-VM-Node127.cluster.com, fpmmm, 127.0.0.1, 36,437, QUERY, mysql, 'set SESSION wsrep_causal_reads = 0', 0
20160825 19:05:03, DS-VM-Node127.cluster.com, fpmmm, 127.0.0.1, 36,438, QUERY, mysql, 'show global variables ', 0
20160825 19:05:03, DS-VM-Node127.cluster.com, fpmmm, 127.0.0.1, 36,439, QUERY, mysql, 'show /*! 50000 GLOBAL */status', 0
20160825 19:05:04, DS-VM-Node127.cluster.com, fpmmm, 127.0.0.1, 36,440, QUERY, mysql, 'show global variables ', 0
20160825 19:05:04, DS-VM-Node127.cluster.com, fpmmm, 127.0.0.1, 36,441, QUERY, mysql, 'show /*! 50000 GLOBAL */status', 0
^ C
[Root @ DS-VM-Node127/data/mariadb] #

Try the third-party AUDIT plug-in on MariaDB. Pay attention to your MariaDB version and the mcafee mysql audit version. This version is not supported, at the beginning, I encountered such an error.

 

[Root @ iZ62rkcotqvZ ~] # Https://dl.bintray.com/mcafee/mysql-audit-plugin/:audit-plugin-mariadb-10.0-1.1.0-625-linux-x86_64.zip
[Root @ iZ62rx97xj1Z ~] # Unzip audit-plugin-mariadb-10.0-1.1.0-625-linux-x86_64.zip
Archive: audit-plugin-mariadb-10.0-1.1.0-625-linux-x86_64.zip
Creating: audit-plugin-mariadb-10.0-1.1.0-625/
Creating: audit-plugin-mariadb-10.0-1.1.0-625/lib/
Inflating: audit-plugin-mariadb-10.0-1.1.0-625/lib/libaudit_plugin.so
Inflating: audit-plugin-mariadb-10.0-1.1.0-625/COPYING
Inflating: audit-plugin-mariadb-10.0-1.1.0-625/THIRDPARTY.txt
Inflating: audit-plugin-mariadb-10.0-1.1.0-625/README.txt
[Root @ iZ62rx97xj1Z ~] # Cd audit-plugin-mariadb-10.0-1.1.0-625/lib/
[Root @ iZ62rx97xj1Z ~ /Audit-plugin-mariadb-10.0-1.1.0-625/lib] # mysql-uroot-p-e "show global variables like 'In in _ dir ';"
+ --------------- + -------------------------------- +
| Variable_name | Value |
+ --------------- + -------------------------------- +
| Plugin_dir |/usr/local/mariadb/lib/plugin/|
+ --------------- + -------------------------------- +
[Root @ iZ62rx97xj1Z ~ /Audit-plugin-mariadb-10.0-1.1.0-625/lib] # cp libaudit_plugin.so/usr/local/mariadb/lib/plugin/
[Root @ iZ62rx97xj1Z ~ /Audit-plugin-mariadb-10.0-1.1.0-625/lib] # mysql-uroot-p-e "install plugin audit soname 'libaudit _ plugin. So ';"