MySQL binlog 的恢复操作

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

测试出有个问题：mysqlbinlog 不加任何参数恢复整个binlog 日志文件发现里面有这个操作 SET @@SESSION.GTID_NEXT 的操作，

如果需要恢复文件的时候就需要把他过滤掉，否则恢复数据不成功

测试环境：./mysql Ver 14.14 Distrib 5.7.19

结论：需要用binlog 日志还原数据记录的时候，备份好自己的binlog 日志以后，然后执行 reset master，然后在直接导入我们mysqlbinlog 导出的文件。

或者导入的时候加入-f 参数强行导入

测试步骤如下：

测试表结构

CREATE TABLE `t1` (

  `id` int(60) NOT NULL AUTO_INCREMENT,

  `name` varchar(20) NOT NULL DEFAULT '',

  `age` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

1 insert into t1 values(1,'xiaoxiao',20),(2,'huahua',21),(3,'lili',22);  ###mysqb-bin.0000001

2 flush logs

3 insert into t1 values(4,'xiaohong',18);  # mysql-bin.000002

  insert into t1 values(5,'aying',22);

4 flush logs

5  delete from t1 where id<4;   #mysql-bin.000003

这个时候我们看到我们有了3个binlog文件

show master logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |       499 |

| mysql-bin.000002 |       774 |

| mysql-bin.000003 |       194 |

+------------------+-----------+

 /usr/local/mysql/bin/mysqlbinlog --start-position=219  mysql-bin.000001 >/tmp/ok.txt

###查看以下是日志信息

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#170922 10:14:10 server id 2003309  end_log_pos 123 CRC32 0x509b4a7a    Start: binlog v 4, server v 5.7.19-log created 170922 10:14:10 at startup

ROLLBACK/*!*/;

BINLOG '

8nHEWQ9tkR4AdwAAAHsAAAAAAAQANS43LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAADyccRZEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AXpKm1A=

'/*!*/;

# at 219

#170922 10:19:50 server id 2003309  end_log_pos 290 CRC32 0x5073a29d    Query   thread_id=183   exec_time=0     error_code=0

SET TIMESTAMP=1506046790/*!*/;

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

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

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

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/*!*/;

BEGIN

/*!*/;

# at 290

#170922 10:19:50 server id 2003309  end_log_pos 338 CRC32 0xca27d49b    Table_map: `zst`.`t1` mapped to number 223

# at 338

#170922 10:19:50 server id 2003309  end_log_pos 421 CRC32 0xacc98577    Write_rows: table id 223 flags: STMT_END_F

BINLOG '

RnPEWRNtkR4AMAAAAFIBAAAAAN8AAAAAAAEAA3pzdAACdDEAAwMPAwI8AASb1CfK

RnPEWR5tkR4AUwAAAKUBAAAAAN8AAAAAAAEAAgAD//gBAAAACHhpYW94aWFvFAAAAPgCAAAABmh1

YWh1YRUAAAD4AwAAAARsaWxpFgAAAHeFyaw=

'/*!*/;

# at 421

#170922 10:19:50 server id 2003309  end_log_pos 452 CRC32 0x57228b9c    Xid = 3692

COMMIT/*!*/;

# at 452

#170922 10:20:02 server id 2003309  end_log_pos 499 CRC32 0xe31d7a38    Rotate to mysql-bin.000002  pos: 4

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*/;

/usr/local/mysql/bin/mysqlbinlog -v  mysql-bin.000001 >/tmp/faile.txt

#### 查看以下的日志信息

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#170922 10:14:10 server id 2003309  end_log_pos 123 CRC32 0x509b4a7a    Start: binlog v 4, server v 5.7.19-log created 170922 10:14:10 at startup

ROLLBACK/*!*/;

BINLOG '

8nHEWQ9tkR4AdwAAAHsAAAAAAAQANS43LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAADyccRZEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AXpKm1A=

'/*!*/;

# at 123

#170922 10:14:10 server id 2003309  end_log_pos 154 CRC32 0x2a8835fb    Previous-GTIDs

# [empty]

# at 154

#170922 10:19:50 server id 2003309  end_log_pos 219 CRC32 0xbf230db3    GTID    last_committed=0        sequence_number=1       rbr_only=yes

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

SET @@SESSION.GTID_NEXT= '6fb6a0c6-9dd2-11e7-a4e8-0050569c404d:1'/*!*/;        #### 这个操作，直接导致恢复整个日志文件会出错。

# at 219

#170922 10:19:50 server id 2003309  end_log_pos 290 CRC32 0x5073a29d    Query   thread_id=183   exec_time=0     error_code=0

SET TIMESTAMP=1506046790/*!*/;

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

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

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

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/*!*/;

BEGIN

/*!*/;

# at 290

#170922 10:19:50 server id 2003309  end_log_pos 338 CRC32 0xca27d49b    Table_map: `zst`.`t1` mapped to number 223

# at 338

#170922 10:19:50 server id 2003309  end_log_pos 421 CRC32 0xacc98577    Write_rows: table id 223 flags: STMT_END_F

BINLOG '

RnPEWRNtkR4AMAAAAFIBAAAAAN8AAAAAAAEAA3pzdAACdDEAAwMPAwI8AASb1CfK

RnPEWR5tkR4AUwAAAKUBAAAAAN8AAAAAAAEAAgAD//gBAAAACHhpYW94aWFvFAAAAPgCAAAABmh1

YWh1YRUAAAD4AwAAAARsaWxpFgAAAHeFyaw=

'/*!*/;

### INSERT INTO `zst`.`t1`

### SET

###   @1=1

###   @2='xiaoxiao'

###   @3=20

### INSERT INTO `zst`.`t1`

### SET

###   @1=2

###   @2='huahua'

###   @3=21

### INSERT INTO `zst`.`t1`

### SET

###   @1=3

###   @2='lili'

###   @3=22

# at 421

#170922 10:19:50 server id 2003309  end_log_pos 452 CRC32 0x57228b9c    Xid = 3692

COMMIT/*!*/;

# at 452

#170922 10:20:02 server id 2003309  end_log_pos 499 CRC32 0xe31d7a38    Rotate to mysql-bin.000002  pos: 4

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*/;