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;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)
mysql> drop table huifu.test;

2.查看二进制日志

mysqlbinlog 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

BINLOG '
AmK6YBMBAAAAMAAAAFwEAAAAAPUAAAAAAAEABWh1aWZ1AAR0ZXN0AAEDAAFSboBG
AmK6YB4BAAAANwAAAJMEAAAAAPUAAAAAAAEAAgAB//4BAAAA/gIAAAD+AwAAAP4EAAAAMIlfGQ==
'/*!*/;

...

为什么是乱码？

mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

如何查看乱码

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.导入数据

[root@mysql mysql]# mysqlbinlog log-bin.000001 --start-position 833 --stop-position 1202 > /root/yy.sql
[root@mysql mysql]# mysql -p123456 < /root/yy.sql
mysql> select * from huifu.test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)