How to Restore MySQL Database on Windows?

I had to restore the MySQL InnoDB database on Windows machine which took quite a bit of time as I did not know what to do and required lot of research as I bumped into multiple issues. Here are the steps if you ever wanted to restore the MySQL database.

Prerequisites

Full database backup (*.frm, *.ibd files)
Other MySQL configuration files

Steps:

Check the MySQL version on which the database was created. If the version does not match, MySQL service will not start. If you have access to the server, check out the MySQL version using command prompt or GUI tool. If you don’t have access to the server and you have the full backup of MySQL, check if the file “mysql_upgrade_info” exists within the backup. Open this file in text editor. This file contains only 1 line which is the MySQL version number.

Download the exact MySQL version from https://downloads.mysql.com/archives/installer/ for Windows. Install the MySQL. Only MySQL Server is needed. Other components are optional.

Go through the installation wizard, create the root password and start the service. For my 64 bit Windows 10 machine, the installation path is “C:\Program Files\MySQL\MySQL Server X.Y\” and the MySQL database file path is “C:\ProgramData\MySQL\MySQL Server X.Y\data”. Please note that the installation path is “Program Files” and data path is “ProgramData”.

Now go to Control Panel > Administrative Tools > Services > MySQLXY (XY represents the version) and stop the service.

Copy the folder which contains the MySQL database files to “C:\ProgramData\MySQL\MySQL Server X.Y\data”. For example, if my database name is “pramukh_cms”, your folder structure would look like “C:\ProgramData\MySQL\MySQL Server X.Y\data\pramukh_cms\” and all the *frm and *ibd files will reside here.

Copy all ib* (ib_buffer_pool, ib_logfile0, iblogfile1, ibdata1 etc) files from your backup to “C:\ProgramData\MySQL\MySQL Server X.Y\data\” folder. If this is a development machine and you don’t need existing database’s log files, then you can simply overwrite the ib* files. If you need the existing ib* files, you need to backup the existing files at safe place.

Now start the MySQL service at Control Panel > Administrative Tools > Services > select MySQLXY (XY represents the version) > click Start link

Once the MySQL service start, use mysql client (command line or phpmyadmin) to verify that the database is restored

If MySQL service does not start, you may find the log at “C:\ProgramData\MySQL\MySQL Server X.Y\data\MACHINE_NAME.err” file and you may find the root cause of why the MySQL service does not start.

Alternatively, you may try to use command prompt to start the service using following command and it will print all the logs. Depending on the error log, you may find answer online

"C:\ProgramData\MySQL\MySQL Server X.Y\bin\mysqld.exe" --console

Related Errors

[ERROR] InnoDB: Upgrade after a crash is not supported.