Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
create table foo (id, val1, user_id,...)
create table bar (id, foo_id, val2, ...)
create table baz (id, bar_id, val3, ...)

select * from foo where user_id = 1;
select * from bar where id in (select id from foo where user_id = 1)
select * from baz where id in (for all the above foos)

How do I write a dump command which does the above?

share|improve this question
    
Your best bet is to just use CTAS queries to make copies of the tables containing only the data you need, then dump them. –  FreshPhilOfSO Mar 1 '13 at 11:33
    
Jailer promises to be able to do that: jailer.sourceforge.net/home.htm although I have never used it. –  a_horse_with_no_name Mar 1 '13 at 11:40
add comment

2 Answers

I posted an answer similar to this back on Aug 15, 2011 : Is it possible to mysqldump a subset of a database required to reproduce a query?

You will have to employ the use of --lock-tables

From that post, the OP wanted all data connected with this query

select table1.id, table1.level, table2.name, table2.level 
       from table1 join table2 on table1.id = table2.table1_id 
       join table3 on table3.id = table2.table3_id
       where table3.name in ('fee', 'fi', 'fo', 'fum'); 

I recommended doing these mysqldumps

mysqldump -u... -p... --where="name in ('fee','fi','fo','fum')" mydb table3 > table3.sql
mysqldump -u... -p... --lock-all-tables --where="table3_id in (select id from table3 where name in ('fee','fi','fo','fum'))" mydb table2 > table2.sql
mysqldump -u... -p... --lock-all-tables --where="id in (select table1_id from table2 where table3_id in (select id from table3 where name in ('fee','fi','fo','fum')))" mydb table1 > table1.sql

You then have to load them in a target DB server in reverse order:

mysql -u... -p... -D newdb < table1.sql
mysql -u... -p... -D newdb < table2.sql
mysql -u... -p... -D newdb < table3.sql

Now, let's look at your queries

select * from foo where user_id = 1;
select * from bar where id in (select id from foo where user_id = 1)
select * from baz where id in (for all the above foos)

You would dump as follows:

  • dump foo where user_id=1
  • dump bar where foo_id in (select id from foo where user_id = 1)
  • dump baz where bar_id in (select id from bar where foo_id in (select id from foo where user_id = 1))

Here the dumps

WHERE_CLAUSE="user_id=1"
mysqldump -u... -p... --where="${WHERE_CLAUSE}" mydb foo > foo_subset.sql
WHERE_CLAUSE="foo_id in (select id from foo where user_id = 1)"
mysqldump -u... -p... --lock-all-tables --where="${WHERE_CLAUSE}" mydb bar > bar_subset.sql
WHERE_CLAUSE="bar_id in (select id from bar where foo_id in"
WHERE_CLAUSE="${WHERE_CLAUSE} (select id from foo where user_id = 1))"
mysqldump -u... -p... --lock-all-tables --where="${WHERE_CLAUSE}" mydb baz > baz_subset.sql

Here is the reload order

mysql -u... -p... -D newdb < baz_subset.sql
mysql -u... -p... -D newdb < bar_subset.sql
mysql -u... -p... -D newdb < foo_subset.sql

Give it a Try !!!

share|improve this answer
add comment

You have --where option on mysqldump command :

mysqldump <your options> <your database> foo --where 'user_id = 1'

Max.

share|improve this answer
    
Note that if you use subquery (i didn't saw on my first read, sorry) you should LOCK your tables with option -x (mysqldump <your options> <your database> foo -x --where 'user_id = 1') –  mfouilleul Mar 1 '13 at 15:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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