MySQL 5.6添加审计功能-mariadb之server_audit插件
由于MySQL的社区版是不支持审计系统的，因此通过第三方的插件实现审计功能。此次采用MariaDB的server_audit插件来实现MySQL的审计功能。
安装server_audit插件
1、下载server_audit插件
下载地址：http://mirrors.neusoft.edu.cn/mariadb//mariadb-5.5.62/bintar-linux-systemd-x86_64/mariadb-5.5.62-linux-systemd-x86_64.tar.gz
由于MariaDB的插件集成在自己的包中， 因此下载一个二进制包的MariaDB来获取插件。
[root@bogon software]# wget http://mirrors.neusoft.edu.cn/mariadb//mariadb-5.5.62/bintar-linux-systemd-x86_64/mariadb-5.5.62-linux-systemd-x86_64.tar.gz [root@bogon software]# tar xf mariadb-5.5.62-linux-systemd-x86_64.tar.gz [root@bogon software]# cd mariadb-5.5.62-linux-systemd-x86_64 [root@bogon mariadb-5.5.62-linux-systemd-x86_64]# cd lib/plugin/ [root@bogon plugin]# ls server_audit.so server_audit.so
2、拷贝、安装server_audit插件
查看mysql插件目录
MySQL [(none)]> show variables like 'plugin_dir'; +---------------+-------------------------------+ | Variable_name | Value | +---------------+-------------------------------+ | plugin_dir | /data/tools/mysql/lib/plugin/ | +---------------+-------------------------------+ 1 row in set (0.00 sec)
复制插件到plugin目录
[root@bogon plugin]# cp /data/source/server_audit.so ./
[root@bogon plugin]# ll server_audit.so
-rw-r--r-- 1 root root 226589 Nov 30 17:02 server_audit.so
查看当前MySQL插件情况
MySQL [(none)]> show plugins; +----------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+---------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL | ..... | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | +----------------------------+----------+--------------------+---------+---------+ 42 rows in set (0.01 sec)
安装server_audit插件
MySQL [(none)]> install plugin server_audit soname 'server_audit.so'; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> show plugins; +----------------------------+----------+--------------------+-----------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+-----------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | ... | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | SERVER_AUDIT | ACTIVE | AUDIT | server_audit.so | GPL | +----------------------------+----------+--------------------+-----------------+---------+ 43 rows in set (0.00 sec)
查看server_audit插件参数
MySQL [(none)]> show variables like '%server_audit%'; +-------------------------------+---------------------------------------------------+ | Variable_name | Value | +-------------------------------+---------------------------------------------------+ | server_audit_events | CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML,QUERY_DCL | | server_audit_excl_users | | | server_audit_file_path | server_audit.log | | server_audit_file_rotate_now | OFF | | server_audit_file_rotate_size | 200000000 | | server_audit_file_rotations | 200 | | server_audit_incl_users | | | server_audit_loc_info | | | server_audit_logging | OFF | | server_audit_mode | 1 | | 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)
启用server_audit插件
MySQL [(none)]> set global server_audit_logging=on; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> show variables like '%server_audit%'; +-------------------------------+---------------------------------------------------+ | Variable_name | Value | +-------------------------------+---------------------------------------------------+ | server_audit_events | | | server_audit_excl_users | | | server_audit_file_path | server_audit.log | | server_audit_file_rotate_now | ON | | server_audit_file_rotate_size | 200000000 | | server_audit_file_rotations | 200 | | server_audit_incl_users | | | server_audit_loc_info | | | server_audit_logging | ON | | server_audit_mode | 1 | | 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)
查看日志信息
[root@fsl_mysql_114 auditlogs]# tail -20 server_audit.log 20181130 17:21:18,fsl_mysql_114,zabbixmnt,localhost,19947,0,CONNECT,,,0 20181130 17:21:18,fsl_mysql_114,zabbixmnt,localhost,19947,743,QUERY,,'SET NAMES utf8mb4',0 20181130 17:21:18,fsl_mysql_114,zabbixmnt,localhost,19947,744,QUERY,,'select @@version_comment limit 1',0 20181130 17:21:18,fsl_mysql_114,zabbixmnt,localhost,19947,745,QUERY,,'show global status where Variable_name=\'Com_update\'',0 20181130 17:21:18,fsl_mysql_114,zabbixmnt,localhost,19947,0,DISCONNECT,,,0 20181130 17:21:19,fsl_mysql_114,zabbixmnt,localhost,19948,0,CONNECT,,,0 20181130 17:21:19,fsl_mysql_114,zabbixmnt,localhost,19948,747,QUERY,,'SET NAMES utf8mb4',0 20181130 17:21:19,fsl_mysql_114,zabbixmnt,localhost,19948,748,QUERY,,'select @@version_comment limit 1',0 20181130 17:21:19,fsl_mysql_114,zabbixmnt,localhost,19948,749,QUERY,,'show global status where Variable_name=\'Questions\'',0 20181130 17:21:19,fsl_mysql_114,zabbixmnt,localhost,19948,0,DISCONNECT,,,0 20181130 17:21:21,fsl_mysql_114,zabbixmnt,localhost,19949,0,CONNECT,,,0 20181130 17:21:21,fsl_mysql_114,zabbixmnt,localhost,19949,751,QUERY,,'SET NAMES utf8mb4',0 20181130 17:21:21,fsl_mysql_114,zabbixmnt,localhost,19949,752,QUERY,,'select @@version_comment limit 1',0 20181130 17:21:21,fsl_mysql_114,zabbixmnt,localhost,19949,753,QUERY,,'show global status where Variable_name=\'Slow_queries\'',0 20181130 17:21:21,fsl_mysql_114,zabbixmnt,localhost,19949,0,DISCONNECT,,,0 20181130 17:21:21,fsl_mysql_114,zabbixmnt,localhost,19950,0,CONNECT,,,0 20181130 17:21:21,fsl_mysql_114,zabbixmnt,localhost,19950,755,QUERY,,'SET NAMES utf8mb4',0 20181130 17:21:21,fsl_mysql_114,zabbixmnt,localhost,19950,756,QUERY,,'select @@version_comment limit 1',0 20181130 17:21:21,fsl_mysql_114,zabbixmnt,localhost,19950,757,QUERY,,'show global status where Variable_name=\'Uptime\'',0 20181130 17:21:21,fsl_mysql_114,zabbixmnt,localhost,19950,0,DISCONNECT,,,0
3、server_audit参数说明
server_audit_events ：指定记录事件的类型，可以用逗号分隔的多个值(connect,query,table)，如果开启了查询缓存(query cache)，查询直接从查询缓存返回数据，将没有table记录
(Valid Values: CONNECT, QUERY and TABLE (QUERY_DDL, QUERY_DML added in 1.2.0 and QUERY_DCL added in 1.3.0, QUERY_DML_NO_SELECT added in 1.4))
server_audit_excl_users ： 该列表的用户行为将不记录，connect将不受该设置影响
server_audit_file_path ：使用该变量设置存储日志的文件，可以指定目录，默认存放在数据目录的server_audit.log文件中
server_audit_file_rotate_now ：知否立即切割日志
server_audit_file_rotate_size ：限制日志文件的大小
server_audit_file_rotations ：指定日志文件的数量，如果为0日志将从不轮转
server_audit_incl_users ： 指定哪些用户的活动将记录，connect将不受此变量影响，该变量比server_audit_excl_users优先级高
server_audit_loc_info ：
server_audit_logging ：启动或关闭审计ON/OFF
server_audit_mode ：标识版本，用于开发测试
server_audit_output_type ：指定日志输出类型，可为SYSLOG或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
