mysql找出相关操作记录

来源：互联网发布：米祖淘宝上有店铺吗编辑：程序博客网时间：2024/05/20 22:38

一个案例的模拟过程：

1 删除数据

mysql> insert into test values(1);
Query OK, 1 row affected (0.06 sec)

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

mysql> delete from test;
Query OK, 1 row affected (0.15 sec)

mysql> commit;

2 从bin_log中提取恢复信息

[mysql@SR3 mysqldata]$ mysqlbinlog --no-defaults --start-datetime='2013-11-14 13:35:00' --stop-datetime='2013-11-14 13:36:20' -vv mysql-bin.000003 > /mysqldata/binlog2013.txt

3 查看/mysqldata/binlog2013.txt文件数据取出来之后，需要把数据解析反转，原始数据

[mysql@SR3 mysqldata]$ more binlog2013.txt
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#131114 13:17:33 server id 1 end_log_pos 107   Start: binlog v 4, server v 5.5.16-log created 131114 13:17:33 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
vROFUg8BAAAAZwAAAGsAAAABAAQANS41LjE2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAC9E4VSEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 590
#131114 13:35:42 server id 1 end_log_pos 678   Query   thread_id=6     exec_time=0     error_code=0
use test/*!*/;
SET TIMESTAMP=1384454142/*!*/;
SET @@session.pseudo_thread_id=6/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table test(id int)
/*!*/;
# at 678
#131114 13:35:57 server id 1 end_log_pos 746   Query   thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1384454157/*!*/;
BEGIN
/*!*/;
# at 746
#131114 13:35:57 server id 1 end_log_pos 835   Query   thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1384454157/*!*/;
insert into test values(1)
/*!*/;
# at 835
#131114 13:35:57 server id 1 end_log_pos 862   Xid = 24
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

注：通过这个二进制日志可以查看DML操作及DDL操作的一些语句