What is the best way to migrate my old database to the new schema ?

[tangara goh]

I am new in 'data-migration' and I think it is best seek out advice with the experts before I get my hands dirty.

Basically, I have a database which has a few thousands of rows and just a single table.

With the new schema which has a many to many relationship(many tables), I would need to migrate the data to my new schema.

Can I know if there is/are a free tool(s) out there to migrate the data ?

I will be using MYSQL server 8.0.14.

Running on Windows 10 OS.

Any opinions are most welcomed.

Tks.

[Jeanne Boyarsky]

That's not a lot of data so whatever you choose, it'll be quick.

Since you are migrating between schemas, you need to express how the data should change. While I imagine that data warehouse tools can do this, it seems like overkill for your scenario. I recommend just writing a program to do it.

[Mike London]

There are DB tools out there that will let you drag and drop tables between schemas. Navicat, for example, will let you do that.

Otherwise, for data migration, especially if I know it may need to run more than once, I just whip up a little JDBC logic and even schedule it if necessary.

HTH

- mike

[Tim Holloway]

Well, one approach would be to use an ETL tool like Talend or Pentaho DI (Kettle). For myself, I'd probably dump the single table using mysqldump and load it into the new schema, use a set of sql commands to plunder it into loading the new tables, then drop the copy of the original single table when everything's been place in its new home. For best results, don't index or set foreign keys until AFTER all the new tables have been loaded.