How to take a database dump for selective set of values in few tables?

Question

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?

Answer 1

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 !!!

Answer 2

You have --where option on mysqldump command :

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

Max.