Situations may arise when you need to restore your MySQL database, including server crash, un-installing and re-installing MySQL Server, or salvaging lost or deleted data. Whatever be the cause, you can restore the database if you have backed up your MySQL Server data folder.
The data folder contains all the MySQL files (data file and log file) that you need to restore your database. In order to restore an InnoDB database on Windows, you must find ib* files (ibdata1, ib_logfile0, and ib_logfile1) in the data folder. And, if you need to restore a database running on the MyISAM engine, check the folder for .frm, .myi, and .myd files.
If you can't locate your MySQL Server data folder, try looking for it in its default location: "C:\ProgramData\MySQL\MySQL Server x.x\data".
Note: Here, “x.x” is the version of your MySQL Server you are using. So, replace it with the version number when locating the data folder.
Now let’s discuss in detail how you can restore a MySQL database from data folder in your Windows system.
Steps to Restore MySQL Database from Data Folder in Windows
Step 1: Install MySQL Server
Ensure that the MySQL Server version used to create the database is the same as that of the newly installed version. Or else, MySQL service will not start. You can download the same MySQL version for Windows from here.
Step 2: Start MySQL Service
During MySQL installation, set the root password for Windows and then start the MySQL service.
Step 3: Stop MySQL Service
Now you must stop the MySQL service. To do so, open Control Panel and click on Administrative Tools. From the screen that appears, right-click on Services and select MySQLx.x (replace x.x with the MySQL version) and stop the service.
Step 4: Copy Data Folder with Database Files to a New Data Folder
This is the major step that requires you to copy the backed up data folder containing the MySQL database files you want to restore to a new data folder.
In the data folder, locate the folder with the same name as the database you want to restore. For instance, to restore a db named as "mysql_db", look for the database files in the folder located in “C:\ProgramData\MySQL\MySQL Server x.x\data\mysql_db”. All the ib* files you need to restore an InnoDB database will reside here. Copy those files and move them to a local MySQL folder on your Windows system “C:\ProgramData\MySQL\MySQL Server x.x\data\” folder.
Note: When restoring InnoDB database files, ensure that the innodb_log_file_size has the same size as the original log file.
To restore a MyISAM database, copy the folder containing .frm and .myi files from the old data folder to the new one.
Tip: When trying to restore the db files on a development (or test) machine, you can choose to overwrite the existing ib* files. But, ensure to back up the files first at a secure place before overwriting them.
Step 5: Restart your MySQL Service
After copying the database files, restart the MySQL service and verify that your database is restored.
If you’re unable to restart the service, check the MySQL error log to find the reason behind it. You can find the error log in the file located at “C:\ProgramData\MySQL\MySQL Server x.x\data\MACHINE_NAME.err”.
What If You Fail to Restore MySQL Database?
Try to restore MyISAM tables. First identify the corrupt table by executing the following “mysqlcheck” statement:
Once you’ve found the corrupted table, run the REPAIR command as follows:
For detailed information on how to repair MySQL database and tables, refer to this link.
To recover an InnoDB db, run “innodb_force_recovery=1” to start MySQL. You may have to increase the value from 1–6 to start the server. But, exceeding the value from 3 involves data loss risk. A better alternative is to use a MySQL repair tool that helps restore both InnoDB and MyISAM database files with all the data intact.
Try Stellar Repair for MySQL software, designed for database administrators and other MySQL users to repair a severely corrupted database and restore it to its normal state, with no data loss.
Steps to Restore MySQL Database with Stellar Repair for MySQL Software
- Launch the MySQL repair tool from Stellar®. An “Instruction” message box opens in the main software interface. Click “OK” to proceed.
- On the “Select Data Folder” dialog box, choose the version of your MySQL Server. Next, click “Browse” to select the data folder and click “OK”.
- Select the data folder that contains the database files you need to repair, and then click “OK”.
- Once you’ve added the data folder, hit the “OK” button.
- A “Select Database” window appears with all the database files within the selected data folder. Click “Repair” to start repairing the files.
Note: By default, all the database files are selected in the “Selected Database” dialog box. However, you may choose specific files that you want to be repaired.
- Click “OK” when prompted that the repair process has been completed.
- A screen with the preview of repaired MySQL database and all its components you can restore gets displayed.
- To save the db, click “Save” from the “File” menu.
- Choose “MySQL” as the file format in which you need to save the file, enter the details to connect to a server instance, and then click “Save”.
- You will receive the following message once the repaired database is saved.
You can find the database at the exact location where your original database is stored.
Conclusion
This article explains how to restore MySQL database from the data folder on a Windows machine. It explains the step-wise instructions, which help you perform the db restore process quickly. But, if the manual steps to regain access to the MySQL database fails, using Stellar Repair for MySQL software may help.
7 min read
7 min read
7 min read
