Replication successful but slave skips certain queries

Question

I am testing a MySQL master-slave replication. Here is the master my.cnf:

log-bin=/var/log/mysql/mysql-bin.log
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Here is slave my.cnf:

server-id=3
log-bin=/var/log/mysql/mysql-bin.log
relay-log=/var/log/mysql/slave-relay.log
relay-log-index=/var/log/mysql/slave-relay-log.index
log-slave-updates=1
replicate-wild-do-table = ads.%
replicate-ignore-db = mysql
replicate-ignore-db = test

Everything works fine but if I execute something like this on master, it won't replicate to slave.

use test;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

If I omit use test, the replication is successful.

I would like to skip test db from replication but also need to avoid the above mentioned issue. I have searched a number of posts but couldn't find a solution. Am I missing anything simple?

Answer 1

--replicate-ignore-db applies only to the default database (determined by the USE statement). Because the ads database was specified explicitly in the statement, the statement has not been replicated.

My advice is to change the context with a USE instead of explicitly specify the database in the statement.

Max

EDIT (for questions)

1.

Ok, got it but then what should be the my.cnf values if I want to log all databases on the master but selectively replicate on slave (like ignoring test and replicating ads)?

Note that your syntax is correct, all statements will be written on binary log (master) and the slave will ignore the mysql and test databases BUT YOU SHOULD EXIT THE CONTEXT mysql and test before run queries on other databases.

The context is ads : REPLICATED

USE ads;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

The context is test: IGNORED

USE test;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

The context is toto: REPLICATED because of your replicate-wild-do-table and toto is not ignored by a replicate-ignore-db

USE toto;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

2.

Looks like "replicate-wild-do-table" means "replicate only this db and ignore everything else".

replicate-do-table=ads will replicate statements on ads database (your context should be ads, USE ads;) and ignore the statements on other databases:

The context is ads : REPLICATED

USE ads;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

The context is test : IGNORED

USE test;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

replicate-wild-do-table=ads will replicate statements on ads database even if your are in an other context (test for instance), and ignore the statements on other databases.

The context is ads : REPLICATED

USE ads;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );

The context is test : REPLICATED

USE test;
INSERT INTO ads.users ( id , username ) VALUES ( 19, 'tyy' );