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

需求描述:

　　今天在看mysqlbinlog的内容,看到了--start-position和--stop-position这些选项,

　　就测试下这个参数具体该怎么进行使用呢,在此记录下.

操作过程:

1.通过mysqlbinlog以文本的格式查看某个binlog的内容

[mysql@redhat6 data]$ mysqlbinlog mysql-bin. | 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

# :: server id   end_log_pos      Start: binlog v , server v 5.5.-log created  ::

BINLOG '

jIrMWg8BAAAAZwAAAGsAAAAAAAQANS41LjU3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==

'/*!*/;

# at

# :: server id   end_log_pos      Query    thread_id=    exec_time=    error_code=

SET TIMESTAMP=/*!*/;

SET @@session.pseudo_thread_id=/*!*/;

SET @@session.foreign_key_checks=, @@session.sql_auto_is_null=, @@session.unique_checks=, @@session.autocommit=/*!*/;

SET @@session.sql_mode=/*!*/;

SET @@session.auto_increment_increment=, @@session.auto_increment_offset=/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=,@@session.collation_connection=,@@session.collation_server=/*!*/;

SET @@session.lc_time_names=/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 180   ##############################################################################################################起始点#####

# :: server id   end_log_pos      Query    thread_id=    exec_time=    error_code=

use `employees`/*!*/;

SET TIMESTAMP=/*!*/;

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   ##############################################################################################################起始点#####

# :: server id   end_log_pos      Xid =

COMMIT/*!*/;

# at

# :: server id   end_log_pos      Query    thread_id=    exec_time=    error_code=

SET TIMESTAMP=/*!*/;

BEGIN

/*!*/;

# at 

备注:在at 180和at 496之间是一个插入的操作.180的结束点即end_log_pos就是496.

2.将employees表的内容清空

3.通过--start-position和--stop-position来指定执行哪个范围内的操作

[mysql@redhat6 data]$ mysqlbinlog --start-position= --stop-position= mysql-bin. | 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