How to Restore Database From SQL File in MySQL

1. Restore Database From SQL File Using MySQL Workbench
2. Restore Database From SQL File Using Command Prompt

This tutorial demonstrates restoring the database from a sql file in MySQL.

Restoring the database from a .sql is an easy operation and can be achieved using the MySQL workbench or command line. This tutorial demonstrates both methods of restoring the database from a SQL file.

Restore Database From SQL File Using MySQL Workbench

The MySQL Workbench can be used to restore the database from a .sql file. The operation is very simple and can be done by following a few steps.

1. First, create an empty database on your server where you want to restore the database from the SQL file; if you want to restore it in the previous database, that also can be done, but you will lose changes.

   

2. Now, on the left side, you can find the option Data Import/Restore. Click the option.

   

3. Now, in the Administration – Data Import/Restore, you can find the option Import from the Self-Contained File; click it and browse to your sql file.

   

4. Then, right under the Import from the Self-Contained File, you will find the option Default Target Schema, from which you have to select the schema where you want to restore your database.

   

5. Now, at the bottom of this page, select Dump Structure and Data from the dropdown box.

   

6. After completing the above steps, switch to the Import Progress tab on the Administration – Data Import/Restore Page.

   

7. Final step is to click the Import button and wait for the database to be restored; it will take a few seconds.

   

Your database is now successfully restored using the .sql file in MySQL Workbench.

Restore Database From SQL File Using Command Prompt

We can restore a database by using just one command in the command line. This command takes a few parameters, which are described below the command.

MySQL -u [Server User_Name] –p [database_name] < [RestoreFileName.sql]

Where:

1. The -u [Server User_Name] is the user name for MySQL. In our case, it is the root.
2. -p is the password for the username we used above.
3. database_name is a database where we want to restore our database file, i.e., the target database.
4. RestoreFileName.sql is the name of the database file which will be restored. It should be with the complete file path.

Let’s try an example based on the above command.

MySQL --host=localhost --user=root --port=3306 -p newsakila < C:/Users/Sheeraz/OneDrive/Desktop/New folder/sakila.sql

Where the host will be our server name, and in our case, it is localhost. The above command will restore the database newsakila from the file sakila.sql.

Let’s check our database now by using the following commands:

use newsakila

SHOW TABLES;

The above command will show that the sakila SQL file is imported into the newsakila database schema, which is the restoration. The output for the above commands is below.