Bug #61767 need to select database for replication to work when binlog-ignore-db is enabled
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
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.054 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.