Restoring a MySQL Inno Database

Okay, I’m writting this as much for me as anyone else. Of course, if someone else can use it, please, save your heart and hair and use it. I know I had a minor heart attack, and then proceeded to pull out my hair as I thought I lost my database when I had my computer SSD die on me.

Now, I had good backups, but with MySQLs Inno DB, you can’t just copy files. So here is how you restore them, if you have them on a Windows machine.

1. Install the version of the DB you used

For me this was a very old 5.6.17. You may have to go to the archive installer page here: https://downloads.mysql.com/archives/installer/ to get your old version.

Once you install it, you should be good for the next step.

2. Stop the MySQL Windows Service

Press Win + R, type services.msc, and hit Enter. Scroll down to find your MySQL service (usually named MySQL56 or MySQL). Right-click it and choose Stop.

Alternatively, open Command Prompt as Administrator and run:

net stop MySQL56

Note: the name of your service will vary based upon which version you used.

You have to stop the service so you can overwrite some of the files.

3. Isolate the Clean Target Directory

Navigate to your new MySQL instance’s data directory. By default on Windows, this is located at: C:\ProgramData\MySQL\MySQL Server 5.6\data

Rename this data folder to data.default. This ensures you have a clean backup of the fresh install.

What if you can’t find this folder?

By default, MySQL on Windows stores its data in a hidden directory.

Before starting, open File Explorer, click View at the top menu, hover over Show, and ensure Hidden items is checked.

You will need this to access C:\ProgramData.

4. Copy Backup Files Into Place

Create a new, empty folder named data at C:\ProgramData\MySQL\MySQL Server 5.6\.

Copy all files and folders from your crashed server’s backup directory directly into this new data folder. Ensure you copy:

• The system folders (\mysql\, \performance_schema\)
• All individual database folders (e.g., \your_database_name\ ) that you are wanting to restore
• The core files: ibdata1, ib_logfile0, and ib_logfile1

Without copying all of those files into the new data folder you created, you cannot access the data.

5. Configure InnoDB for Recovery

Locate your configuration file, my.ini, which is typically found in C:\ProgramData\MySQL\MySQL Server 5.6\.

Open it with Notepad (run Notepad as Administrator so you can save changes). Find the [mysqld] section and add this line right underneath it:

[mysqld]
innodb_force_recovery = 1

This tells Windows to bypass strict transaction checks during the crash-recovery phase so the service doesn’t immediately crash or timeout.

6. Handling Log Files

Because you are doing a physical recovery using existing ib_logfile0 and ib_logfile1 files from a crashed server, the innodb_log_file_size variable in your new my.ini must exactly match the size of the log files you are moving over.

If the sizes do not match, MySQL 5.6 will either throw a fatal initialization error and refuse to start, or it will attempt to overwrite/ignore them, destroying the transaction logs required to safely recover your crashed data.

Step 6.1: Find the exact byte size of your backed-up log files

1. Go to your backup folder.
2. Right-click on ib_logfile0 and select Properties.
3. Look at the exact Size (not Size on disk).

Note: In a standard MySQL 5.6 installation on Windows, the default size is usually exactly 50,331,648 bytes (which is 48M). However, if your previous DBA changed it for performance reasons, it could be much larger (e.g., 256M, 512M, or 1G).

Step 6.2: Convert to Megabytes (M) or Gigabytes (G)

• If the size is 50,331,648 bytes, that equals 48M.
• If the size is 268,435,456 bytes, that equals 256M.
• If the size is 536,870,912 bytes, that equals 512M.
• If the size is 1,073,741,824 bytes, that equals 1G.

Step 6.3: Update your my.ini

Open your my.ini on the new Windows machine (remember to run Notepad as Administrator) and look for the innodb_log_file_size directive under the [mysqld] block. If it’s not there, add it right next to your recovery setting.

For example, if your file properties showed it was 256MB:

[mysqld]
innodb_force_recovery = 1
innodb_log_file_size = 256M

7. Start the Service and Monitor:

Open the services.msc console again, right-click your MySQL service, and click Start.

To see what is happening during recovery, open your data folder and look for the text file named [Your-PC-Name].err (e.g., DESKTOP-XXXX.err). Open this in Notepad to watch the InnoDB engine execute its crash recovery.

8. Verification & Final Cleanup

If the .err file concludes with messages indicating the server is ready for connections, your restore was successful.

1. Open your MySQL client application (like MySQL Workbench or Command Prompt via mysql -u root -p) and test reading data from your tables.
2. Remove Recovery Mode: Once you confirm your data is intact, stop the MySQL service one final time. Open my.ini, delete or comment out the innodb_force_recovery = 1 line, save the file, and restart the service normally.

Restoring a MySQL Inno Database was originally found on Access 2 Learn