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. Join them; it only takes a minute:

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

Is there a tool to export data from selected rows in a table with all data stored in other tables in other tables linked by relational design?

The purpose is to ease migrations of bits of data between servers for adhoc migrations. I am looking specifically for a tool for MySQL InnoDB with defined foreign keys.

share|improve this question

closed as off-topic by Phil, Paul White, RolandoMySQLDBA, dezso, bluefeet Jan 26 '15 at 14:28

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – Phil, Paul White, RolandoMySQLDBA, dezso, bluefeet
If this question can be reworded to fit the rules in the help center, please edit the question.

are you asking to export the relationships themselves or the related data? also, which rdbms? – swasheck Apr 19 '13 at 16:59
@swasheck, sorry about that, updated the question for clarity – James McMahon Apr 19 '13 at 17:02
Having written a program that does this (for SQL Server), I can tell you it's not for the faint of heart -- I strongly recommend using 3rd-party software to do this if it's available. – Jon Seigel Apr 19 '13 at 17:02
...Also make sure you have a way to reconcile the migrated data to ensure that everything migrated correctly. – FrustratedWithFormsDesigner Apr 19 '13 at 17:40
Jailer claims to be able to do that: (I haven't used though) – a_horse_with_no_name Apr 19 '13 at 22:03
up vote 2 down vote accepted

In reality, you could just script it yourself using mysqldump.

Here my posts using the --where option for mysqldump:

With a good review of your tables and some SQL elbow grease, you can script the dump for any table and its child records with the corresponding foreign key references.

Give it a Try !!!

share|improve this answer
Ultimately I ended writing some custom logic to do the serialization and deserialization of the objects. I think custom scripting is probably the way to go. – James McMahon Aug 18 '13 at 1:31

This would be nigh impossible to accomplish in MySQL unless all tables had necessary FOREIGN KEY constraints defined (not guaranteed at all, considering MyISAM doesn't support them).

If all tables DID have the FK's, it would still be quite a task and I don't know of a tool that would export a subset.

I know Navicat will attempt to reverse engineer the relations, but I don't think it will dump a subset of the data.

share|improve this answer
To your first point, I am using InnoDB with defined relational constraints – James McMahon Apr 19 '13 at 21:10

protected by RolandoMySQLDBA Aug 5 '15 at 15:16

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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