mysqlbinlog基于某个偏移量进行数据的恢复(重做),--start-position,--stop-position的使用方法

需求描述:

　　今天在看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