Description:
We have two DB servers configured to replicate in a master/slave
arrangement. Specifically, we have some tables in db1 the master server
that we want to have a copy of in db2 on the slave server. This is
achieved using the replicate-rewrite-db directive. As specified in the
docs, replicate-rewrite-db only works on certain queries, so some
operations don't replicate cleanly from master to slave. I noticed that
a statement like:
GRANT SELECT ON d1.t1 TO 'foo'@'localhost' IDENTIFIED BY 'bar';
executed on the master breaks replication on the slave, yielding:
130122 1:59:50 [ERROR] Slave SQL: Error 'Table 'd1.t1' doesn't exist' on query. Default database: ''. Query: 'grant select on d1.t1 to 'foo'@'localhost' identified by 'bar'', Error_code: 1146
130122 1:59:50 [Warning] Slave: Table 'd1.t1' doesn't exist Error_code: 1146
This is consistent with the replicate-rewrite-db docs. As we have no
particular reason to replicate users/privilege grants from one DB to
another, we'd like to disable this. According to
http://dev.mysql.com/doc/refman/5.0/en/replication-features-userprivs.html,
we can achieve this by adding:
replicate-wild-ignore-table=mysql.%
to the slave's my.cnf. We did this, but still get the same error with
the above GRANT statement. We're on 5.1.67, but I tested the procedure to reproduce the bug on 5.5.29 with the same results.
How to repeat:
- Configure two MySQL DB instances, master and slave.
- Execute 'CREATE DATABASE d1' on the master.
- Execute 'CREATE DATABASE d2' on the slave.
- Edit the master's my.cnf to specify binlog-format=mixed.
- Edit the slave's my.cnf to contain replicate-wild-ignore-table=mysql.% and replicate-rewrite-db="d1->d2"
- Configure the slave to replicate from the master according to http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
- Start replication by issuing START SLAVE on the slave
- On the master, issue USE d1; CREATE TABLE t1 (foo integer not null); confirm that d2.t1 exists on slave.
- Issue 'GRANT SELECT ON `d1`.`t1` TO 'foo'@'localhost' IDENTIFIED BY 'bar'; on the master.
- On the slave, execute 'SHOW SLAVE STATUS' to see whether replication is still working.
My expectation is that the slave would ignore the GRANT SELECT statement. Instead, in both 5.1.67 and 5.5.29, I get Error 'Table 'd1.t1' doesn't exist' on query. Default database: 'd2'. Query: 'grant select on `d1`.`t1` to 'foo'@'localhost' IDENTIFIED BY 'bar'
