Knowledge.ToString()

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.

Share

Comments

6 responses to “How to Restore MySQL Database on Windows?”

  1. saarema Avatar
    saarema

    copy also mysql.ibd

  2. John Avatar
    John

    Your a Savior my friend. Thanks

  3. saad fazelpour Avatar
    saad fazelpour

    I followed all the steps until step 7. I restarted service but I don’t see my databases. Do I have to do something else?

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Saad,

      I had the same thing and had to keep following the steps multiple times.

      Regards,
      Vishal Monpara

  4. sathvik Avatar
    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 ??

    1. Vishal Monpara Avatar
      Vishal Monpara

      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

Leave a Reply

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