How to Restore MySQL Database from SQL Dump File in Linux?

In the events like MySQL server crash, database corruption, and data loss, you can restore the database and recover its data from its most recent backup. You can create a dump (backup) file using mysqldump utility. By default, the utility writes the data as SQL statements that can be used to recreate the original database.

Assuming that you’ve created a backup using mysqldump, this article will discuss steps to restore the database from the backup on a Linux system.

Before Getting Started

If you need to learn how to back up a database in Linux, perform the following steps. Or, you may skip to the next section to check the commands to restore a MySQL database.

Step 1: Open the command line in your Linux terminal, then log in to your database server as a root user.

Step 2: Once you’re logged in, back up your MySQL database using the mysqldump command:

[root@mysqlbox ~]# mysqldump -u root -p testdb > /data1/testdb_backup.sql

In this mysqldump command, ‘p’ will prompt you for the password required to log into the server. Next, ‘testdb’ is the database backed up, and ‘db_backup’ is the backup file’s path. Finally, ‘testdb_backup.sql’ is the backup file name.

Step 3: Exit the MySQL database server by executing:

mysql> exit

Step 4: Now, check if the database backup is created or not. To do so, run the following command while logged in as root user:

[root@mysqlbox ~]# ls -l /data1/testdb_backup.sql

Restoring a MySQL Database from SQL Dump File in Linux

To restore from a SQL dump file (i.e. backup) created using the mysqldump utility, perform these two steps:

Step 1: Create a Dummy Database

Create a dummy (i.e., empty) database and restore the original file's contents to the dummy database. Doing so will prevent you from encountering an error '1049 (42000): Unknown database'.

For creating an empty db, login into MySQL as admin user and then run this command:

mysql> create database testdb;

Now quit the server and proceed with the next step.

Step 2: Restore MySQL Database from SQL Dump File

Now, let’s restore the backup file created with mysqldump using the ‘mysql’ tool. In our case, we will be restoring the ‘testdb_backup.sql’ file.

[root@mysqlbox ~]# mysql -u root -p testdb < /data1/testdb_backup.sql

Here, the '<' parameter is used to restore the backup (testdb_backup.sql) of the testdb database.

Note: If you’ve created a mysql dump containing multiple databases and you need to restore a single database from the backup file, use the ‘--one-database’ option as shown below:

mysql> --one-database yourdbname < dumpfilename.sql

Make sure to create an empty db before running this command or you may get an error.

What If the Restore Process Fails?

If you’re unable to restore a MySQL database due to corruption or invalid backup, using Stellar Repair for MySQL software can come in handy. This MySQL repair tool helps fix a corrupted database for both Linux and Windows-based systems. Also, it saves time from manually repairing a MyISAM table and recovering an InnoDB table by running innodb_force_recovery. The software allows to repair corruption in both MyISAM and InnoDB tables while maintaining the original table structure. Also, it helps restore all the database objects. You can preview all these objects before saving them by downloading the demo version of the MySQL repair tool.

Conclusion

Hopefully, now you know how to restore a MySQL database from a mysqldump file in Linux. But remember, situations may arise when the backup you want to restore has also turned corrupt or is obsolete. If restoring from backup doesn’t work for you and immediate access to the data within your MySQL database is required, you may consider using a MySQL repair tool suggested in the article.