3

We would like access to accurate MySQL backups for debugging, without having personal information (PII) in the backups.

Is it possible to run UPDATEs on the replicated database (ie. replacing text columns with Lorem Ipsum) without breaking replication?

(Note: The schema allows us to identify newly updated rows very easily - meaning it is trivial for us to identify new PII quickly)

2

Yes with many caveats- I have literally this setup to expose Wikipedia database in public, and I would not recommend it unless you do not have an alternative options. We use triggers that update/blank fields that are private, replication filters to avoid full tables/databases, and views to limit certain per-row accesses.

Problems we faced:

  • Statement based replication is very flexible, but if you run certain writes based on existing data (e.g. INSERT...SELECT, updates based on modified columns), it will either break replication or create a data drift. You should limit application writes by inserts and updates by fields you are not going to modify-but it is not possible in most cases to control all possibilities except on simple models (insert-only writes, for example). The breakage can be very subtle but it may happen over time if the application is minimally complex.
  • Row based replication is more accurate (because you get the final state), but it does not trigger the triggers. We started using MariaDB 10.1 to have replica-only triggers to be able to do that (thus solving data drift), but we still have issues due to row based replication have limitation in terms of supporting different schema on master and replica.
  • Triggers and filters make things difficult to manage and more room for errors
  • If possible, I would suggest not having real time replication, but using dumps so you can filter on query time (in our cases it is not possible due to the size of the database and requiring real time from some usages).
  • Because you have different data, running pt-table-checksum and other comparing tools is not really possible (to check for data discrepancies)

When we have the resources, we will likely move to a custom filtering automation that analyzes the row based binary protocol in pseudo real-time and performs more complex filtering on application layer.

1

Yes, you can UPDATE replicated database without breaking replication but you have to evaluate the following:

  1. You can not update PRIMARY KEY fields.
  2. You can not update fields used in any modification WHERE in master: Example:

In Master:

DELETE table1 WHERE text_field='abc'

In SLAVE:

YOU CAN NOT UPDATE text_field

0

A thought...

Move PII info out of the current database into a separate one. Then binlog_ignore_db to avoid replicating it. (Or you could filter on the Slave or ...)

Even without your current requirement, this "isolation" may be a good idea for security.

This has the flaw that the JOINs to get to such info are broken.

Another thought...

Don't access PII via JOINs, use Functions. Then you have a single location to stop or scramble PII data.

  • Thanks Rick, it's an interesting thought. I guess the problem is that 80% of the database is PII - in one way or another. It's a real challenge - because given the nature of the data even the meta-data could be used to correlate identity - something we're grappling with. But we couldn't both shard PII from the database - and - have a reliable beta/test/debug dataset... I don't think. – Jim Morrison Jun 14 '18 at 10:01
  • You do have a tough problem. I have often pondered how to anonymize PII data without either leaving correlations or losing correlations that are important to analysis/indexing/distributions/etc. – Rick James Jun 16 '18 at 17:26

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.