”;
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 |
”;