Simplest Way to Restore MySQL Database

The collection of data that is particularly organized for fast retrieval is called a database. To store a large amount of data, we now use Relational Database Management System (RDBMS).

MySQL is an open source Relational Database Management System (RDMS) based on SQL or Structured Query Language. MySQL is developed by Swedish Company MYSQL AB but is now under Oracle. MySQL is compatible with a number of file formats such as .sql, .myd, .myi, .mysql, .arm, .phl, .sal, .sqr, .tmd, .cnf, .dbs, .ddl, .opt, .arz, .ibz, .ibc, .qbquery, .rul, .frm, .ibd, .ism, .mrg.In the database, the data is stored in folder and tables are stored in files of different format. Some of the file formats:

• db.opt – contains characteristics of a database when created or altered.
• frm – the table structure file.
• .myd – MyISAM table data is stored in it.
• .myi – MyISAM table indexes are stored in it.
• .ibd – InnoDB table data & indexes are stored in it.

MySQL database is often damaged by various reasons like accidental deletion, network failure, software malfunction, etc. To recover MySQL database, there are different ways. Read below to recover it completely.

Different Approach to Restore MySQL Database

Approach 1: Using mysqldump command

Using mysqldup comman to recreate or recover MySQL Database as New Database. mysqldump command connects to MySQL server and creates a SQL Dump file through which we can recreate the database.

• First of all, create a new database using MYSQL.
• Name it the same which you have earlier lost.
• Load the file using mysqldump command.

mysql -u [user] -p [database_name] < [filename].sql
where:

• Replace [user] with username and password.
• The [database_name] is the name of the database.
• [filename] is the filename of the dump file.

Approach 2: Using phpMyAdmin to Recover a MySQL Database Instantaly

• Open phpMyAdmin
• In the left navigation pane, click on New.
• Now create a new database by entering the name in create a database and click on Create button.
• In the top bar, click on Import.
• Now under File to Import, click on the Browse button.
• Choose the file you want to restore and click Go.

In this way, you can recover MySQL Database easily.

Approach 3: Using MySQL Workbench

In this approach, you first need to have the backup of MySQL files and must have MySQL server installed on your device. This approach will only work for MYISAM tables and not for InnoDB tables.

• First of all stop MySQL services.
• Next copy the database folder into the new MySQL\data\ folder.
• Now restart the MySQL Service.
• Run mysqlcheck or CHECK followed by REPAIR to ensure that there is no error and if there is then it is repaired.
• For restoring data, open MySQL Workbench .
• Go to the left navigation pane.
• Click on Data Import/Restore to recover all the database.

Approach 4: Using MySQL Database Restoring Tool

Another simple and hassle-free way to repair and recover damaged and corrupt SQL database files is by using third-party software called Aryson MySQL Database Repair. Some of its features are as follows:

• Repair and recover maximum possible data from MySQL database facing corruption.
• Can recover data from both InnoDB and MyISAM tables of MySQL database.
• Can easily restore database objects like tables, keys, table properties, data types, views, triggers, etc.
• Loaded with smart preview feature to preview recovered files.
• Auto-generates Log file at your mentioned location in your system.
• Easy to use Graphical user interface.
• Support all major versions of MySQL.
• Runs smoothly on all versions of MS Windows OS versions.
• Repair MySQL Database of all versions of MySQL.
• Extract data from corrupted MySQL DB and save as new script.

Follow the step below to Restoring MySQL Database:

• Launch MySQL Database Recovery.
• Click on Open button and then click the Browse button to choose corrupt MySQL Database .idb and .frm file.
• Select a file and click on OK button.
• The scanning process starts.
• The scanning process is completed, press the OK button.
• Now click on Removery Mode options to Restore the MySQL Database.
• Now save as script and then click OK button to finish the saving process.

Conclusion

Recovering MySql database using different command is not possible for a normal person who have no prior knowledge. Hence to recover MySql database, use of professional software like Aryson MySQL Database Repair is the best possible choice. If you need migrate database from MySQL to MSSQL Server then, you can use the another professional software like MySQL Conversion.

Realted Post