Importing .sql file in MySQL via command line and phpMyAdmin
Websites using databases have become common now. A website can have unlimited number of databases unless the package under which the website is hosted or the plan provided by the hosting provider restricts the number of databases a website can have. When working with databases, it is necessary to back up data in case you need to restore your website. In MySQL, we use dumping a database to a file with extension .sql to backup and import the .sql file to restore the database.
In this documentation, we are going to see how to import a .sql file in MySQL using various available methods. We can import the .sql file with the help of the command line and via the front-end like phpMyAdmin. Let’s look into this now.
Importing .sql file in MySQL using the command line Interface
First, we are going to see how to import the .sql file with the help of a Command Line Interface. If you have a VPS or a Dedicated Server, it is recommended performing this via the CLI. If you are not confident to perform the operation via the back-end, you can also import the database backups with the help of a front-end application such as phpMyAdmin as described in the later section. Follow the steps described below to perform a MySQL import using the command line interface.
If you are the webmaster or website administrator of your website, acquire the login credentials from our support section so that you could ssh into the server.
1) Open the “Terminal” or “Console” in your PC
2) SSH into the server with the following command (Assuming you have a ‘root’ access to the server and the ssh port is 22 as default. Please replace the ‘*.*.*.*’ with the IP address of the your server.
===
[root@system1 ~]# ssh root@*.*.*.*
The authenticity of host ‘*.*.*.*(*.*.*.*)’ can’t be established.
RSA key fingerprint is 12:35:61:32:e4:31:14:fe:12:6f:ac:c2:61:af:e2:40.
Are you sure you want to continue connecting (yes/no)?
===
The RSA key is displayed. Now, please type “yes” if you want to connect.
===
Are you sure you want to continue connecting(yes/no)? yes
Warning: Permanently added ‘*.*.*.*’ (RSA) to the list of known hosts.
root@*.*.*.*password:
===
3) Now enter the password of the server for the root user.
Now, you are inside the server as user ‘root’. Now you need to open mysql to perform an import operation. Enter the below command to proceed.
===
[root@host ~]# mysql -u username -p
===
Now enter the password for the user. Enter the following command to import a backup file ‘bak1.sql’ to the database ‘db1’. You can also list the databases to confirm the database before performing the import. It will also help to avoid any typo.
===
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| cphulkd |
| eximstats |
| leechprotect |
| modsec |
| mysql |
| performance_schema |
| db1 |
| roundcube |
+——————–+
9 rows in set (0.00 sec)
mysql>
mysql> db1 < bak1.sql
===
4) Now the .sql file ‘bak1.sql’ is imported to the database, db1. You can close the mysql interface with the help of ‘exit’ command.
===
mysql> exit
Bye
===
5) Now, you can exit from the server with the help of following command.
===
[root@host ~]# exit
logout
Connection to host.com closed.
[root@system1 ~]#
===
These are the steps to import a .sql file from a backup to a database using the Command Line Interface.
Importing .sql file in MySQL using the phpMyAdmin
The next method is importing the database backup using the phpMyAdmin interface.
Follow the steps below to perform the import using the phpMyAdmin. You can open the application from the cPanel interface.
1) Login to cPanel.
2) Open phpMyAdmin by clicking on the icon.
3) You will get a new interface. Here you can select the database need to restore with the backup file.
4) After selecting the database, click on the ‘Import’ button as shown in the figure below.
5) Choose the file to import from the interface.
6) Now, click on the button ‘Go’ to finish the import.
After the process completes, you will get a message as ‘Import has been successfully finished, ## queries executed.’.
These are the methods to import a .sql file to a selected database with the help of Command Line Interface and phpMyAdmin interface.
If you need any further assistance please contact our support department.
