MySQL之MariaDB啟用審計插件配置詳解

為了讓我們運維朋友不背一些不要的黑鍋，今天我們就一起來看一篇關於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';"