MySQL Audit 审计
邢昊焜
审计插件的选择：
市面上有三种免费的审计插件
- mariadb
- percona
- McAfee
其中，McAfee的最容易获得，我们选用McAfee的审计插件。
mysql-audit下载地址
1. 部署mysql-audit
1.1 确定本机MySQL插件的位置
mysql [(none)]> show global variables like 'plugin_dir';
+---------------+-------------------------------+
| Variable_name | Value |
+---------------+-------------------------------+
| plugin_dir | /home/mysql/mysql/lib/plugin/ |
+---------------+-------------------------------+
1 row in set (0.03 sec)
1.2 解压，复制到plugin的文件目录
注意：下载下来的是归属root用户，且没有执行权限的。需要加上。
$ unzip audit-plugin-mysql-8.0-1.1.7-921-linux-x86_64.zip
Archive: audit-plugin-mysql-8.0-1.1.7-921-linux-x86_64.zip
creating: audit-plugin-mysql-8.0-1.1.7-921/
creating: audit-plugin-mysql-8.0-1.1.7-921/lib/
inflating: audit-plugin-mysql-8.0-1.1.7-921/lib/libaudit_plugin.so
inflating: audit-plugin-mysql-8.0-1.1.7-921/COPYING
inflating: audit-plugin-mysql-8.0-1.1.7-921/THIRDPARTY.txt
inflating: audit-plugin-mysql-8.0-1.1.7-921/README.txt
inflating: audit-plugin-mysql-8.0-1.1.7-921/plugin-name.txt
creating: audit-plugin-mysql-8.0-1.1.7-921/utils/
inflating: audit-plugin-mysql-8.0-1.1.7-921/utils/offset-extract.sh
$ cd audit-plugin-mysql-8.0-1.1.7-921
$ ll
total 36
-rw-r--r--. 1 root root 19071 Jan 28 23:14 COPYING
drwxr-xr-x. 2 root root 31 Jan 28 23:15 lib
-rw-r--r--. 1 root root 1833 Jan 28 23:14 plugin-name.txt
-rw-r--r--. 1 root root 1905 Jan 28 23:14 README.txt
-rw-r--r--. 1 root root 6494 Jan 28 23:14 THIRDPARTY.txt
drwxr-xr-x. 2 root root 30 Jan 28 23:15 utils
$ cd lib/
$ ll
total 2964
-rw-r--r--. 1 root root 3034048 Jan 28 23:15 libaudit_plugin.so
$ chown -R mysql:mysql libaudit_plugin.so
$ chmod +x libaudit_plugin.so
$ cd /home/mysql/mysql/lib/plugin/
$ ll
total 105996
...
-rwxr-xr-x. 1 mysql mysql 3034048 Jan 28 23:15 libaudit_plugin.so
...
2. 开启及配置mysql-audit
2.1 实例内开启（不推荐）
生产环境不推荐使用这个方式打开审计插件。此方法不用重启实例。
mysql [(none)]> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
Note: On production systems, McAfee recommends using the plugin-load option for installing the audit plugin.
2.2 my.cnf配置文件中打开
在[mysqld]标签下增加：
plugin-load=AUDIT=libaudit_plugin.so
在配置好my.cnf后需要重启实例才能生效。
2.3 配置
可以在MySQL客户端中用show global variables查看审计的配置。
一共有30个可自定义的参数。
mysql [(none)]> show global variables like '%audit%';
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| audit_before_after | after |
| audit_checksum | |
| audit_client_capabilities | OFF |
| audit_delay_cmds | |
| audit_delay_ms | 0 |
| audit_force_record_logins | OFF |
| audit_header_msg | ON |
| audit_json_file | OFF |
| audit_json_file_bufsize | 1 |
| audit_json_file_flush | OFF |
| audit_json_file_retry | 60 |
| audit_json_file_sync | 0 |
| audit_json_log_file | mysql-audit.json |
| audit_json_socket | OFF |
| audit_json_socket_name | /var/run/db-audit/mysql.audit__data_mysql_3308_mydata_3308 |
| audit_json_socket_retry | 10 |
| audit_json_socket_write_timeout | 1000 |
| audit_offsets | |
| audit_offsets_by_version | ON |
| audit_password_masking_cmds | CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER,UPDATE |
| audit_password_masking_regex | identified(?:/\*.*?\*/|\s)*?by(?:/\*.*?\*/|\s)*?(?:password)?(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]|password(?:/\*.*?\*/|\s)*?\((?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"](?:/\*.*?\*/|\s)*?\)|password(?:/\*.*?\*/|\s)*?(?:for(?:/\*.*?\*/|\s)*?\S+?)?(?:/\*.*?\*/|\s)*?=(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]|password(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"] |
| audit_record_cmds | |
| audit_record_objs | |
| audit_sess_connect_attrs | ON |
| audit_socket_creds | ON |
| audit_uninstall_plugin | OFF |
| audit_validate_checksum | ON |
| audit_validate_offsets_extended | ON |
| audit_whitelist_cmds | BEGIN,COMMIT,PING |
| audit_whitelist_users | |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
30 rows in set (0.01 sec)
其中：
audit_json_file：控制审计插件的开关
audit_json_log_file：定义审计日志存放的位置，默认值：mysql-audit.json
audit_json_file_flush：刷新审计日志用
配置例：
[mysqld]
#audit
audit_json_file=ON
plugin-load=AUDIT=libaudit_plugin.so
audit_json_log_file=/data/logs/mysql-audit.json
2.3.1 audit_json_file_flush的用法
可以创建个crontab每小时分割一次审计日志，参考内容如下：
#!/bin/bash
source ~/.bash_profile
mv mysql-audit.json mysql-audit.json.`date +%F`
mysql -S /data/mysql/3308/mysql.sock -uroot -proot -e 'set global audit_json_file_flush=on;'
大概这个意思，真正应用在生产上，还需要添加自己所需的内容。
类似资料：