MySQL Data Migration after Restructuring Database

Question

I work at a young business with an accounting C# application. This application stores and is fed huge amounts of data from a MySQL database, which has been running for at least a year and a half.

After getting many problems related to how to database was stuctured we decided to make a new database design taking into account all normalization standards. Many tables where merged into a single one and other tables where split into multiple.

Finally we decided on all the structure for our new database and are working on a new design for our C# app to interact with it. We created the new structure using MySQL Workbench.

Problem is we must first work on a solution to migrate all the data from the old stucture to the new one. We're dealing with around 200 Megabytes of data and we have around 3 months to release the new software version.

I know this isn't a simple question that could be answered by one person. What I'm looking for is advice on the topic. Should I write a custom script/application to do all the migration? In what language should I program the migration solution? We want this migration to happen as swiftly as possible so the system is down for a small amount of time.


Show source
| c#   | database   | mysql   | bigdata   | database-migration   2017-01-02 20:01 1 Answers

Answers ( 1 )

  1. 2017-01-02 21:01

    Two hundred megabytes is not vast. If you have a decent machine running your MySQL server software, you should be able to run the whole conversion overnight (or faster than that) once you have the conversion procedures worked out.

    That means you can run it over and over again on test copies of your old and new database until you get it right.

    You should probably extract yourself a small subset of your overall database for development and unit testing of the conversion. But beware: database subsets often omit oddball edge cases by mistake. Make sure you test the conversion on the whole database repeatedly, not just the day before go-live day. If you don't, you'll be sorry. Don't ask how I know this, ok?

    IF the conversion can be accomplished entirely with MySQL statements, that's probably the way to do it. Put them in a series of .sql files and run the files from the mysql command line interface.

    The statements I'm thinking of look sort of like this

      USE new_test_database;
      INSERT INTO  general_ledger
            VALUE  (datestamp, customer, from_acct, to_acct, amount, memo)
           SELECT  datestamp, customer, from, to, amount, 'Conversion from cash'
             FROM  old_database_copy.cash_ledger;
      INSERT INTO  general_ledger
            VALUE  (datestamp, customer, from_acct, to_acct, amount, memo)
           SELECT  datestamp, customer, from, to, amount, 'Conversion from credit'
             FROM  old_database_copy.credit_ledger;
    

    You get the idea: a bunch of SQL that populates your new tables with the information in your old tables.

    If you can write a bunch of SQL that will do the conversion completely, you can store it git or somewhere, test it repeatedly as often as you need to (with copies of the old and new database) until you're sure it works, and then run it one last time the night before go-live day.

    If you CAN'T do this in pure SQL, then it's pretty obvious that your shop should use C# to do it: that's what you know. Write yourselves one or more simple command-line C# programs that you can run to do the conversion. I suspect that will also be reasonably fast; fast enough to test repeatedly. If you have reasonably large machines (8g of RAM) you probably can slurp entire tables into C# data structures, manipulate them, and then write them out again.

    Notice a couple of things:

    1) if you're using InnoDB, you might want to create your new tables with MyISAM then convert them after the conversion is done. MyISAM isn't transactional, so it will do multirow insert operations more efficiently.

    2) You also might want to disable foreign key constraints during the bulk load, then try to re-enable them when all the data is in place. If you do things this way, plan on having a totally working conversion process a month before go-live, so you have time to correct any fk screwups.

    Do not overwrite your old production database. Put your new stuff in a new database. But you knew that.

◀ Go back