Creating Production -> staging flow, how to skip email addresses when doing mysqldump? I want to run mysqldump or basically, anything that lets me easily export / import mysql db from one server into another.

Thing is: I want to export production data as staging / integration / demo data. That means, prior to importing the data, all user data must be gone. We had it one time, that the users got imported, and because the system ran a cron job, the server started sending out 200'000 emails. Can you imagine? yes yes, just disable the cronjob prior to import, but accidents happen. So importing user data and then deleting them is not an option.

Optimally I want to just ignore certain columns where there is an email, or replace that email with a test email string.

Is there something that helps me with that?

php is what we use for the application, so any php import export tool is a possibility as well.
Native mysqldump doesn't have a feature to ignore columns. But you can do this by exporting that specific tables as CSV then import it to your staging.

Example


DB: bhuvi
Total tables: 3
No. of tables I need to ignore some columns: 1




testtable has some secret data.

select * from testtable;
+------+----------+-----------------+
| id   | name     | email           |
+------+----------+-----------------+
|    1 | sqladmin | sql@admin.com   |
|    2 | bhuvi    | bhuvi@gmail.com |
|    3 | dba      | dba@xyz.com     |
|    4 | stack    | ex@stack.com    |
|    5 | user     | user@test.com   |
+------+----------+-----------------+


View for showing dummy emails

create view secret as ( select id, name , 'test@test.com' as email from testtable);
select * from secret;
+------+----------+---------------+
| id   | name     | email         |
+------+----------+---------------+
|    1 | sqladmin | test@test.com |
|    2 | bhuvi    | test@test.com |
|    3 | dba      | test@test.com |
|    4 | stack    | test@test.com |
|    5 | user     | test@test.com |
+------+----------+---------------+


Export the data as CSV from shell

mysql -u root -p bhuvi -e "select * from secret" | sed 's/\t/"|"/g;s/^/"/;s/$/"/;s/\n//g' &gt; testtable.csv


Dump all the tables with schema only

 mysqldump --no-data -u root -p bhuvi &gt; bhuvi.sql


Dump the table which are import as it is

DBTODUMP=bhuvi
SQL="SET group_concat_max_len = 10240;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name separator ' ')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema='${DBTODUMP}'"
SQL="${SQL} AND table_name NOT IN ('testtable')"
TBLIST=`mysql -uroot -ppass -AN -e"${SQL}"`
mysqldump -uroot -ppass ${DBTODUMP} ${TBLIST} &gt; bhuvi_tables.sql


Restore the DB:

Restore schema:

mysql -u root -p stage &lt; bhuvi.sql


Restore full tables:

mysql -u root -p stage &lt; bhuvi_tables.sql


Restore CSV file:

 mysqlimport --ignore-lines=1 --fields-terminated-by="|"  --fields-enclosed-by='"'  --verbose --local -u root -p stage testtable.csv


Check the data:

mysql&gt; select * from testtable;
+------+----------+---------------+
| id   | name     | email         |
+------+----------+---------------+
|    1 | sqladmin | test@test.com |
|    2 | bhuvi    | test@test.com |
|    3 | dba      | test@test.com |
|    4 | stack    | test@test.com |
|    5 | user     | test@test.com |
+------+----------+---------------+


Or simple method: Purge the email column after restore

udpate testtable set email='test@test.com'