0

Synopsis

I would like to import production data into the development environment so testing can be done in an almost accurate way. Of course I need to invalidate e-mail addresses as I don't want e-mails reaching the end-user.

Source

Just a side note - my expression deliberately matches ['] as part of the query syntax

So far I have the following using grep which was to test my expression.

$ cat site_backup.sql | egrep -io '\w+([._-]\w*)@\w+([._-]\w*)\.\w{2,4}'

This is printing the e-mail addresses, but of course I need to replace using sed:

$ sed -r -e "s/'(\w+)([._-]\w*)@(\w+)([._-]\w*)(\.\w{2,4})'/\1\2@invalid.\3\4\5/g" -i site_backup.sql

Works fine on a small percentage of e-mail addresses; I've noticed a pattern in what was not replaced 'word@word.ex.t'

Conclusion

After appending another expression for this, it has no affect. I still haven't looked into awk which I know is very powerful so i'm not ignorant to it, i'm hoping someone could help.

My goal is simple, I am using mysqldump to export a production database, I then wish to replace all e-mail addresses (or as many as reasonably possible) before importing the data in to the development environment. Am I possibly doing this from an awkward angle?

Ash
  • 471
  • 1
  • 4
  • 14
  • It would be nice if you could segregate the email addresses before you export.. but if you can't, try this regex: http://www.regular-expressions.info/email.html – ethrbunny Feb 07 '14 at 12:39
  • You could just import it (possibly into a dummy) first and then change the emails as it simplifies the action since you now have column access. Also, this is off topic for serverfault. – Grumpy Feb 07 '14 at 13:37

2 Answers2

2

Import your dump into a new database and use a query like this:

UPDATE table_name SET email_field_name = REPLACE(email_field_name, '@', '@invalid.');
Dennis Williamson
  • 62,149
  • 16
  • 116
  • 151
  • I don't think this is appropriate because there are a number of tables that contain e-mail addresses, more-so orders, customers and newsletter subscribers. Also I was hoping I could use [tag:bash] for this. Looking at the sql dump It's a file of characters, I presume although long-winded my regex needs to be fine-tuned to match an *expression* similar to a quoted string; containing exactly one @ symbol followed by a further word. I don't believe this task is for the database. – Ash Feb 07 '14 at 19:40
0

I found a good solution on stackoverflow: https://stackoverflow.com/a/18355644/1690631

Thanks to anubhava.

Community
  • 1
BntMrx
  • 301
  • 3
  • 10