How to restore MySQL database on Windows?

By | February 21, 2018

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:

  1. 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.
  2. 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.
  3. 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”.
  4. Now go to Control Panel > Administrative Tools > Services > MySQLXY (XY represents the version) and stop the service.
  5. 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.
  6. 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.
  7. Now start the MySQL service at Control Panel > Administrative Tools > Services > select MySQLXY (XY represents the version) > click Start link
  8. Once the MySQL service start, use mysql client (command line or phpmyadmin) to verify that the database is restored
  9. 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.
  10. 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.

Vishal Monpara is a full stack Solution Developer/Architect with 13 years of experience primarily using Microsoft stack. He is currently working in Retail industry and moving 1’s and 0’s from geographically dispersed hard disks to geographically dispersed user leveraging geographically dispersed team members.

2 thoughts on “How to restore MySQL database on Windows?

  1. Vishal Monpara Post author

    Hello Sathvik,

    Out of the given steps, if you can share where you were stuck and what kind of error you encountered, I can help you.

    Regards
    Vishal Monpara

  2. sathvik

    i did the same thing . intially i operated mysql on linux . i collected .ibd from there and copy pasted the same in windows bases . but when i am viewing the databases from mysql workbench no tables are being shown . can u please help me ??

Leave a Reply

Your email address will not be published. Required fields are marked *