14

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, 2013 at 16:59
  • @swasheck, sorry about that, updated the question for clarity
    – James McMahon
    Apr 19, 2013 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, 2013 at 17:02
  • ...Also make sure you have a way to reconcile the migrated data to ensure that everything migrated correctly.
    – FrustratedWithFormsDesigner
    Apr 19, 2013 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, 2013 at 22:03

2 Answers 2

Reset to default
11

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, 2013 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, 2013 at 21:10

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