Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

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: regular-expressions.info/email.html –  ethrbunny Feb 7 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 at 13:37

1 Answer 1

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 at 19:40

Your Answer

 
discard

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.