MySQL Bugs: #56640: Combination of replicate_do_db and replicate_do

Bug #56640	Combination of replicate_do_db and replicate_do_table does not work
Submitted:	8 Sep 2010 10:42	Modified:	19 Aug 2019 8:43
Reporter:	Dave Chirnside	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.75, 5.6.45, 5.7.27	OS:	Linux (Ubuntu 9.04)
Assigned to:		CPU Architecture:	Any
Tags:	replication replicate_do_table replicate_do_db

Description:
In the slave my.cnf I tried the following:
replicate_do_db=foo
replicate_do_table=bar.table1

On the master I have:
binlog_do_db=foo
binlog_do_db=bar

Updates on the master to db foo, causes update on the slave as expected however updates on bar.table1 are not replicated on the slave.

I can see the updates to bar.table1 in the slave relay-bin file but they are not executed for some reason.

If I change the slave configuration to:
replicate_wild_do_table=foo.%
replicate_do_table=bar.table1
then replication works correctly for updates to db foo and table bar.table1.

It appears therefore that combinations of replicate_do_db and replicate_do_table do not work.

How to repeat:
Add lines to my.cnf on slave:
replicate_do_db=foo
replicate_do_table=bar.table1

Add linns to master my.cnf:
binlog_do_db=foo
binlog_do_db=bar

Restart both slave and master.

For the second scenario change slave my.cnf to:
replicate_wild_do_table=foo.%
replicate_do_table=bar.table1

Restart slave.

Thank you for the report.

But version 5.0.75 is old and several bugs were fixed since. Please upgrade to version 5.0.91, try with it and if problem still exists provide example of statement which is not replicated on slave.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This problem can be reproduced on MySQL 5.0.96 and later version.

[Test case]
(1) Download MySQL 5.0.96 tarball from MOS(p13872352_50_Linux-x86-64.zip)
(2) Make replication environment with dbdeployer by using this command.

dbdeployer deploy replication 5.0.96

(3) Change node1's my.sandbox.cnf. Add two lines to bottom.

replicate_do_db=foo
replicate_do_table=bar.table1

(4) Restart node1 with this command.

./restart

(5) Run these command on Master

create database foo;
use foo;
create table t1(i1 int);
insert into t1 values(1);
commit;

(6) In Node2, we can see table t1 and data, but 
we cannot see table t1 in Node1;

[Workaround]
(i) Use replicate-wild-do-table instead of replicate-do-db like this,

replicate_wild_do_table=foo.%
replicate_do_table=bar.table1

or 

(ii) Use replicate_do_db only like this.

replicate_do_db=foo

I can see the same problem on current supported version (MySQL 5.6, 5.7, 8.0).