無聊研究binlog寫了個(gè)提取單個(gè)表的binlog 的信息，便于誤刪恢復(fù)數(shù)據(jù)，這樣做的目的是為了減少恢復(fù)時(shí)其他表可能引起的出錯(cuò)，以及縮短恢復(fù)時(shí)間 首先研究binlog日志的格式 如下： /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #160229 15:19:52 server id 162 end_log_pos 123 CRC32 0xbbe9b551 Start: binlog v 4, server v 5.7.9-log created 160229 15:19:52 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' GPHTVg+iAAAAdwAAAHsAAAABAAQANS43LjktbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAY8dNWEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AVG16bs= '/*!*/; # at 123 #160229 15:19:52 server id 162 end_log_pos 154 CRC32 0xd473f6f4 Previous-GTIDs # [empty] # at 154 #160229 15:18:07 server id 162 end_log_pos 219 CRC32 0xe86bb499 Anonymous_GTID last_committed=0 sequence_number=1 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #160229 15:18:07 server id 162 end_log_pos 291 CRC32 0xe1803afe Query thread_id=102887 exec_time=341 error_code=0 SET TIMESTAMP=1456730287/*!*/; SET @@session.pseudo_thread_id=102887/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=524288/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!/C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 291 #160229 15:18:07 server id 162 end_log_pos 343 CRC32 0xcfc435f2 Table_map: `test`.`ty` mapped to number 127 # at 343 #160229 15:18:07 server id 162 end_log_pos 463 CRC32 0xb4ef18fe Write_rows: table id 127 flags: STMT_END_F BINLOG ' r/DTVhOiAAAANAAAAFcBAAAAAH8AAAAAAAEABHRlc3QAAnR5AAQDDw8DBBQAFAAP8jXEzw== r/DTVh7iAAAAeAAAAM8BAAAAAH8AAAAAAAEAAgAE//AFAAAABEVFRUUCQzFfAAAA8AEAAAAEQUFB QQJDMUMAAADwAwAAAARDQ0NDAkMxQwAAAPAEAAAABEREREQCQzFBAAAA8AIAAAAEQkJCQgJDMTcA AAD+GO+0 '/*!*/; # at 463 #160229 15:18:07 server id 162 end_log_pos 494 CRC32 0xed1fb95b Xid = 53
COMMIT/*!*/; -------到第一個(gè)commit為第一部分 為binlog必須的，缺失會(huì)報(bào)錯(cuò) # at 494 #160229 15:18:07 server id 162 end_log_pos 559 CRC32 0xef3ac14c Anonymous_GTID last_committed=1 sequence_number=2 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 559 #160229 15:18:07 server id 162 end_log_pos 631 CRC32 0xe834f1d8 Query thread_id=102887 exec_time=350 error_code=0 SET TIMESTAMP=1456730287/*!*/; BEGIN /*!*/; # at 631 #160229 15:18:07 server id 162 end_log_pos 683 CRC32 0xc64ac724 Table_map: `test`.`ty` mapped to number 127 # at 683 #160229 15:18:07 server id 162 end_log_pos 803 CRC32 0x4a5f1c75 Write_rows: table id 127 flags: STMT_END_F BINLOG ' r/DTVhOiAAAANAAAAKsCAAAAAH8AAAAAAAEABHRlc3QAAnR5AAQDDw8DBBQAFAAPJMdKxg== r/DTVh7iAAAAeAAAACMDAAAAAH8AAAAAAAEAAgAE//AFAAAABEVFRUUCQzFfAAAA8AEAAAAEQUFB QQJDMUMAAADwAwAAAARDQ0NDAkMxQwAAAPAEAAAABEREREQCQzFBAAAA8AIAAAAEQkJCQgJDMTcA AAB1HF9K '/*!*/; # at 803 #160229 15:18:07 server id 162 end_log_pos 834 CRC32 0x96148022 Xid = 77
COMMIT/*!*/; -------最后一個(gè)commit到最后必須存在 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 思路就是先將第一部分以及最后一部分提取出來，我們csplite文件（以COMMIT/*!*/; 為分隔標(biāo)示，不熟悉csplit命令的先去熟悉一下用法），在第一以及最后一部分中間插入單個(gè)表的binlog 信息而拼接成一個(gè)符合binlog格式的日志文件
