Bug #61767 need to select database for replication to work when binlog-ignore-db is enabled
Submitted: 6 Jul 2011 8:40 Modified: 1 Aug 2013 19:06
Reporter: Joffrey MICHAIE Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:5.5.13 OS:Solaris (SunOS 5.10 )
Assigned to: CPU Architecture:Any
Tags: 5.5.13, binlog-ignore-db, replication, solaris

[6 Jul 2011 8:40] Joffrey MICHAIE
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
[8 Jul 2012 15:22] Valeriy Kravchuk
Thank you for the feature request.
[1 Aug 2013 19:06] Joffrey MICHAIE
Hi,

I think this is a duplicate of bug http://bugs.mysql.com/bug.php?id=60188 which was recently fix.

Regards,
Joffrey