12

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.

Improve this question
6
  • 1
    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
  • 2
    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
  • 2
    Jailer claims to be able to do that: sourceforge.net/projects/jailer (I haven't used though) – a_horse_with_no_name Apr 19 '13 at 22:03
10

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 !!!

Improve this answer
1
  • 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
0

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.

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

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