MySQL用户误操作delete from 表。如何查询这个用户信息？

Question

MySQL用户误操作delete from 表。如何查询这个用户信息？

Answer 1

MySQL企业版有audit审计模块。开启审计之后，可以查询到每个操作的用户。另外，如果你有备份文件，也可以通过备份文件加binlog文件对误删除的表进行恢复。希望能帮到你。

Answer 2

MySQL社区版没有自带的设计功能或插件。调研发现MariaDB的audit plugin 同样适用于MySQL，支持更细粒度的审计，比如只审计DDL操作，满足我们的需求。因为最近测试环境的某表结构经常性的被变更且数据被清空的情况，所以引入MariaDB的插件对DDL进行审计
MariaDB audit plugin 官网
MariaDB audit plugin 下载地址
http://dl.bintray.com/mcafee/mysql-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的审计日志如
20180416 11:25:22,mysql-5.6.dev.yz,root,localhost,34950852,21554,QUERY,test,'truncate table t1',0
关于server_audit_events可选的参数有connect：会记录所有的连接，包括失败的以及关闭连接的日志，如日志中记录的，但是对我们来说不关心这些
[[email protected] 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
set global server_audit_events='CONNECT,QUERY,table,QUERY_DDL,QUERY_DML,QUERY_DCL';关键参数：
server_audit_file_path：如果server_audit_output_type=file，设置为日志路径
server_audit_logging：必须要开启才记录日志
server_audit_events：有如下选项，多个用逗号隔开
CONNECT：Logs connects, disconnects and failed connects (including the error code).
QUERY：Queries issued and their results (in plain text), including failed queries due to syntax or permission errors.
TABLE：Which tables were affected by query execution.
QUERY_DDL：Works as the 'QUERY' value, but filters only DDL-type queries (CREATE, ALTER, etc).
QUERY_DML：Works as the 'QUERY' value, but filters only DML-type queries (INSERT, UPDATE, etc).
QUERY_DCL：Works as the 'QUERY' value, but filters only DCL-type queries (GRANT, REVOKE, etc.)