MySQL Conditional Replication based on Records

Question

Hey there,

Our organization has a very large database which Replicates to separate global servers.

In our database some tables contain data exclusive to a certain region thus providing privileges to exclusive information to users who are within those regions.

example (

transactions_australia, transactions_dubai

)

Contrary to the regional tables, we have global tables which contain information from all regions in the world.

example (

person, address, contacts

)

The issue we face is that when giving out users privileges to this information we present a security risk we are trying to overcome.

The head programmer suggests that we duplicate data which resides in global tables via queries which can extract only that specific regions data into separate database and preform this duplication every time we need to run reports or extract data.

I do not fully agree with this approach as the time to duplicate records of 11+ tables (which contain hundreds of thousands of records) will take far to long, and also the issue of data integrity and synchronization is to risky.

I am searching for an alternative to this duplication approach.

Is there a way of setting up a conditional replication to certain servers?

For example.

If a person record is updated and that person belongs to the Australia region then the record would be updated on the master server and replication would take place only in the corresponding server.

Or are there alternatives to this?

I have briefly looked into using views however I do not think they will provide that level of extrapolation from the main dataset we are after.

Answer 1

MySQL offers selective replication, but the smallest level of selection is a table. This is because, for the most part, a table can almost always stand alone from a purely database point-of-view. Trying to limit replication by particular rows would severely compromise data integrity and be a database administration nightmare: MySQL would need more information about the tables' use than it is designed to deal with.

In the chapter on replication in the docs, the system settings are described to control replication of selective tables or databases.