Mysql – Copying thesql data from one db to another with a where clause for a subset of tables

MySQLmysqldump

I'd like to be able to move mysql data from one db to another. I only want to move records where account_id=1234 except for tables like account which does not have an account_id column.

Is there any possible way to do this?

Best Answer

By "move" do you mean "copy"? That is, are you leaving the rows behind?

Two different machines

mysqldump has options to specify particular a database and list of tables. It also has an optional WHERE clause. So...

mysqldump -h src -w "account_id=1234" -d db tbl1 tbl2 > some_tables.sql
mysqldump -h src -d db tbl3 tbl4 > other_tables.sql

(Please verify the syntax.)

Then load those two files into some other place (after doing CREATE DATABASE newdb):

mysql -h dest newdb <some_tables.sql
mysql -h dest newdb <other_tables.sql

Same machine

CREATE DATABASE newdb;
CREATE TABLE newdb.tbl1 LIKE db.tbl1;
INSERT INTO  newdb.tbl1 SELECT * FROM db.tbl1 WHERE account_id=1234;
 ditto for tbl2, etc
CREATE TABLE newdb.tbl3 LIKE db.tbl3;
INSERT INTO  newdb.tbl3 SELECT * FROM db.tbl3;
 ditto for tbl4, etc
Related Question