[摘要]+----+------+
1 row in set (0.00 sec)mysql> update hello set name = David where id = 3;
Query OK...
+----+------+
1 row in set (0.00 sec)
mysql> update hello set name = 'David' where id = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0查看 ROW 格式的 binlog，需要使用 sudo mysqlbinlog -v --base64-output=DECODE-ROWS /var/lib/mysql/mysql-bin.000002 命令。执行 update 后相应新增的 binlog 内容： 若执行如下 SQL： mysql> insert hello (name) values ('Frank');
Query OK, 1 row affected (0.02 sec) 相应生成的 binlog 内容： # at 442
#180617 22:55:47 server id 1 end_log_pos 507 CRC32 0x79de08a7 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 507
#180617 22:55:47 server id 1 end_log_pos 581 CRC32 0x56f9eb6a Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1529247347/*!*/;
BEGIN
/*!*/;
# at 581
#180617 22:55:47 server id 1 end_log_pos 634 CRC32 0xedb73620 Table_map: `testdb`.`hello` mapped to number 110
# at 634
#180617 22:55:47 server id 1 end_log_pos 684 CRC32 0x525a6a70 Write_rows: table id 110 flags: STMT_END_F
### INSERT INTO `testdb`.`hello`
### SET
### @1=4
### @2='Frank'
# at 684
#180617 22:55:47 server id 1 end_log_pos 715 CRC32 0x09a0d4de Xid = 14
COMMIT/*!*/; 若执行如下 SQL： mysql> delete from hello where id = 2;
Query OK, 1 row affected (0.02 sec) 相应生成的 binlog 内容： # at 715
#180617 22:56:44 server id 1 end_log_pos 780 CRC32 0x9f52450e Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 780
#180617 22:56:44 server id 1 end_log_pos 854 CRC32 0x0959bc8d Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1529247404/*!*/;
BEGIN
/*!*/;
# at 854
#180617 22:56:44 server id 1 end_log_pos 907 CRC32 0x2945260f Table_map: `testdb`.`hello` mapped to number 110
# at 907
#180617 22:56:44 server id 1 end_log_pos 956 CRC32 0xc70df255 Delete_rows: table id 110 flags: STMT_END_F
### DELETE FROM `testdb`.`hello`
### WHERE
### @1=2
### @2='Bill'
# at 956
#180617 22:56:44 server id 1 end_log_pos 987 CRC32 0x0c98f18e Xid = 15
COMMIT/*!*/; 使用 binlog 增量恢复 MySQL 逻辑备份通常会结合全量备份和增量备份，使用 mysqldump 定期全量备份数据库，然后利用 binlog 保存增量数据。恢复数据时，就是用 mysqldump 备份的数据恢复到备份的时间点。数据库在备份时间点到当前时间的增量修改，则通过 mysqlbinlog 将 binlog 中的增量数据恢复到数据库。现在假设已经使用 mysqldump 将数据库还原到： mysql> select * from hello;
+----+------+
|