mysql 查找误删除人员信息

1:建监控连接信息的表

use dba;
create table accesslog(`thread_id` int primary key auto_increment, `time` timestamp, `localname` varchar(40), `machine_name` varchar(40));


2：设置变量init_connect

mysql> show variables like 'init%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| init_connect | |
| init_file | |
| init_slave | |
+---------------+-------+
3 rows in set (0.00 sec)

mysql> set global init_connect='insert into dba.accesslog(thread_id,time,localname,machine_name) values(connection_id(),now(),user(),current_user());';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'init%';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| init_connect | insert into dba.accesslog(thread_id,time,localname,machine_name) values(connection_id(),now(),user(),current_user()); |
| init_file | |
| init_slave | |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)


3：分配用户权限
mysql> grant select,insert,update on dba.accesslog to baidandan@'192.168.9.45' identified by 'baidandan';
Query OK, 0 rows affected (0.00 sec)

--为了做实验，给baidandan赋予操作dba.t表的权限
mysql> grant select,delete on dba.t to baidandan@'192.168.9.45';
Query OK, 0 rows affected (0.00 sec)

4：测试
--客户端连接进行测试
C:\Users\dandan>mysql -u baidandan -p -h 192.168.6.51
Enter password: *********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 371
Server version: 5.6.20-r5436-log Source distribution


Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> use dba;
Database changed
mysql> delete from t;
Query OK, 1 row affected (0.10 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from t;
Empty set (0.00 sec)


假如我现在想看是谁把DBA.t表里的数据给删掉了。
查看日志：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 1640 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

--假如我知道这个数据是在6月18号9点后被删除的：
[root@ser6-51 data]# mysqlbinlog mysql-bin.000007 --start-datetime='2015-06-18 09:00:00'

--查到删除的语句：
#150618 16:55:30 server id 1 end_log_pos 1609 CRC32 0xa2296c53 Query thread_id=371 exec_time=0 error_code=0
use `dba`/*!*/;
SET TIMESTAMP=1434617730/*!*/;
delete from t
/*!*/;

--查询accesslog表
mysql> select * from dba.accesslog where thread_id=371;
+-----------+---------------------+------------------------+------------------------+
| thread_id | time | localname | machine_name |
+-----------+---------------------+------------------------+------------------------+
| 371 | 2015-06-18 16:55:19 | baidandan@192.168.9.45 | baidandan@192.168.9.45 |
+-----------+---------------------+------------------------+------------------------+
1 row in set (0.00 sec)

show master status;

 

日志位置

mysql/log/xxxxxx.bin

select * from mysql.slow_log where thread_id=247;