I have a mysql production database, with multiple tables. Tables are related with foreign key constraints. I want to make a small test database, by collecting/exporting only a small subset of data from the whole database (i.e. from all tables), so that foreign key constraints are preserved in the data subset.

How can I do that?

  • @dezso: This question is tagged [mysql], and the other is [postgresql] -- do the answers in that question still apply? – Jon Seigel Oct 5 '13 at 15:25
  • @JonSeigel based on what I see written in the docs, I would say yes. – dezso Oct 5 '13 at 15:38
  • this looks like what you look for jailer.sourceforge.net It exports consistent, referentially intact row-sets from relational databases. – user29226 Oct 10 '13 at 10:46

I doubt that there's a cookie-cutter approach, but the way I did it in the past went roughly as follows:

  1. Pick some critical tables that must be populated for proper testing, preferably which have many dependencies, e.g. OrderDetail.
  2. Create a new database with the same schema except no foreign key enforcement.
  3. Randomly pick some rows from the source database, e.g. top 50 ordered by something pseudo-random, and insert into the new database.
  4. Get all rows that they depend on and insert into the new database.
  5. Repeat until no more rows are inserted.
  6. Turn foreign key enforcement on.

It's slow and has many manual steps, but it worked for me.