MySQL 添加审计功能

MySQL社区版没有自带的设计功能或插件。调研发现MariaDB的audit plugin 同样适用于MySQL，支持更细粒度的审计，比如只审计DDL操作，满足我们的需求。因为最近测试环境的某表结构经常性的被变更且数据被清空的情况，所以引入MariaDB的插件对DDL进行审计

MariaDB audit plugin 官网

MariaDB audit plugin 下载地址

查看MySQL的插件路径

mysql> show global variables like '%plugin%';

+---------------+------------------------------+

| Variable_name | Value                        |

+---------------+------------------------------+

| plugin_dir    | /usr/local/mysql/lib/plugin/ |

+---------------+------------------------------+

1 row in set (0.00 sec)

mysql> select version();

+------------+

| version()  |

+------------+

| 5.6.33-log |

+------------+

1 row in set (0.00 sec)

我选择下载的插件版本文件为 server_audit-1.4.0.tar.gz

解压后将插件文件server_audit.so拷贝到MySQL的插件文件目录下

安装

mysql> INSTALL PLUGIN server_audit SONAME 'server_audit.so';

Query OK, 0 rows affected (0.02 sec)
#在线安装加载插件重启后会失效，可以在配置文件中配置
[mysqld] 
...
plugin_load=server_audit=server_audit.so

配置审计项

# 安装完之后相关的配置项有

SHOW GLOBAL VARIABLES LIKE 'server_audit%';

+-------------------------------+-----------------------+

| Variable_name                 | Value                 |

+-------------------------------+-----------------------+

| server_audit_events           | CONNECT,QUERY,TABLE   |

| server_audit_excl_users       |                       |

| server_audit_file_path        | server_audit.log      |

| server_audit_file_rotate_now  | OFF                   |

| server_audit_file_rotate_size | 1000000               |

| server_audit_file_rotations   | 9                     |

| server_audit_incl_users       |                       |

| 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              |

+-------------------------------+-----------------------+

根据我们的需求设置

mysql> set global server_audit_events='query_ddl,table';

Query OK, 0 rows affected (0.00 sec)

mysql> set global server_audit_logging=on;

Query OK, 0 rows affected (0.00 sec)

设置完之后关于ddl的审计日志如

 ::,mysql-5.6.dev.yz,root,localhost,,,QUERY,test,'truncate table t1',

关于server_audit_events可选的参数有connect：会记录所有的连接，包括失败的以及关闭连接的日志，如日志中记录的，但是对我们来说不关心这些

[root@mysql-5.6.dev.yz 3306_develop]# tailf server_audit.log

20180416 11:22:42,mysql-5.6.dev.yz,root,10.211.253.104,34950731,0,CONNECT,test,,0

20180416 11:22:48,mysql-5.6.dev.yz,admin,10.211.253.153,34950655,0,DISCONNECT,test,,0

20180416 11:22:48,mysql-5.6.dev.yz,admin,10.211.253.153,34950732,0,CONNECT,test,,0

20180416 11:22:49,mysql-5.6.dev.yz,admin,10.211.253.101,34950664,0,DISCONNECT,test,,0

巴特西

MySQL 添加审计功能

最新文章

热门文章