1

I have a master/slave replication setup based on statement based replication. On master database named DB1, I have 5 tables and I want to replicate only two table on slave's database named DB2.

Also, I want to change the database name on my slave end.

So my configuration file on Slave end look like this and replication filtering is this:

   replicate-rewrite-db="DB1->DB2"
   replicate-do-table=DB2.table4
   replicate-do-table=DB2.table5

Also I have tried below filtering as well:

   replicate-rewrite-db="DB1->DB2"
   replicate-do-table=DB1.table4
   replicate-do-table=DB1.table5

But in both case records not getting replicated , also no error is reporting.

As per MySQL documentation , replicate-rewrite-db doesn't work with replicate-do-table.

Any method to resolve this? Or is this not possible?

I'm using MySQL 5.6 community edition on Linux server.

I haven't made any changes to master end as it is on customer side.

1

As you already mentioned documentation that you cannot use table level filtering on replication. You might like to try another approach instead. Replicate whole database and change all tables except those you need to use to BLACKHOLE engine.

alter table X engine=BLACKHOLE;

| improve this answer | |
  • Sir , im using innodb.. – Ankit Kapoor Aug 26 '15 at 7:28
  • What I'm suggesting here is because you will not be able to filter replication as well as rewrite to new database... you can choose a "hack"... 1. Specify "replicate-rewrite-db" and let everything replicate. This way you will be able to replicate to different database. 2. Change the engine of tables that you don't want to replicate to BLACKHOLE. This way you will not storing the data of those tables. I hope I'm clear here! – mysql_user Aug 26 '15 at 7:37
  • 1
    I just noted @ronaldoMySQLDBA's comment about "not using BLACKHOLE" but here your sole purpose is to ignore that data. Unless there is a way I missed about mixing replicate-rewrite-db with replicate-do-table this is the only way I can think of. – mysql_user Aug 26 '15 at 7:40
  • There is another but messy way I wouldn't suggest but just sharing.. 1. Use replicate-rewrite-db 2. Drop tables that you don't need 3. Igonre replication errors generated due to those missing tables.. may be using pt-slave-restart or using slave-skip-errors... Which is even more riskier!! [Don't use this ;)] – mysql_user Aug 26 '15 at 7:43
  • hmmm..but both risky ..isn't ? – Ankit Kapoor Aug 26 '15 at 10:13

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.