2

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?

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' );
  • Max, sorry..I didn't get your point. The "replicate-wild-do-table=ads.%" option is for replicating ANYTHING to the ads database, right? Also, as I mentioned, the statement is getting replicated even with the ads database specified explicitly if I omit the use statement. – user1450378 Jun 17 '14 at 11:07
  • The USE test; will change your context and every statements will be ignored (because of your configuration replicate-ignore-db = test) even if you explicitly specify a db name before your table name (with the dot notation). – Maxime Fouilleul Jun 17 '14 at 18:04
  • 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)? – user1450378 Jun 18 '14 at 4:48
  • Answer in edit. – Maxime Fouilleul Jun 18 '14 at 8:51
  • Thanks for the explanation, Max. It really makes sense. But it looks like "replicate-ignore-db" is really useless. I removed all "replicate-ignore-db" statements from slave my.cnf and kept "replicate-wild-do-table = ads.%" intact. Now the replication seems to be working fine even in the "USE test; INSERT INTO ads.users.." scenario. Looks like "replicate-wild-do-table" means "replicate only this db and ignore everything else". Is my understandings are correct or am I still missing anything? – user1450378 Jun 18 '14 at 9:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.