MySQL之MariaDB启用审计插件配置详解
对于MySQL Percona MariaDB三家都有自己的审计插件，但是呢，MySQL的审计插件是只有企业版才有的，同时也有很多第三方的的MySQL的审计插件，而Percona和MariaDB都是GPL的审计插件
先来看看MariaDB的审计插件使用，启用插件和调整参数
[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, 2016, 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='101023161';
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='101023161';"
[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
##时间，节点，用户，来源，事件类型，库，语句，影响行数
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]#
再来试试第三方审计插件在MariaDB上用法，这里需要注意你的MariaDB版本，和MCAFEE MYSQL AUDIT的版本，版本不支持是不行的，我开始就遇到了这样的错。
[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 'plugin_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';"
