5

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.

1
  • If you ran a second instance of MySQL on port 3307, you don't need it to have a full copy of the data. You just need the binary logs in it. You could make all the tables in the 3307 instance use the BLACKHOLE storage engine. That is already in my answer. You could set the expire_logs_days for 3307 instance to 1 or 2 to limit the amount of binlogs. Dec 4 '14 at 16:07
4

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

1

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.

1

As you stated you cannot prevent events getting written to the binlogs with replicate-do-tables, however that will determine what gets written to the slaves binlogs if you have log-slave-updates on.

Consider setting up an intermediate slave on the same trusted machine as your private master that runs for the sole purpose of binlogs filtering, then have your "public" database replicate from that.

If you worried about the 'private' data being written to the binlog even that environment keep in mind the file system permissions restrict read access to just the mysql system user. If you're worried about that account getting compromised so they can read your binlogs, keep in mind at that point you have larger problems and they can just grab the entire datadirectory.

0

You may want to look at MySQL::Replication - Decentralised, peer-to-peer, multi-master MySQL replication.

[ While it is Perl, it is a standalone application. Not sure if it is still being maintained. ]

Simply put, it allows selective replication such as:

  • replicate only some tables
  • only some columns or rows
  • Star-schema node replication

It does quite a bit out of the box; being a separate application, you can incorporate any type of filtering you like without needing to restart your database instances. And being Perl, you can easily hack at it.

-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.

2
  • But I would like to have the public data available on the slave as soon as it is updated on the master. I don't know of a way to have the ETL happen automatically and in real time.
    – chris
    Mar 19 '13 at 15:22
  • Aah. Good point. You could use a trigger to update the buffer table, but that starts to get pretty ugly.
    – TimG
    Mar 19 '13 at 16:22

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.