Replicate some tables without transferring the entire log

I have a mysql database that contains some tables with private information, and some tables with public information.

I would like to replicate only the tables containing public information from one database to another, making sure that NO confidential information ever gets stored on the slave.

I know I can use the replicate-do-table to specify that only some tables are replicated, but my understanding is that the entire bin log is transferred to the slave.

Is there a way to ensure that only the public information is transferred to the slave?

I am very hesitant to add another copy of the database to an existing server - I just don't think the existing server has the available capacity, in either RAM or CPU.

The only way to filter on one DB server is to run multiple instances of MySQL on one DB Server.

SERVER DB1

Port 3306 would be your normal database instance for your application

Port 3307 would be a slave of Port 3306

There are a few things you need to do with the MySQL instance running on DB1

• Enable the following in my.cnf for

• log-bin=mysql-bin

• log-slave-updates

• replicate-do-table=db1.public_tb1

• replicate-do-table=db2.public_tb2

• ...

• replicate-do-table=dbn.public_tbn

As an option, convert all tables in DB1 3307 to the BLACKHOLE storage engine.

That way, DB1 3307 only has binlogs with information. No actual data.

SERVER DB2

Setup MySQL instance and make it Slave from the 3307 instance of DB1. Why is that good ?

Because the binlogs in the DB1 3307 instance should only contain the public info. Thus, all slaves from DB1 3307 will only see public info.

CAVEAT

Please see my other posts on how to use BLACKHOLE tables in replication

• Apr 18, 2013 : Single slave - multiple master MySQL replication

• Feb 03, 2012 : One slave, multiple masters MySql

• Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns?

• May 16, 2011 : Is Multi Master Single Slave possible in mySQL DB?

• Mar 11, 2011 : MySQL in star topology

StackExchange Database Administrators Q#37015, answer score: 7