Restoring MySQL database becomes essential in various scenarios, like server crash, uninstalling and reinstalling MySQL Server, recovering lost or corrupt data, etc. Whatever be the situation, you can easily restore the database if you have backed up your MySQL Server data folder. The data folder contains all the files (data files and log files) that are required to restore the database.
The approach to restore database from data folder for InnoDB and MyISAM storage engines is different. In order to restore an InnoDB database on Windows, you must find ib* files (ibdata1, ib_logfile0, and ib_logfile1) in the data folder. On the other hand, 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 searching for it in its default location, which is: C:\ProgramData\MySQL\MySQL Server x.x\data. Here, replace ‘x.x’ with the version of your MySQL Server.
Steps to Restore MySQL Database from Data Folder in Windows
Now let’s discuss in detail how to restore MySQL database from data folder on a Windows system.
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. 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
Next, you need to 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 your MySQL version) and stop the service.
Step 4: Copy Data Folder with Database Files to a New Data Folder
Now, you need 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 database named - "mysql_db", look for the database files in the folder located in C:\ProgramData\MySQL\MySQL Server x.x\data\mysql_db. Here, you will find all the ib* files required to restore the InnoDB database. Copy those files and move them to a local MySQL folder on your Windows system.
Note: When restoring InnoDB database files, ensure that the innodb_log_file_size has the same size as the original log file.
To restore 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 database files on a development (or test) machine, you can choose to overwrite the existing ib* files. But, back up the files first at a secure place before overwriting them.
Step 5: Restart the 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?
If you fail to restore the database, then there are chances that there is corruption in tables or database. In such a case, first identify the corrupt tables by executing the CHECK TABLE statement (see the below example).
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option: {
FOR UPGRADE
| QUIC K
| FAST
| MEDIUM
| EXTENDED
| CHANGED
}
Once you’ve found the corrupted tables, you can repair the tables by running the REPAIR TABLE command as given below:
For detailed information, read our guide on How to Repair MySQL Databases and Tables.
A better alternative is to use Stellar Repair for MySQL. This professional MySQL repair tool is designed for database administrators and MySQL users to help them repair both InnoDB and MyISAM database files with all the data intact. After repair, it saves the repaired data to a new database file without any data loss. It also supports recovery of specific tables, including partition tables. This MySQL repair tool can restore data from MySQL database created on Windows and Linux operating systems.
Conclusion
This article explains how to restore MySQL database from the data folder on a Windows machine. If database restore fails or if your MySQL database is corrupted, then you can use the REPAIR TABLE command to repair the tables. To save time and effort, you can use Stellar Repair for MySQL to repair the damaged or corrupt MySQL database quickly and with complete integrity. It supports recovery of all the objects, including partition tables, from the damaged database. It helps in resolving the errors related to corruption in MySQL database.
7 min read
 in SQL Server-1448.jpg)
