2

I need to do regulars SQL dump to save a database, but in the table "users" there is the columns "name" and "firstname" that must be anonymous (by changing the values with random string or else). I would like to know how could I dump my data and change the values of these columns at the same time?

This is for a MySQL database

Dharman
  • 30,962
  • 25
  • 85
  • 135
Gcpt
  • 51
  • 1
  • 1
  • 7
  • 2
    Why don't you copy that table to another temporary table, change the values, then take a dump of the temporary table. By the way, you should've anonymized the data before storing it in your database – rdas Apr 05 '19 at 14:53
  • 1
    Create views on all your base tables that obfuscate the data which must be changed. Then dump the views. – xQbert Apr 05 '19 at 19:40
  • 1
    @xQbert, Have you tried dumping a view with `mysqldump`? It dumps the view definition, not the data from the underlying tables. – Bill Karwin Apr 05 '19 at 19:48
  • 1
    *sigh* seemed like a creative way to handle it. – xQbert Apr 05 '19 at 19:50

1 Answers1

2

You can't do this with the mysqldump tool. It doesn't have any option for transforming the output, it only dumps the data that is in your database. So if you use mysqldump, your options are:

  • Store anonymized data and dump that data (as suggested by @DroidX86 in a comment above)
  • Get really good at sed and use it to filter the output of mysqldump. I am really good with sed, and I wouldn't try that.

So you may try to dump data without using mysqldump, so you have more options for custom transformations on the data.

You can write a script to SELECT ... FROM MyTable; which is basically what mysqldump does. But if you write your own tool, you can use expressions to anonymize the name and firstname instead of dumping the contents of those columns as-is.

However, coding your own replacement for mysqldump is harder than it sounds. Some developers have attempted it and gotten it spectacularly wrong. I've answered questions about it before:

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828