Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top


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.


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'


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?

share|improve this question
It would be nice if you could segregate the email addresses before you export.. but if you can't, try this regex: – ethrbunny Feb 7 '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 7 '14 at 13:37

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.');
share|improve this answer
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 '14 at 19:40

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.