binlog_format='mixed'
(root@g1-db-test-v01:3306)[(none)]>use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed (root@g1-db-test-v01:3306)[test]>update db1.t2 set name='haha' where id=105; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 (root@g1-db-test-v01:3306)[test]>use db1; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed (root@g1-db-test-v01:3306)[db1]>delete from test.t1 where id=11; Query OK, 1 row affected (0.02 sec)
假如使用mysqlbinlog恢复db1的数据，命令加上参加 -d :
mysqlbinlog -d db1 test-mysql-bin.000115 > 115d.sql
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #170809 15:38:35 server id 1882073306 end_log_pos 120 CRC32 0x9c11ffa3 Start: binlog v 4, server v 5.6.23-72.1-log created 170809 15:38:35 # Warning: this binlog is either in use or was not closed properly. BINLOG ' +7uKWQ/aKC5wdAAAAHgAAAABAAQANS42LjIzLTcyLjEtbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAaP/ EZw= '/*!*/; # at 120 #170809 15:39:20 server id 1882073306 end_log_pos 198 CRC32 0x58ea2e3a Query thread_id=75 exec_time=0 error_code=0 SET TIMESTAMP=1502264360/*!*/; SET @@session.pseudo_thread_id=75/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1073741824/*!*/; 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 198 # at 313 #170809 15:39:20 server id 1882073306 end_log_pos 344 CRC32 0xb2671190 Xid = 32389 COMMIT/*!*/; # at 344 #170809 15:41:05 server id 1882073306 end_log_pos 422 CRC32 0x4befc6fe Query thread_id=75 exec_time=0 error_code=0 SET TIMESTAMP=1502264465/*!*/; BEGIN /*!*/; # at 422 #170809 15:41:05 server id 1882073306 end_log_pos 526 CRC32 0x2b0ef4d4 Query thread_id=75 exec_time=0 error_code=0 use `db1`/*!*/; SET TIMESTAMP=1502264465/*!*/; delete from test.t1 where id=11 /*!*/; # at 526 #170809 15:41:05 server id 1882073306 end_log_pos 557 CRC32 0xb34d26a0 Xid = 32473 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; (END)
上面的binlog只记录了 delete from test.t1 where id=11
描述这样一种场景，上述案例本想恢复update那一条数据，结果只能恢复delete那一条。
而delete的却是test库的表，如果在测试环境进行恢复，因只需恢复db1，没有创建test库，则恢复时会报错，恢复终止。
也就是说，在binlog_format='statement/mix'时，如果mysqlbinlog 不加 --database(-d)参数，解析的binlog里记录所有变更sql。但如果增加了 --database db1,则只记录use db1后面的变更sql
这样有一个问题，如果db1里的一张表 t1被删除了，我们想恢复。按照一般方法，是先使用最近备份恢复db1所有数据（因为按库备份)，然后使用mysqlbinlog恢复db1的数据到drop t1前一刻，此时使用mysqlbinlog 如果加了 --databae=db1，那在有跨库操作的情况下，会遗漏部分sql；而如果不加 --database=db1，那就更没法恢复了，因为之前只使用了db1的备份先恢复的，也就是只恢复了db1。综上，不加--database的话，就要将所库都先恢复一遍，然后使用mysqlbinlog不加参数继续恢复。
当设置row模式时，则不存在这种问题。
binlog_format='row'
(root@g1-db-test-v01:3306)[(none)]>use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed (root@g1-db-test-v01:3306)[test]>update db1.t2 set name='hehe' where id=105; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 (root@g1-db-test-v01:3306)[test]>use db1; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed (root@g1-db-test-v01:3306)[db1]>delete from test.t1 where id=10; Query OK, 1 row affected (0.02 sec)
mysqlbinlog --base64-output=decode-rows -vv -d db1 test-mysql-bin.000116 > 116d.sql
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #170809 16:28:12 server id 1882073306 end_log_pos 120 CRC32 0x6c11777a Start: binlog v 4, server v 5.6.23-72.1-log created 170809 16:28:12 # Warning: this binlog is either in use or was not closed properly. # at 120 #170809 16:28:51 server id 1882073306 end_log_pos 192 CRC32 0x256a9f3c Query thread_id=78 exec_time=0 error_code=0 SET TIMESTAMP=1502267331/*!*/; SET @@session.pseudo_thread_id=78/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1073741824/*!*/; 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 192 #170809 16:28:51 server id 1882073306 end_log_pos 246 CRC32 0x285d8d5d Table_map: `db1`.`t2` mapped to number 395 # at 246 #170809 16:28:51 server id 1882073306 end_log_pos 366 CRC32 0xb84daa2e Update_rows: table id 395 flags: STMT_END_F ### UPDATE `db1`.`t2` ### WHERE ### @1=105 /* INT meta=0 nullable=0 is_null=0 */ ### @2='haha' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### @3='57747ab889255af96b48d65e505382' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### @4='' /* VARSTRING(72) meta=72 nullable=0 is_null=0 */ ### @5=NULL /* VARSTRING(72) meta=0 nullable=1 is_null=1 */ ### SET ### @1=105 /* INT meta=0 nullable=0 is_null=0 */ ### @2='hehe' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### @3='57747ab889255af96b48d65e505382' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### @4='' /* VARSTRING(72) meta=72 nullable=0 is_null=0 */ ### @5=NULL /* VARSTRING(72) meta=0 nullable=1 is_null=1 */ # at 366 #170809 16:28:51 server id 1882073306 end_log_pos 397 CRC32 0x74a5b1df Xid = 32558 COMMIT/*!*/; # at 397 #170809 16:29:07 server id 1882073306 end_log_pos 468 CRC32 0x66079b66 Query thread_id=78 exec_time=0 error_code=0 SET TIMESTAMP=1502267347/*!*/; BEGIN /*!*/; # at 468 # at 519 # at 567 #170809 16:29:07 server id 1882073306 end_log_pos 598 CRC32 0xb3ffd246 Xid = 32583 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; (END)
