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)
