Description:
When binlog-ignore-db is set on master and statement based-replication, any statement not explicitely using the database will not be replicated.
You need to do "mysql> use any-db-not-ignored" for the statement to be propagated.
This is not ideal for systemwide mysql commands such as create user, where you should not need to perform a "use mysql" for it to be replicated.
How to repeat:
Run 2 instances of mysql
minimal my.cnf :
cat /# etc/my.cnf
[client]
#password = your_password
port = 3307
socket = /tmp/mysql1.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld1]
datadir = /usr/local/mysql-data1
user = mysql
pid_file = /usr/local/mysql-data1/pid.pid
port = 3307
socket = /tmp/mysql1.sock
server-id = 12
[mysqld2]
datadir = /usr/local/mysql-data2
pid_file = /usr/local/mysql-data2/pid.pid
user = mysql
port = 3308
socket = /tmp/mysql2.sock
server-id = 13
[mysqld]
skip-external-locking
log-bin=mysql-bin
# /usr/local/mysql/support-files/mysqld_multi.server start 1
# /usr/local/mysql/support-files/mysqld_multi.server start 2
Configure replication (on both) :
mysql> GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'replication'@'127.0.0.1' IDENTIFIED BY 'replic';
Start replication (on both) :
mysql1> CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_PORT=3308,MASTER_USER='replication',MASTER_PASSWORD='replic';
mysql1> START SLAVE;
mysql2> CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_PORT=3307,MASTER_USER='replication',MASTER_PASSWORD='replic';
mysql2> START SLAVE;
Issue the following statement on 1 of servers :
mysql1> CREATE USER newuser;
Check if the user exists on other :
mysql> show grants for newuser;
+-------------------------------------+
| Grants for newuser@% |
+-------------------------------------+
| GRANT USAGE ON *.* TO 'newuser'@'%' |
+-------------------------------------+
1 row in set (0.00 sec)
This is the correct behaviour.
Now, stop both mysql :
/usr/local/mysql/support-files/mysqld_multi.server stop
Add the following line within [mysqld] section :
binlog-ignore-db = thisdbisignored
And start again mysql :
/usr/local/mysql/support-files/mysqld_multi.server start
Issue the following statement :
mysql> CREATE USER anotheruser;
mysql> show grants for anotheruser;
ERROR 1141 (42000): There is no such grant defined for user 'anotheruser' on host '%'
The event was not replicated.
If I issue a "Use mysql" before, then the statement is replicated.
The documentation says :
http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#option_mysqld_b...
(...)
Statement-based logging. Tells the server to not log any statement where the default database (that is, the one selected by USE) is db_name.
(...)
I also reproduced the same behaviour using binlog_format = ROW in my.cnf
Suggested fix:
When using binlog-ignore-db, statements not selecting a database should not be ignored
The following schema :
http://dev.mysql.com/doc/refman/5.5/en/replication-rules-db-options.html
could also be linked to the documentation page for binlog-ignore-db
Thank you in advance
Reference :
http://bugs.mysql.com/bug.php?id=6749
http://bugs.mysql.com/bug.php?id=48147