1010针对一个binlog日志的分析

针对一个BINLOG日志的分析

-- 当前binlog_format | ROW
[root@109 mysql]# cat wang1010.txt
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#171010 16:15:41 server id 109 end_log_pos 120 CRC32 0x62153ff3 Start: binlog v 4, server v 5.6.36-log created 171010 16:15:41
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
rYHcWQ9tAAAAdAAAAHgAAAABAAQANS42LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAfM/
FWI=
'/*!*/;
# at 120
#171010 16:50:25 server id 109 end_log_pos 250 CRC32 0x3b7ba6f0 Query thread_id=3487 exec_time=0 error_code=0
use `wang0728`/*!*/;
SET TIMESTAMP=1507625425/*!*/;
SET @@session.pseudo_thread_id=3487/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
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=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table wang1010(id int ,name varchar(20))
/*!*/;

# at 250
#171010 16:50:41 server id 109 end_log_pos 326 CRC32 0x50dafb46 Query thread_id=3487 exec_time=0 error_code=0
SET TIMESTAMP=1507625441/*!*/;
BEGIN
/*!*/;
# at 326
#171010 16:50:41 server id 109 end_log_pos 384 CRC32 0xc12a366e Table_map: `wang0728`.`wang1010` mapped to number 94
# at 384
#171010 16:50:41 server id 109 end_log_pos 430 CRC32 0x32327701 Write_rows: table id 94 flags: STMT_END_F

BINLOG '
4YncWRNtAAAAOgAAAIABAAAAAF4AAAAAAAEACHdhbmcwNzI4AAh3YW5nMTAxMAACAw8CPAADbjYq
wQ==
4YncWR5tAAAALgAAAK4BAAAAAF4AAAAAAAEAAgAC//wBAAAABXdhbmdxAXcyMg==
'/*!*/;
### INSERT INTO `wang0728`.`wang1010`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='wangq' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 430
#171010 16:50:41 server id 109 end_log_pos 461 CRC32 0xdf1bff19 Xid = 1855
COMMIT /*!*/; -- 通过COMMIT进行分割，多少个COMMIT对应的多个提交操作
# at 461

-- 第二个事物 插入数据
#171010 16:50:57 server id 109 end_log_pos 537 CRC32 0xfd3f88a0 Query thread_id=3487 exec_time=0 error_code=0 -- 开始时间 SERVER_ID 线程ID 执行时长 是否有错误
SET TIMESTAMP=1507625457/*!*/;-- 时间戳标志 来源于FROM_UNIXTIME
BEGIN
/*!*/;
# at 537
#171010 16:50:57 server id 109 end_log_pos 595 CRC32 0x42dbbe1e Table_map: `wang0728`.`wang1010` mapped to number 94
# at 595
#171010 16:50:57 server id 109 end_log_pos 642 CRC32 0xf93f3fdf Write_rows: table id 94 flags: STMT_END_F -- Write_rows代表ROWS_EVENT分为三种：WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT，分别对应insert，update和delete操作。

BINLOG '
8YncWRNtAAAAOgAAAFMCAAAAAF4AAAAAAAEACHdhbmcwNzI4AAh3YW5nMTAxMAACAw8CPAADHr7b
Qg==
8YncWR5tAAAALwAAAIICAAAAAF4AAAAAAAEAAgAC//wCAAAABmZhbmdmYd8/P/k=
'/*!*/;
### INSERT INTO `wang0728`.`wang1010` -- 执行具体SQL 逻辑SQL语句 insert into wang1010 values(2,'fangfa');
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='fangfa' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 642
#171010 16:50:57 server id 109 end_log_pos 673 CRC32 0xeffda5ba Xid = 1856-- 在事务提交时，不管是STATEMENT还是ROW格式的binlog，都会在末尾添加一个XID_EVENT事件代表事务的结束。该事件记录了该事务的ID，在MySQL进行崩溃恢复时，根据事务在binlog中的提交情况来决定是否提交存储引擎中状态为prepared的事务。
COMMIT/*!*/;-- 执行提交操作
# at 673

-- 第三个事物 修改数据
#171010 16:51:15 server id 109 end_log_pos 749 CRC32 0x367d25aa Query thread_id=3487 exec_time=0 error_code=0
SET TIMESTAMP=1507625475/*!*/;
BEGIN
/*!*/;
# at 749
#171010 16:51:15 server id 109 end_log_pos 807 CRC32 0xb1f654d9 Table_map: `wang0728`.`wang1010` mapped to number 94
# at 807
#171010 16:51:15 server id 109 end_log_pos 864 CRC32 0x88e157f4 Update_rows: table id 94 flags: STMT_END_F

BINLOG '
A4rcWRNtAAAAOgAAACcDAAAAAF4AAAAAAAEACHdhbmcwNzI4AAh3YW5nMTAxMAACAw8CPAAD2VT2
sQ==
A4rcWR9tAAAAOQAAAGADAAAAAF4AAAAAAAEAAgAC///8AQAAAAV3YW5ncfwBAAAABHl1YW70V+GI
'/*!*/;
### UPDATE `wang0728`.`wang1010`
### WHERE
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='wangq' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ -- 逻辑SQL语句 update wang1010 set name='yuan' where id=1;
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='yuan' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 864
#171010 16:51:15 server id 109 end_log_pos 895 CRC32 0x6065d0a5 Xid = 1857
COMMIT/*!*/;
# at 895

-- 第四个事物 删除数据
#171010 16:51:27 server id 109 end_log_pos 971 CRC32 0x313a7b00 Query thread_id=3487 exec_time=0 error_code=0
SET TIMESTAMP=1507625487/*!*/;
BEGIN
/*!*/;
# at 971
#171010 16:51:27 server id 109 end_log_pos 1029 CRC32 0xfecc1bd5 Table_map: `wang0728`.`wang1010` mapped to number 94
# at 1029
#171010 16:51:27 server id 109 end_log_pos 1074 CRC32 0xbae577a2 Delete_rows: table id 94 flags: STMT_END_F

BINLOG '
D4rcWRNtAAAAOgAAAAUEAAAAAF4AAAAAAAEACHdhbmcwNzI4AAh3YW5nMTAxMAACAw8CPAAD1RvM
/g==
D4rcWSBtAAAALQAAADIEAAAAAF4AAAAAAAEAAgAC//wBAAAABHl1YW6id+W6
'/*!*/;
### DELETE FROM `wang0728`.`wang1010` -- 逻辑SQL语句 delete from wang1010 where id=1;
### WHERE
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='yuan' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 1074
#171010 16:51:27 server id 109 end_log_pos 1105 CRC32 0x36a54189 Xid = 1858 --
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */; -- 为什么最后存在一个ROLLBACK ？？
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 

-- 恢复数据的方法 mysqlbinlog MySQL-bin.000011 | mysql -uroot -p111111
或者
mysqlbinlog --start-position=134 --stop-position=330 yueliangdao_binglog.000001 >test1.txt
进入MYSQL导入
mysql> source c:\\test1.txt

参考 http://www.cnblogs.com/ivictor/p/5780037.html