Creating Production -> staging flow, how to skip email addresses when doing mysqldump?
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'