How Can We Help?
Importing .sql file in MySQL via command line and phpMyAdmin
Websites utilizing databases have now become commonplace. A website can have an infinite number of databases unless the web hosting package or plan limits the number. When working with databases, it is essential to create backups in case a website needs restoration. In MySQL, we utilize the process of dumping a database to a .sql file for backup and importing the .sql file to restore the database.
This documentation will demonstrate how to import a .sql file in MySQL using various methods. We can import the .sql file through the command line interface or the front-end application like phpMyAdmin. Let’s explore these methods now.
Importing .sql file in MySQL using the Command Line Interface
First, let’s see how to import the .sql file using the Command Line Interface. If you have a VPS or a Dedicated Server, it is recommended to perform this task via the CLI. If you are not confident in operating through the back-end, you can also import the database backups using a front-end application like phpMyAdmin, as explained in the later section. Follow the steps below to perform a MySQL import using the command line interface.
If you are the webmaster or website administrator, obtain the login credentials from our support section to ssh into the server.
1) Open the “Terminal” or “Console” on your PC.
2) Use the following command to SSH into the server (assuming you have root access to the server and the default SSH port is 22). Replace “*.*.*.*” with the IP address of 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 will be displayed. To connect, type “yes”.
===
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘*.*.*.*’ (RSA) to the list of known hosts.
root@*.*.*.* password:
===
3) Enter the password for the root user.
Now, you are inside the server as the user ‘root’. Now you need to open MySQL to perform the import operation. Enter the following command to proceed.
===
[root@host ~]# mysql -u username -p
===
Enter the password for the user. To import a backup file named ‘bak1.sql’ to the database ‘db1’, use the following command. You can also list the databases to confirm the database before importing, which can help avoid any typos.
===
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) The .sql file ‘bak1.sql’ is now imported to the database ‘db1’. You can close the mysql interface using the ‘exit’ command.
===
mysql> exit
Bye
===
5) To exit the server, use the 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 phpMyAdmin
The next method is importing the database backup using the phpMyAdmin interface.
Follow the steps below to perform the import using phpMyAdmin. You can access the application from the cPanel interface.
1) Log in to cPanel.
2) Open phpMyAdmin by clicking on the icon.
3) A new interface will open. Here, select the database that needs to be restored 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) Click on the ‘Go’ button to finalize the import.
After the process completes, a message will appear stating ‘Import has been successfully finished, ## queries executed.’
These are the methods to import a .sql file to a selected database using the Command Line Interface and phpMyAdmin interface.
If you require further assistance, please contact our support department.
To import an .sql file in MySQL, you can use either the command line interface or the phpMyAdmin interface. The command line method is recommended for users with a VPS or Dedicated Server, while phpMyAdmin is suitable for those who prefer a front-end application. Here are the steps to import an .sql file using each method.
Importing .sql file in MySQL using the command line interface:
1. Open the Terminal or Console on your PC.
2. SSH into the server using the command: ssh root@*.*.*.* (replace *.*.*.* with the server’s IP address).
3. Enter the password for the root user.
4. Open MySQL by entering the command: mysql -u username -p (replace username with your MySQL username).
5. Enter the password for the user.
6. To confirm the database before importing, use the command: show databases;
7. To import the .sql file, use the command: db1 < bak1.sql (replace db1 with the database name and bak1.sql with the .sql file name).
8. Exit MySQL using the command: exit.
9. Exit the server using the command: exit.Importing .sql file in MySQL using phpMyAdmin:
1. Log in to cPanel.
2. Open phpMyAdmin.
3. Select the database you want to restore using the backup file.
4. Click on the Import button.
5. Choose the .sql file you want to import.
6. Click the Go button to finish the import.
After the import process is completed, you will receive a message indicating the success and the number of queries executed.
If you need any further assistance, please contact our support department.
