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

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

#####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 #定义切割审计日志的文件大小
server_audit_file_rotations = 0            #定义审计日志的轮询个数,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*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审计插件变量解释: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> 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)