MySQL之MariaDB启用审计插件之尽可能的不让运维背黑锅

评论
10,582 次

摘要

今天zabbix群一个同学说他们老大要他们做MySQL的详细记录监控，要有连接，操作的语句，我一想这不就是MySQL的审计么，于是就再次折腾了下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_rotations = 999;
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   | 999                   |
| 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

也可以通过写配置文件来实现

#####Mariadb - 审计
plugin_load = server_audit=server_audit.so #载入审计插件
server_audit = FORCE_PLUS_PERMANENT        #防止插件被卸载
server_audit_file_path = server_audit.log  #定义审计日志文件名
server_audit_file_rotate_now = OFF         #是否强制切割审计日志
#server_audit_file_rotate_size = 1073741824 #定义切割审计日志的文件大小1G
server_audit_file_rotate_size = 10485760 #定义切割审计日志的文件大小10M
#server_audit_file_rotations = 0            #定义审计日志的轮询个数，0为不轮询
server_audit_file_rotations = 1000         #定义审计日志的轮询个数，0为不轮询
server_audit_logging = ON                  #开启日志记录
server_audit_output_type = file            #指定日志输出类型，可为SYSLOG或FILE
server_audit_query_log_limit = 1024
server_audit_syslog_facility = LOG_USER
server_audit_syslog_ident = mysql-server_auditing
server_audit_syslog_priority = LOG_INFO

开启和关闭日志记录

mysql> set global audit_json_file='OFF';      
Query OK, 0 rows affected (2.70 sec)

mysql> set global audit_json_file='ON'; 
Query OK, 0 rows affected (0.00 sec)

[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*10;"
[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';"

MariaDB审计插件变量解释：https://mariadb.com/kb/en/mariadb/server_audit-system-variables/#server_audit_events

MariaDB审计插件介绍：https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/

Percona的审计插件：https://www.percona.com/blog/2014/05/07/mysql-audit-plugin-now-available-in-percona-server-5-5-and-5-6/

https://www.percona.com/doc/percona-server/5.6/management/audit_log_plugin.html

MySQL的审计插件：https://dev.mysql.com/doc/refman/5.6/en/audit-log-installation.html

第三方的MySQL审计插件：https://github.com/mcafee/mysql-audit

这个据说支持MySQL (5.1, 5.5, 5.6, 5.7) 和MariaDB (5.5, 10.0, 10.1) 的32、64位

20170225_152301 新增MySQL 5.6.26开启审计

mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
ERROR 1123 (HY000): Cant initialize function 'AUDIT'; Plugin initialization function failed.
mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| audit_log   | ACTIVE        |
+-------------+---------------+
1 row in set (0.00 sec)

mysql> SHOW global VARIABLES LIKE '%audit%';
+---------------------------+---------------+
| Variable_name             | Value         |
+---------------------------+---------------+
| audit_log_buffer_size     | 1048576       |
| audit_log_file            | audit.log     |
| audit_log_flush           | OFF           |
| audit_log_format          | OLD           |
| audit_log_handler         | FILE          |
| audit_log_policy          | ALL           |
| audit_log_rotate_on_size  | 0             |
| audit_log_rotations       | 0             |
| audit_log_strategy        | ASYNCHRONOUS  |
| audit_log_syslog_facility | LOG_USER      |
| audit_log_syslog_ident    | percona-audit |
| audit_log_syslog_priority | LOG_INFO      |
+---------------------------+---------------+
12 rows in set (0.00 sec)

mysql> SET GLOBAL audit_log_rotate_on_size=1073741824;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL audit_log_flush=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW global VARIABLES LIKE '%audit%';
+---------------------------+---------------+
| Variable_name             | Value         |
+---------------------------+---------------+
| audit_log_buffer_size     | 1048576       |
| audit_log_file            | audit.log     |
| audit_log_flush           | OFF           |
| audit_log_format          | OLD           |
| audit_log_handler         | FILE          |
| audit_log_policy          | ALL           |
| audit_log_rotate_on_size  | 1073741824    |
| audit_log_rotations       | 0             |
| audit_log_strategy        | ASYNCHRONOUS  |
| audit_log_syslog_facility | LOG_USER      |
| audit_log_syslog_ident    | percona-audit |
| audit_log_syslog_priority | LOG_INFO      |
+---------------------------+---------------+
12 rows in set (0.00 sec)

mysql>