How To Restore Mysql Database From Data Folder In Windows

Situations may ascend when yous need to restore your MySQL database, including server crash, un-installing and re-installing MySQL Server, or salvaging lost or deleted data. Whatever exist the cause, you tin 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 y'all demand to restore your database. In social club 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 tin can't locate your MySQL Server data folder, try looking for it in its default location: "C:\ProgramData\MySQL\MySQL Server ten.ten\data".

Notation: Here, "x.10" is the version of your MySQL Server you are using. So, supervene upon information technology with the version number when locating the data folder.

Now permit's discuss in detail how you can restore a MySQL database from data binder in your Windows system.

Steps to Restore MySQL Database from Data Binder in Windows

Pace i: Install MySQL Server

Ensure that the MySQL Server version used to create the database is the same every bit that of the newly installed version. Or else, MySQL service volition not start. You can download the same MySQL version for Windows from here.

Step 2: Offset MySQL Service

During MySQL installation, set the root password for Windows and then get-go the MySQL service.

Stride 3: Stop MySQL Service

Now you lot must stop the MySQL service. To do then, open up Control Panel and click on Authoritative Tools. From the screen that appears, correct-click on Services and select MySQLx.10 (replace ten.x with the MySQL version) and stop the service.

Step 4: Copy Data Binder with Database Files to a New Data Folder

This is the major footstep that requires you to re-create the backed upwards data folder containing the MySQL database files you lot want to restore to a new information binder.

In the information folder, locate the folder with the aforementioned name every bit the database you want to restore. For case, to restore a db named every bit "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. Re-create those files and move them to a local MySQL folder on your Windows system "C:\ProgramData\MySQL\MySQL Server ten.x\information\" binder.

Note: When restoring InnoDB database files, ensure that the innodb_log_file_size has the aforementioned 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 v: Restart your MySQL Service

Later copying the database files, restart the MySQL service and verify that your database is restored.

If you're unable to restart the service, bank 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 10.x\data\MACHINE_NAME.err".

What If You Fail to Restore MySQL Database?

Endeavor to restore MyISAM tables. First identify the corrupt table by executing the following "mysqlcheck" statement:

mysqlcheck [options] db_name tablename [tablename2... ]

One time y'all've found the corrupted table, run the REPAIR control equally follows:

REPAIR TABLE tablename [options]

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. Yous may have to increase the value from 1–half-dozen to start the server. But, exceeding the value from iii involves data loss run a 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.

Endeavor Stellar Repair for MySQL software, designed for database administrators and other MySQL users to repair a severely corrupted database and restore information technology to its normal state, with no information 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 go along.

• On the "Select Data Folder" dialog box, choose the version of your MySQL Server. Next, click "Browse" to select the information folder and click "OK".

• Select the data folder that contains the database files you lot demand to repair, and then click "OK".

• Once you've added the data folder, hitting the "OK" button.

• A "Select Database" window appears with all the database files within the selected information folder. Click "Repair" to beginning repairing the files.

Notation: Past default, all the database files are selected in the "Selected Database" dialog box. However, you may cull 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 relieve the db, click"Relieve" from the "File" card.

• Choose "MySQL" as the file format in which you need to relieve the file, enter the details to connect to a server instance, and so 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 automobile. It explains the pace-wise instructions, which help you perform the db restore process chop-chop. But, if the manual steps to regain access to the MySQL database fails, using Stellar Repair for MySQL software may assist.

Source: https://www.stellarinfo.com/article/restore-mysql-database-from-data-folder-in-windows

Posted by: leebrigingening37.blogspot.com

Share this post