Mysql审计

今天有个库被人误操作，更新时没加条件，结果全表都更新了。所以业务人员要求添加审计。Mysql有一个插件来实现，需要单独下载。

1）下载：https://bintray.com/mcafee/mysql-audit-plugin/release

2）解压后将libaudit_plugin.so文件拷贝到$MYSQL_HOME/lib/plugin

3）然后安装即可

mysql> INSTALL PLUGIN audit  SONAME 'libaudit_plugin.so';

4）安装完成后查看相关变量及状态

mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
...
| AUDIT                      | ACTIVE   | AUDIT              | libaudit_plugin.so | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+

mysql> show variables like '%audit%';
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name                   | Value                                                                                                                                                                                                                                                                                                                                                                                       |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| audit_checksum                  |                                                                                                                                                                                                                                                                                                                                                                                             |
| 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          | /tmp/mysql.audit__home_mysql_data_3306                                                                                                                                                                                                                                                                                                                                                      |
| audit_json_socket_retry         | 10                                                                                                                                                                                                                                                                                                                                                                                          |
| audit_offsets                   |                                                                                                                                                                                                                                                                                                                                                                                             |
| audit_offsets_by_version        | ON                                                                                                                                                                                                                                                                                                                                                                                          |
| audit_password_masking_cmds     | CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER                                                                                                                                                                                                                                                                                                           |
| 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_uninstall_plugin          | OFF                                                                                                                                                                                                                                                                                                                                                                                         |
| audit_validate_checksum         | ON                                                                                                                                                                                                                                                                                                                                                                                          |
| audit_validate_offsets_extended | ON                                                                                                                                                                                                                                                                                                                                                                                          |
| audit_whitelist_cmds            |                                                                                                                                                                                                                                                                                                                                                                                             |
| audit_whitelist_users           |                                                                                                                                                                                                                                                                                                                                                                                             |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

mysql> show status like '%audit%';
+------------------------+-----------+
| Variable_name          | Value     |
+------------------------+-----------+
| Audit_protocol_version | 1.0       |
| Audit_version          | 1.0.8-527 |
+------------------------+-----------+

5）安装完成后，默认不开启审计通过上述变量做一些设置。以下这是我添加设置，一看就明白

对应下列行：1、开启审计（0为关闭）2、审计的日志文件（默认在data目录下.json文件）3、开启日志刷新（同mysql binlog）4、只记录lmis和Insterface库操作5、只记录这些操作6、审计日志缓冲区（最大256K）

set global audit_json_file=1;
set global audit_json_log_file='mysql-audit.log';
set global audit_json_file_flush=on;
set global audit_record_objs='lmis.*,interface.*';
set global audit_record_cmds='insert,update,delete,truncate,drop,alter,rename';
set global audit_json_file_bufsize=64*1024;

最后添加到my.cnf文件中

#Audit
audit_json_file=1;
audit_json_log_file='mysql-audit.log';
audit_json_file_flush=on;
audit_record_objs='lmis.*,interface.*';
audit_record_cmds='insert,update,delete,truncate,drop,alter,rename';
audit_json_file_bufsize=64K;

说明：其它参数详见：https://github.com/mcafee/mysql-audit/wiki/Configuration