How to dump database from mysql with deleting or corruption of sensitive data?

Question

I am using mysql. Some of the tables contain sensitive data, such as usernames, email addresses, etc. I want to reset the data, but with these columns in the table deleted or changed to some fake data. Is there any way to make this easy?

Answer 1

I use this approach:

• Copy the contents of confidential tables to a temporary table.
• Clear / encrypt sensitive columns.
• Provide the --ignore-table mysqldump.exe arguments to leave the original tables.

It stores foreign keys, and you can store columns that are not sensitive.

The first two actions are contained in the stored procedure, which I call before executing the dump. It looks something like this:

 BEGIN truncate table person_anonymous; insert into person_anonymous select * from person; update person_anonymous set Title=null, Initials=mid(md5(Initials),1,10), Midname=md5(Midname), Lastname=md5(Lastname), Comment=md5(Comment); END 

As you can see, I am not clearing the contents of the fields. Instead, I save the hash. That way, you can still see which lines have the same value, and between exports you can see if something has changed or not, and no one can read the actual values.

Answer 2

There is a tool called Jailer , which is commonly used to export a subset of a database. We use this at work to create a small test database from a production backup, while all confidential data is confused.

The GUI is a little rude, but Jailer is the best alternative I have found so far. You can simply deselect sensitive tables or columns and get a full copy of the rest. Jailer also supports data obfuscation during export - you can, for example, have an md5 hash of all usernames or change all email addresses to [e-mail].

There is a textbook to get started.

Answer 3

ProxySQL is a different approach.

Here is an article explaining how to confuse data with proxysql.

https://proxysql.com/blog/obfuscate-data-from-mysqldump