今天有个库被人误操作,更新时没加条件,结果全表都更新了。所以业务人员要求添加审计。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