需求描述:
今天在看mysqlbinlog的内容,看到了--start-position和--stop-position这些选项,
就测试下这个参数具体该怎么进行使用呢,在此记录下.
操作过程:
1.通过mysqlbinlog以文本的格式查看某个binlog的内容
[mysql@redhat6 data]$ mysqlbinlog mysql-bin.000027 | more /*!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 #180410 17:57:32 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.57-log created 180410 17:57:32 BINLOG ' jIrMWg8BAAAAZwAAAGsAAAAAAAQANS41LjU3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== '/*!*/; # at 107 #180410 17:57:32 server id 1 end_log_pos 180 Query thread_id=89 exec_time=0 error_code=0 SET TIMESTAMP=1523354252/*!*/; SET @@session.pseudo_thread_id=89/*!*/; 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/*!*/; BEGIN /*!*/; # at 180 ##############################################################################################################起始点##### #180410 17:57:32 server id 1 end_log_pos 496 Query thread_id=89 exec_time=0 error_code=0 use `employees`/*!*/; SET TIMESTAMP=1523354252/*!*/; INSERT INTO `departments` VALUES ('d001','Marketing'), ('d002','Finance'), ('d003','Human Resources'), ('d004','Production'), ('d005','Development'), ('d006','Quality Management'), ('d007','Sales'), ('d008','Research'), ('d009','Customer Service') /*!*/; # at 496 ##############################################################################################################起始点##### #180410 17:57:32 server id 1 end_log_pos 523 Xid = 30173 COMMIT/*!*/; # at 523 #180410 17:57:32 server id 1 end_log_pos 596 Query thread_id=89 exec_time=1 error_code=0 SET TIMESTAMP=1523354252/*!*/; BEGIN /*!*/; # at 596
备注:在at 180和at 496之间是一个插入的操作.180的结束点即end_log_pos就是496.
2.将employees表的内容清空
3.通过--start-position和--stop-position来指定执行哪个范围内的操作
[mysql@redhat6 data]$ mysqlbinlog --start-position=180 --stop-position=496 mysql-bin.000027 | mysql -uroot -p Enter password: [mysql@redhat6 data]$
4.查看employess表的数据
备注:at 180和at 496之间的insert语句已经被成功执行.所以呢,--start-position就是起始点,--stop-position就是结束点,两个位置之间的内容,就会被mysql重新执行.那么在实际的恢复过程中,就可以跳过一些错误的语句不执行.
文档创建时间:2018年4月18日15:04:55