We are in the process of preparing a data migration of some fairly big DB (200GB), InnoDB tables from our old server to the new servers. The data is very index heavy and trying to migrate the data via the preferred way, using mysqldump, takes a miserable 3-4 days. Most time is spent working the indexes. This also made us think about the data recovery in case we ever need to restore from a backup. 3-4 days is not really acceptable.
Q1. Does anyone have some good advice / experience regarding the migration / dumping / recovery of large, index heavy InnoDB databases?
We are now thinking about copying the binary data files from one server to the other. But I'm very unsure if this will be handled well using 2 different versions of the MySQL server.
Q2. Does anyone have experience regarding this type of migration. Did it go well?
Any tips are greatly appreciated!
Best Answer
If you are looking for a fastest way, you can try mysql_upgrade program. Copy the binaries and run the program to check and fix compatibility. Don't forget to backup first.