Methods for merging or moving an active MySql database

Question

I am upgrading a set of tables, adding and shifting things around. I copy all my data into the new format.... then it automatically becomes old as new data is constantly being added and changed in the old system.

Without shutting down the old system for too long, is there a way to merge the two systems when data is still going into the old?

Answer 1

If the transaction log (a log of queries run) is on, you can dump the old database, then after that finishes, replay the statements from the transaction log on the new db, from the time you started the db dump. This can be done with a fairly simple bash or powershell script.

Once the replays 'catch up' on the new db, you can temporarily prevent application access to the old db, replay any remaining statements and switch your app to the new db.

Have a look at the mysql docs on point in time recovery (PITR).