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.

Colin 't Hart
  • 9,323
  • 15
  • 35
  • 43
simplifiedDB
  • 669
  • 6
  • 17
  • 36
  • Please don't use BLACKHOLE storage engine : http://dba.stackexchange.com/questions/73502/is-it-possible-to-recover-data-from-a-blackhole-table/73521#73521 – RolandoMySQLDBA Aug 25 '15 at 09:48
  • im using innodb engine...@RolandoMySQLDBA i expect a solution from you , i know you can suggest me something – simplifiedDB Aug 26 '15 at 07:19
  • Have you tries ignoring other table? https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_replicate-wild-ignore-table – Jehad Keriaki Sep 16 '16 at 03:04

1 Answers1

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;

mysql_user
  • 1,942
  • 12
  • 9
  • Sir , im using innodb.. – simplifiedDB Aug 26 '15 at 07: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 07: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 07: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 07:43
  • hmmm..but both risky ..isn't ? – simplifiedDB Aug 26 '15 at 10:13
  • You don't want to replicate tables but with available situation this is not possible. So you're replicating all tables and discarding data of some (which you don't need).

    When you set BLACKHOLE engine on slave, you don't store data for those tables. This is not risky if you really intend not to keep that data!!

     – mysql_user Aug 26 '15 at 10:42
  • okay !! but this is not a solution..its a work around – simplifiedDB Aug 31 '15 at 11:23
  • ...because what you're asking is not allowed in MySQL, you need to do a, "work-around". Until MySQL supports this request I'm afraid this work-around is the solution for you. – mysql_user Sep 01 '15 at 08:11
  • yeah i do understand this..thank you for your support mysql_user – simplifiedDB Sep 04 '15 at 10:43