Copying mysql data from one db to another with a where clause for a subset of tables

Question

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?

Answer 1

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