Re: Restoring old database

[Peter Brawley]

Don't try to jump from 5.1 to 5.7. As the manual says, you need to do it stepwise: 5.1 -> 5.5 -> 5.6 -> 5.7.

So you need to reinstall 5.1, make sure it's using the settings that were in place before you installed 5.7, ensure data is intact, then run mysqldump, eg ...

mysqldump –uUSR –pPWD –K –E –A –R –f >some_backup_dir/mybackup.sql

Then for each upgrade step, install the version, load the dump file script into the new installation instead of trying to reuse existing data files and folders. The installer often forces this issue by ignoring the current defaults-file and datadir settings, thus hiding existing data from the new installation.

All major versions and some minor ones change table structures in the mysql database; the new version won’t run correctly, or won’t run at all, until these changes are written to your mysql database. To get this done, restart MySQL with --skip-grant-tables to disable privilege checking, then run mysql_upgrade. Now load the dump script in the Mysql client with

source c:/backup/mysqlbackup.sql

Then restart MySQL without --skip-grant-tables, check the data thorughly to ensure all is ok, then on to the next version, starting with a new mysqldump &c.

Seems a long process? Yes. But shortcuts introduce unreliability.



Edited 1 time(s). Last edit at 04/09/2016 12:32PM by Peter Brawley.