why binlog doesn't log the DML statement?

Question

two method compared
1. use test; create table t (a int);
2. create table test.t1(a int);

my result is the first one can log the binlog but the second one can't not

use test;create table t(a int);

mysqlbinlog -v mysql-bin.000005|grep create
use `test`/*!*/;
SET TIMESTAMP=1490090893/*!*/;
create table t(a int)
/*!*/;
DELIMITER ;

but when I do not use "use", DML cannot log in binlog,can tell me why?

create table test.t(a int);

mysqlbinlog -v mysql-bin.000005|grep create test.t

IGGt · Answer

Do you use statement based or row based logging? If it is statement based then you most likely have binlog-do-db set in your replication filters e.g. binlog-do-db=test so that only queries where the default database is test are written to your binary log.

e.g.

USE test;
CREATE table t . . .

USE test;
CREATE table otherdb.table . . .

both appear in your binary logs, but

USE otherdb;
CREATE table t2. . .

USE otherdb;
CREATE table test.t . . .

Don't appear.

In the first instance your default database is test, in the second it is otherdb.

see: https://dev.mysql.com/doc/refman/5.5/en/replication-rules-db-options.html

why binlog doesn't log the DML statement?

One Answer

Add your own answers!

Ask a Question