[OT] Tool to export subset of database in presence of foreig

Apologies for the off-topic posting, but I’m having no luck whatsoever
tracking this down elsewhere, and I suspect that this group of people
must be able to point me in the right direction! I can’t believe that a
tool like this doesn’t already exist…

We have a rapidly growing MySQL/InnoDB database with over 100 tables and
some fairly complex inter-table relationships enforced via foreign key
constraints. For testing and development purposes we would like to dump
a subset of this database (e.g. all data from the last month).

Dumping the last month’s worth of data from any particular table is
easy. The trick is that we would like the dump to be consistent across
all the tables. Effectively what we would like to do is specify a “root”
query which selects a number of rows from one table and then have a
utility generate the “closure” of this query across all the other tables
to bring along what’s necessary to satisfy the foreign key constraints.

I’m not sure that I’ve explained that particularly well - please let me
know if I’ve made myself clear!

We could certainly write something like this, but I can’t believe that
we’re the first people to need this kind of thing, so there must already
be something like this “out there”.

Very many thanks in advance for any help you can offer,


On Jan 31, 2007, at 11:08 AM, Paul B. wrote:

Very many thanks in advance for any help you can offer,


Well, This is just a wild shot, but could you make a full copy of the
database and then IN THE COPY delete the top-level records that
wouldn’t be in your “root” set?

Otherwise, you may just be writing that special-purpose utility
yourself :wink:


Rob B. http://agileconsultingllc.com
[email protected]