Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This question already has an answer here:

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?

share|improve this question

marked as duplicate by dezso, Paul White, bluefeet, RolandoMySQLDBA, Marian Oct 10 '13 at 11:10

This question was marked as an exact duplicate of an existing question.

@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 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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.