群里看到有同学提问，多瞅了眼
[root@mysql55 mysql]# mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000004 --skip-gtids ... # at 3370 #171026 10:06:36 server id 1 end_log_pos 3465 CRC32 0x7e37d2ec Query thread_id=10 exec_time=0 error_code=0 SET TIMESTAMP=1508983596/*!*/; insert into kk.kk values(7) /*!*/; # at 3465 [root@mysql55 mysql]# mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000004 --skip-gtids -d kk ... # at 3370 # at 3465 #171026 10:06:36 server id 1 end_log_pos 3496 CRC32 0x3c5ea8e8 Xid = 160 COMMIT/*!*/;
原意是想筛选指定数据库下的entries，指定-d解析发现3370~3465之间的entries丢失
首先binlog中记录了insert into kk.kk values(7)，可以肯定当时binlog_format=statement；使用-d参数后insert语句消失，怀疑日志格式引起异常
自己针对statement、row格式测试一番，然后解析binlog基本确定上述问题的原因
1、日志格式是 binlog_format='statement'
2、insert into kk.kk values(7) 这个语句不是在kk库下执行
后来那位同学私聊我，确认了上面的情况(･ω･)
官方文档对mysqlbinlog --database已经解释的十分清楚
https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html
--database=db_name, -d db_name
This option causes mysqlbinlog to output entries from the binary log (local log only) that occur while db_name is been selected as the default database by USE.
The --database option for mysqlbinlog is similar to the --binlog-do-db option for mysqld, but can be used to specify only one database. If --database is given multiple times, only the last instance is used.
The effects of this option depend on whether the statement-based or row-based logging format is in use, in the same way that the effects of --binlog-do-db depend on whether statement-based or row-based logging is in use.
Statement-based logging.The --database option works as follows:
• While db_name is the default database, statements are output whether they modify tables in db_name or a different database.
• Unless db_name is selected as the default database, statements are not output, even if they modify tables in db_name.
• There is an exception for CREATE DATABASE, ALTER DATABASE, and DROP DATABASE. The database being created, altered, or dropped is considered to be the default database when determining whether to output the statement.
Suppose that the binary log was created by executing these statements using statement-based-logging:
mysqlbinlog --database=test does not output the first two INSERT statements because there is no default database. It outputs the three INSERT statements following USE test, but not the three INSERT statements following USE db2.INSERT INTO test.t1 (i) VALUES(100); INSERT INTO db2.t2 (j) VALUES(200); USE test; INSERT INTO test.t1 (i) VALUES(101); INSERT INTO t1 (i) VALUES(102); INSERT INTO db2.t2 (j) VALUES(201); USE db2; INSERT INTO test.t1 (i) VALUES(103); INSERT INTO db2.t2 (j) VALUES(202); INSERT INTO t2 (j) VALUES(203);
mysqlbinlog --database=db2 does not output the first two INSERT statements because there is no default database. It does not output the three INSERT statements following USE test, but does output the three INSERT statements following USE db2.
Row-based logging.mysqlbinlog outputs only entries that change tables belonging to db_name.The default database has no effect on this. Suppose that the binary log just described was created using row-based logging rather than statement-based logging. mysqlbinlog --database=test outputs only those entries that modify t1 in the test database, regardless of whether USE was issued or what the default database is.
If a server is running with binlog_format set to MIXED and you want it to be possible to use mysqlbinlog with the --database option, you must ensure that tables that are modified are in the database selected by USE. (In particular, no cross-database updates should be used.)
Statement格式：use db_name后的语句(操作db_name、其他库)，全部能被mysqlbinlog --database=db_name解析出来；use other_db后面的语句，哪怕是操作db_name下的对象也不能被mysqlbinlog --database=db_name解析出来；create/alter/drop database db_name能被解析出来。
Row格式：只要操作db_name下的对象，就能被mysqlbinlog --database=db_name解析出来，与语句在哪个db执行无关。
开篇的那个问题应该如何避免呢？使用Row-based logging~
开始接触MySQL复制时，看到很多说binlog-do-db/binlog-ignore-db是危险的(跨库操作)，当时就傻傻的死记下来。很长时间过后发现那些文章根本就没提及binlog_format，根本就不能一概而论~没有环境说明的测试都是扯蛋，文章说的再好也不及用实验来验证~