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 '[email protected]'

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?

2
  • It would be nice if you could segregate the email addresses before you export.. but if you can't, try this regex: regular-expressions.info/email.html
    – ethrbunny
    Feb 7, 2014 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 7, 2014 at 13:37

2 Answers 2

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.');
1
  • 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 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 7, 2014 at 19:40
0

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

Thanks to anubhava.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .