mysql 5.6 审计_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