0

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.

1
  • Did you thought about creating a view on top of your data but just changing the emails you need to change, and then dump this view content to import somewhere else instead of dumping the underlying tables? Dec 28, 2017 at 17:56

2 Answers 2

1
  1. Dump the database
  2. Pump it in the temporary database
  3. Empty columns you do not want to transfer
  4. Dump the temporary database
  5. Transfer it
1

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

enter image description here

testtable has some secret data.

select * from testtable;
+------+----------+-----------------+
| id   | name     | email           |
+------+----------+-----------------+
|    1 | sqladmin | [email protected]   |
|    2 | bhuvi    | [email protected] |
|    3 | dba      | [email protected]     |
|    4 | stack    | [email protected]    |
|    5 | user     | [email protected]   |
+------+----------+-----------------+

View for showing dummy emails

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

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' > testtable.csv

Dump all the tables with schema only

 mysqldump --no-data -u root -p bhuvi > 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} > bhuvi_tables.sql

Restore the DB:

Restore schema:

mysql -u root -p stage < bhuvi.sql

Restore full tables:

mysql -u root -p stage < 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> select * from testtable;
+------+----------+---------------+
| id   | name     | email         |
+------+----------+---------------+
|    1 | sqladmin | [email protected] |
|    2 | bhuvi    | [email protected] |
|    3 | dba      | [email protected] |
|    4 | stack    | [email protected] |
|    5 | user     | [email protected] |
+------+----------+---------------+

Or simple method: Purge the email column after restore

udpate testtable set email='[email protected]'
2
  • is there a trick to have the update basically run in one transaction? I am not an sql expert, but if I run the update in the same commit as the inserts, it might just work good enough, right? then again, if I have too many inserts, a huuuuge commit will probably break the server / run out of memory.
    – Toskan
    Dec 31, 2017 at 0:29
  • Try to update in chunks. like update 1000 rows -> commit then update next 1000 rows -> commit. Else try the steps which I answered. Dump all tables along with your email column table, Expert specific tables as CSV using views or use this mysql -u root -p dbname -e "select id, name , '[email protected]' as email from testtable" > | sed 's/\t/"|"/g;s/^/"/;s/$/"/;s/\n//g' > testtable.csv
    – TheDataGuy
    Dec 31, 2017 at 4:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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