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)

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

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.


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

  • hi,I have no binlog-do-db=test item, and I test row basement and statement statement, both of them need to add "use test" while data log in binary, is differenet from mysql document ~~ strange ! and mysql version is 5.5.9 – LawrenceLi Mar 21 '17 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.