Take the 2-minute tour ×
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.

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
    
@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
add comment

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

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer

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
add comment

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