How can I replicate some tables without transferring the entire log?

Question

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?

Answer 1

For something specialized like this, a common approach is to copy the rows that you want to move into a temp table/database (or equiv buffer) and then have replication take it from there. The industry term for this is ETL (Extract, Transform, Load). The intermediate/temp/buffer gives you an opportunity to manipulate (Transform step) the data before it ships-out (Load step). It can be a little tedious but guarantees a consistent result.

Answer 2

If your architecture permits you can try a reverse approach using the replicate-ignore-table only for your private tables

replicate-ignore-table=<database>.pvt_table_name
replicate-ignore-table=<database>.other_pvt_table_name
...

combining a behaviour like this:

SET sql_log_bin = 0;

INSERT INTO pvt_table_name () VALUES(); -- or UPDATES

SET sql_log_bin = 1;

A more managed way is to create your own procedure so:

Write you PROCEDURE

DELIMITER |
CREATE PROCEDURE write_my_private_data (`id` INT, `private_data` VARCHAR(255))
BEGIN
  SET SESSION SQL_LOG_BIN = 0;
  INSERT INTO `pvt_table_name` (`id`, `private_data`) VALUES (id, private_data);
  SET SESSION SQL_LOG_BIN = 1;
END
|
DELIMITER ;

And replace into your software your:

INSERT INTO `pvt_table_name` (`id`, `private_data`) VALUES (id, private_data);

With the:

CALL write_my_private_data (1,'privateData');

See the sql_log_bin to turn off logging for the current session

This condition is true only if you are sure that there is a single point of insert/update (your software with the dynamic declaration of sql_log_bin), on the contrary this condition fails if there may also be interventions by third parties, such as manual insert direct on the table.