Restore databases via file copy

from the Artful MySQL Tips List

Database restoration via file copy is fundamentally unreliable, because there's no guarantee the file copy process has captured all required files. The most reliable way to restore databases is to run a script that has been generated by mysqldump from a MySQL instance of the same major MySQL version.

What if such a dump script isn't available? First read this. The following may be the best you can do:

1. Install the MySQL instance, but do not start it.

2. Edit my.ini|cnf to match the my.ini of the source installation. If the database file copies are from another installation, copy them preserving folder relations into datadir as set by my.ini|cnf.

3. In a command line window, start the MySQL server with this command:

mysqld --defaults-file="my.ini|cnf" --console --skip-grant-tables
While errors occur, fix them, stop and restart.

4. Stop the MySQL server.

5. Copy in the database folders and their contents, including ib_logfile* and ibdata*, but not including mysql> folder contents.

6. Restart the MySQL server as in #3.

7. Once everything is tested and working, stop the server again, make a backup copy of the new mysql folder, then copy in the old mysql folder contents.

8. Restart the server as in #3.

9. Run mysql_upgrade.

10. Stop the server, restart with:

mysqld --defaults-file="my.ini" --console --skip-grant-tables
and login via the mysql client with your password.

Last updated 15 Sep 2020

Return to the Artful MySQL Tips page