MySQL - Database Import

Table of content


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
Advertisements