mysql 社区版没有审计功能, 企业版有审计功能, 但是需要付费
社区版可以使用 mcafee 开发的审计功能插件 libaudit_plugin.so, 这个插件可以用在 mysql 5.1 以后的版本, 使用时请下载对应的版本
https://bintray.com/mcafee/mysql-audit-plugin/release#files
解压拷贝 libaudit_plugin.so 到 mysql 的插件目录:
查看 MySQL 的插件目录:
- > show variables like 'plugin_dir';
- +---------------+------------------------------+
- | Variable_name | Value |
- +---------------+------------------------------+
- | plugin_dir | /usr/local/mysql/lib/plugin/ |
- +---------------+------------------------------+
复制库文件到 MySQL 库目录下:
- # cp audit-plugin-mysql-5.7-1.1.4-725/lib/libaudit_plugin.so /usr/local/mysql/lib/plugin/
- # chmod a+x libaudit_plugin.so
进入 mysql 命令窗口, 安装插件:
- > install plugin audit soname 'libaudit_plugin.so';
- Query OK, 0 rows affected (0.06 sec)
查看 mysql 当前已经加载了哪些插件:
- > show plugins;
- +----------------------------+----------+--------------------+--------------------+---------+
- | Name | Status | Type | Library | License |
- +----------------------------+----------+--------------------+--------------------+---------+
- | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
- | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
- | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
- | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
- | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
- | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
- | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
- | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
- | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
- | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
- | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
- | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
- | partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
- | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
- | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
- | ngram | ACTIVE | FTPARSER | NULL | GPL |
- | rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
- | rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
- | AUDIT | ACTIVE | AUDIT | libaudit_plugin.so | GPL |
- +----------------------------+----------+--------------------+--------------------+---------+
查看版本:
- > show global status like '%audit%';
- +------------------------+-----------+
- | Variable_name | Value |
- +------------------------+-----------+
- | Audit_protocol_version | 1.0 |
- | Audit_version | 1.1.4-725 |
- +------------------------+-----------+
开启 Audit 功能:
- > SET GLOBAL audit_json_file=ON;
- Query OK, 0 rows affected (0.00 sec)
执行任何语句(默认会记录任何语句, 有语法错误的不会记录), 然后去 mysql 数据目录查看 mysql-audit.json 文件(默认为该文件)
插入一些数据, 查看一下 mysql-audit.json 文件信息(json 格式), 如下:
复制代码
- {
- "msg-type": "activity",
- "date": "1510038432019",
- "thread-id": "43",
- "query-id": "1891",
- "user": "root",
- "priv_user": "root",
- "ip": "","host":"localhost","connect_attrs": {"_os":"linux-glibc2.5","_client_name":"libmysql","_pid":"4009","_client_version":"5.7.9","_platform":"x86_64","program_name":"mysql"},"pid":"4009","os_user":"root","appname":"mysql","rows":"1","cmd":"insert","objects": [
- {
- "db": "part",
- "name": "e",
- "obj_type": "TABLE"
- }
- ],
- "query": "insert into e values (9898,'smart','james')"
- }
可以查看插件有哪些可配置的参数:
mysql> SHOW GLOBAL VARIABLES LIKE '%audi%';
其中我们需要关注的参数有:
1. audit_json_file
是否开启 audit 功能(0 为关闭)
2. audit_json_log_file
记录文件的路径和名称信息(默认放在 mysql 数据目录下. json 文件)
3. audit_record_cmds
audit 记录的命令, 默认为记录所有命令可以设置为任意 dmldclddl 的组合如: audit_record_cmds=select,insert,delete,update 还可以在线设置 set global audit_record_cmds=NULL(表示记录所有命令)
4. audit_record_objs
audit 记录操作的对象, 默认为记录所有对象( 审计哪些数据库), 可以用 SET GLOBAL audit_record_objs=NULL 设置为默认也可以指定为下面的格式: audit_record_objs=,test.*,mysql.*,information_schema.*
5. audit_whitelist_users
用户白名单
详细的参数说明, 可以直接访问官方说明:
https://github.com/mcafee/mysql-audit/wiki/Configuration
最后为了保证重启数据库, 配置不丢失, 修改 my.cnf 配置文件, 将下面的配置添加到 [mysqld] 中, 所以在配置文件中 my.cnf 加入参数:
- audit_json_file=on #保证 mysql 重启后自动启动插件
- plugin-load=AUDIT=libaudit_plugin.so #防止删除了插件, 重启后又会加载
- audit_record_cmds='insert,delete,update,create,drop,alter,grant,truncate' #要记录哪些命令语句, 因为默认记录所有操作;
保存重启即可看到效果
mysql 审计插件卸载
直接执行 UNINSTALL PLUGIN AUDIT 卸载会报错:
- > uninstall plugin AUDIT;
- ERROR 1148 (42000): Uninstall AUDIT plugin disabled
- > set audit_uninstall_plugin=on;
- ERROR 1238 (HY000): Variable 'audit_uninstall_plugin' is a read only variable
需要在 my.cnf 中 [mysqld] 下添加 audit_uninstall_plugin=1, 重启 mysql 重启完毕后执行两次 UNINSTALL PLUGIN AUDIT; 即可卸载
- > UNINSTALL PLUGIN AUDIT;
- ERROR 1620 (HY000): Uninstall AUDIT plugin must be called again to complete
- > UNINSTALL PLUGIN AUDIT;
- Query OK, 0 rows affected, 1 warning (0.01 sec)
卸载完成后需要从 my.cnf 中删除 audit_uninstall_plugin=1 , 否则下次 mysql 启动会报错:[ERROR] /data/mysql/bin/mysqld: unknown variable 'audit_uninstall_plugin=1'
来源: https://www.thinksaas.cn/group/topic/838865/