user2868104 user2868104 - 3 months ago 18
MySQL Question

how to sqldump a subset of data from each table for an entire database in mysql

Let's say there is a production

DB
P
and a local
DB
L
. I would like to have a subset of the data from
P
and restore them in to
L
.

I know One can easily
sqldump
a subset of data from a table in
mysql
.
But what if it is a
DB
with, say, 50 tables? Can one just easily
sqldump
all the tables for the first, say, 1000, records?

As a bonus, assuming it is
star schema
, is it possible to use a major table, which connects to most other tables, and select the first 1000 records for that table and retrieve the subset of data that are foreign keyed for the 1000 records correspondingly?

Answer

I don't think there's an easy way to do this all at once. The only way to subset the data is with the -w option, but it will try to use the same WHERE condition for all tables. You could do it in two steps:

mysqldump -w 'id < 1000' dbname mastertable > mastertable.sql
mysqldump -w 'master_id < 1000' dbname child1 child2 child3 ... > children.sql

This assumes all the child tables use the same name for their foreign key.

You can simplify the second command to:

mysqldump --ignore-table=dbname.mastertable -w 'master_id < 1000' dbname > children.sql

if all the other tables in the database are children.

Comments