How to Restore MySQL Database from Backup in Different Ways?

There is no denying the fact that databases are the beating heart of any organization, big or small. In fact, you cannot even begin to think about launching your business until and unless you have a robust database in place.

The trouble with databases is that they are prone to various kinds of problems, which can threaten your company’s smooth running. You might have heard about the importance of database backups in the context of web hosting and how they can help you recover from disasters like a hacker attack or human error. Let’s learn about MySQL database recovery software.

If you don’t take regular backups or something goes wrong with your database, it can result in serious problems like losing user data. This article will show you various ways to restore your MySQL database in different scenarios so that this won’t happen to you again.

Method 1- Restore MySQL Database from Backup file.

Data loss or corruption can occur in a variety of situations, such as when users accidentally delete or overwrite data. For these reasons, it is recommended that backups be performed on a regular basis. This process ensures data consistency in your databases and ensures that nothing critical happens to your data and that it is not irreversibly lost. 

Furthermore, data backup keeps data up to date and allows you to restore it at any time in the state it was in before the data was damaged.

Note: MySQL generates a large number of files that are used to backup MySQL databases’ data, structure, and indexes. Among them are the following:

my.cnf file contains MySQL configuration settings.

.frm file contains a table schema and definition. 

(Only for the MyISAM engine) Table data is stored in the myd file. 

(Only for the MyISAM engine) .myi file stores table indexes.

(Only for the InnoDB engine) .ibd file contains table data and indexes.

You can avoid data loss if you ensure to not delete the above files.

Method 2- Restore a database from the command line client 

To restore a database from a dump file in MySQL, use the MySQL command.

mysqldump is a command-line functionality that creates a logical database backup of MySQL as a single.sql file containing a range of MYSQL statements. You can use the utility to dump MySQL tables, numerous databases, or even their objects. Ensure that the mysqldump utility cannot backup MySQL databases or data to separate.sql files. Furthermore, to use the mysqldump and MySQL commands, you must have access to a running MySQL server.

Step 1: Create the Database

Connect to the MySQL server where you want to create the database in the command prompt and run the MySQL command:

mysql> create database abcd(database name);

Please keep in mind that the database name should be the same as the database you want to restore.

Step 2: Restore the Database

We can use the dump file to restore the MySQL database. The command’s syntax is as follows:

mysql -u [user_name] –p [target_database_name] < [dumpfilename.sql]

The above syntax has the following parameters used:

Parameters	Meaning of Parameters
-u [user_name]	Username to connect to MYSQL server
–p	Password for the above username to connect to the server
[target_database_name]	Name of the empty database where we want to load the data from backup file
<	Resembles the Process to Restore the Database
[dumpfilename.sql]	Path to the dump file

Now in order to restore the abcd database, enter the following command:

mysql –host=dbfmylast –user=root –port=3306 -p abcd< D:\abcd.sql

Here, dbmylast refers to the target server that has an empty database where we will load the data from the abcd.sql dump file.

Check the results of the following using commands:

mysql> use abcd;

…

mysql> show tables;

Now, you will come across multiple tables in the ABCD database.

Method 3- Restore Databases in MYSQL using mysqldump from the dump file

You can use the mysqldump utility to restore not just a single database, but all databases on the server in bulk.

To restore all MySQL databases from a dump file, use the following syntax:

mysql -u root -p < alldatabases.sql

 alldatabases.sql is the path to the dump file having a backup of all databases in the server.

If you want to restore the backup from the backup_all_databases.sql dump file, all you need to do is, run the following command:

mysql -u root -p < D:\backup_all_databases.sql

Method 4- Restore one Table from the dump file using MYSQL

Sometimes, it may be necessary to restore a specific MySQL table from the database in some cases, such as when the table was dropped or its data was overwritten.

To demonstrate, use the mysqldump command to create a backup copy of the working hours table from the abcd database. The backup working hours table.sql file is the output file that contains the table back up.

mysqldump –host=dbfmylast –user=root –port=3306 -p abcd working_hours > D:\backup_working_hours_table.sql

Now, you can drop the above table with the DROP TABLE command:

mysql> drop table working_hours;

Now, verify if the database was removed with the command:

mysql> use abcd;

…

mysql> show tables;

In the output, you can see the entire list of tables in the database but cannot find the working_hours table.

Time to restore the working_hours table into the abcd database with the mysql command.

mysql –host=dbfmylast –user=root –port=3306 -p abcd< D:\backup_working_hours_table.sql

You can see the table is imported in the abcd database.

Method 5 – Restore the MYSQL database using Workbench from the .sql file

This section describes how to use the MySQL Workbench tool to restore a MySQL database from a.sql file. Create an empty database on the server to which you want to connect before restoring the database.

• In Navigator, open MySQL Workbench and select Data Server > Import/Restore.
• Select Import from Self-Contained File in the Administration- Data Import/Restore the document.
• Browse for the.sql file containing the backup of the abcd database. 
• Under Default Schema to be Imported To, choose the target database into which the backup of the source database, including its structure and data, will be imported.
• To restore the database structure and data, select Dump Structure and Data from the drop-down list at the bottom of the document.
• When you’ve finished configuring all of the parameters, go to the Import Progress tab and click Start Import. When the import is finished, you can close the tool.

Method 6 – Restore an MYSQL database using phpMyAdmin

If you’re familiar with phpMyAdmin, it is easy to recover any deleted table or even copy a MySQL database to a different server.

You must use the Export and Import tools for MySQL database backup and restoration.

To restore a MySQL database, 

• Launch phpMyAdmin.
• Create a new database into which the database will be exported. Go to the Databases tab, type in the database name, choose the same collation as the backup database, and click on Create.
• The database will now be visible in the Navigation panel. 
• Then, from the hamburger menu, choose Import.
• Under File to Import:, select the file containing the database backup on the Importing into the database “abcd” page. 
• To begin restoring the database, click Go at the bottom of the page.
• After that, the MySQL tables and other database objects will be added to the abcd database.

Method 7 – Backup and Restore MYSQL database with Stellar Repair for MySQL

Following any of the above-mentioned steps can repair a corrupt MySQL database; however, none of the procedures are guaranteed to solve the problem. In such cases, using a professional tool is the only option. Stellar Repair for MySQL, on the other hand, is a dependable tool that completely repairs corrupt MySQL databases.

To run Stellar Repair for MySQL and repair all corrupt databases, follow these steps.

• Stellar Repair for MySQL should be installed and run.

• Select the corrupt MySQL database in the ‘Select Database Folder’ section of the main interface. To select the database, you have two options:
• MySQL 6.x and below or MySQL 8.x

• Click ‘OK’ after selecting the file.
• The scanning process will be initiated by the software.

• Following the repair process, a list of all repaired database files will be displayed.

• Under the ‘File’ tab, select ‘Save.’
• As shown in the diagram, there are four ways to save:

• To connect to Server, enter your credentials.

• Save the file.
• After saving the repaired database, the software will display a success message.

Conclusion :

This article talks about the importance of taking database backups and what to do when the database you’re using gets corrupted or accidentally deleted. Restoring your database from a backup is not as difficult as you think it is. The good thing is that all major database management systems provide a simple way to do this. You just need to follow a few steps, and your database will be restored in no time. Take the necessary steps to restore it from the backup. We have discussed ‌third-party MySQL database recovery software in various scenarios. And if you’re using a database, make sure you set a schedule to take database backups. That way, you can easily restore it in case of any disaster.