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? This may be the best you can do:

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

2. Edit my.ini to match the my.ini of the source installation.

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

mysqld --defaults-file="<path_to_my.ini>my.ini" --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="<path_to_my.ini>my.ini" --console –-skip-grant-tables

and login via the mysql client with your password.

Return to the Artful MySQL Tips page