5.4使用二进制日志恢复数据

1、创建数据库并插入数据

mysql> create database huifu;
mysql> create table huifu.test(id int);
mysql> insert into huifu.test values (1),(2),(3),(4);
mysql> select * from huifu.test;
mysql> drop table huifu.test;

 2、查看二进制日志

mysqlbinlog log-bin.000001

mysqlbinlog --base64-output=decode-rows -v log-bin.000001

# at 768
#210605 1:25:14 server id 1 end_log_pos 833 CRC32 0xe27f88b1 Anonymous_GTID last_committed=4 sequence_number=5
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 833
#210605 1:25:14 server id 1 end_log_pos 935 CRC32 0x12fcb4b7 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1622827514/*!*/;
create table huifu.test(id int)
/*!*/;
# at 935
#210605 1:25:22 server id 1 end_log_pos 1000 CRC32 0x3ee6da2b Anonymous_GTID last_committed=5 sequence_number=6
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1000
#210605 1:25:22 server id 1 end_log_pos 1068 CRC32 0xbc6cb240 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1622827522/*!*/;
BEGIN
/*!*/;
# at 1068
#210605 1:25:22 server id 1 end_log_pos 1116 CRC32 0x46806e52 Table_map: `huifu`.`test` mapped to number 245
# at 1116
#210605 1:25:22 server id 1 end_log_pos 1171 CRC32 0x195f8930 Write_rows: table id 245 flags: STMT_END_F
### INSERT INTO `huifu`.`test`
### SET
### @1=1
### INSERT INTO `huifu`.`test`
### SET
### @1=2
### INSERT INTO `huifu`.`test`
### SET
### @1=3
### INSERT INTO `huifu`.`test`
### SET
### @1=4
# at 1171
#210605 1:25:22 server id 1 end_log_pos 1202 CRC32 0x40be65a8 Xid = 38
COMMIT/*!*/;

 3、导入数据

mysqlbinlog log-bin.00001 --start-position 883 --stop-position 1202 > /root/yy.sql

mysql -p < /root/yy.sql