As we learned in the previous tutorial about ”Database Export”, now we”ll learn how to import the exported data, or backup, into an existing MySQL database. This process is known as database import.

In MySQL, to import an existing dump or backup file into a database, we use the mysql command-line tool.

Importing Backup Data

We can import the backup data into an MySQL database using the mysql command-line tool. It takes the username, database name, and the backup file with the data.

Syntax

Following is the syntax of mysql command-line tool −

$ mysql -u username -p new_database_name < dumpfile_path

Where,

• username: This is the MySQL username to use when connecting to the MySQL server.

• new_database_name: The name of the database where you want to import the data.

• dumpfile_path: It is the path of the backup file. The data will be imported from this file.

• <: This symbol imports the data from the file named output_file_path.

Example

In this example, we will import the file named “data-dump.sql” that was generated in the previous tutorial (Database Export). The file contains a table named ”CUSTOMERS”.

Before doing that, let us login to MySQL server as a user to create a new databases −

$ mysql -u root -p

After logging in, it will bring you into MySQL command-line. Now, create a new database named testdb using the below query −

CREATE DATABASE testdb;

When we execute the above query, the output is obtained as follows −

Query OK, 1 row affected (0.01 sec)

To exit from the MySQL command-line, execute q. Now, from the normal command line, we can import the dump file ”data-dump.sql” using the following query.

Once we execute the below statement, we need to enter our MySQL server password.

$ mysql -u root -p testdb < data-dump.sql

If the above command is runs successfully, it won”t show any visible output. Instead, it imports the data. If any error occur during the execution, MySQL will display them to the terminal.

Verification

To verify whether the import was successful, execute the following query to login into MySQL command-line −

$ mysql -u root -p

Now, select the current database to ”testdb” using the following MySQL ”Use” query −

Use testdb;

Execute the following query to check whether the table named CUSTOMERS in “data-dump.sql” file has been imported or not −

Show Tables;

As we can see the output below, the CUSTOMERS table has been succesfully imported into the new database ”testdb”.

Tables_in_testdb
customers

Let us also verify whether the records has been imported or not by executing the below query −

select * from customers;

The records are also successfully imported.

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00