How to take a database dump for selective set of values in few tables? 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?
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 &gt; 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 &gt; 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 &gt; table1.sql


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

mysql -u... -p... -D newdb &lt; table1.sql
mysql -u... -p... -D newdb &lt; table2.sql
mysql -u... -p... -D newdb &lt; 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 &gt; 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 &gt; 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 &gt; baz_subset.sql


Here is the reload order

mysql -u... -p... -D newdb &lt; baz_subset.sql
mysql -u... -p... -D newdb &lt; bar_subset.sql
mysql -u... -p... -D newdb &lt; foo_subset.sql


Give it a Try !!!