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.

  • 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? – Patrick Mevzek Dec 28 '17 at 17:56
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 | 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' > 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 | 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'
  • 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 '17 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 , 'test@test.com' as email from testtable" > | sed 's/\t/"|"/g;s/^/"/;s/$/"/;s/\n//g' > testtable.csv – Bhuvanesh Dec 31 '17 at 4:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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