Mysql data export tool

Question

so, say I have a SQL(Mysql) database containing 4 tables {A, B, C, D}

and I want to create a testing database which contains a subset of data from the first database (both in time and type).

so for example: "I want to create a new (identical in structure) database containing all of the data for user "bob" for the last two weeks."

the naive approach is to dump two weeks of data from the first database, use vagrant / chef to spin up a new empty database and import the dump data.

however, this does not work as each table has foreign keys with each other. so, if I have two weeks of data of "A", it might rely on a year old data from "D".

My current solution is to use the data layer of my java application load the data in to memory and then inserted it into the database. however, this is not sustainable / scalable.

so, in a roundabout way, my question is, does anyone know of any tools or tricks to migrate a "complete" set of data from one database to another considering a time period of 1 table and including all other related data from the other tables as well?

any suggestions would be fantastic :)

Removing the chef tag as this does not appear to be related to Chef — coderanger
– coderanger, Commented Apr 5, 2017 at 16:45

Mikhail Antonov · Accepted Answer · 2017-04-05 08:09:02Z

1

Try your "naive approach", but SET FOREIGN_KEY_CHECKS=0; first, then run your backup queries, then SET FOREIGN_KEY_CHECKS=1;

answered Apr 5, 2017 at 8:09

Mikhail Antonov

1,3873 gold badges24 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luke_P Over a year ago

interesting, but I ideally want this tool to be able to work without knowing what data is contained in each table.

Devart · Accepted Answer · 2017-04-05 08:35:22Z

1

There is the way to recreate similar database with part of data, not so simple, but can be used:

Create new database (only schema), for example using buckup or schema comparer tool.
The next step is to copy table data. You could do it with a help of data comparer tool (look at schema comparer link). Select tables you need and check record data to synchronize.

answered Apr 5, 2017 at 8:35

Devart

120k23 gold badges172 silver badges189 bronze badges

Collectives™ on Stack Overflow

Mysql data export tool

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related