This is a detailed tutorial on how to import an SQL File using the command line and the GUI in MySQL. Steps to import an SQL file using the command line in MySQL and also using the phpMyAdmin GUI are mentioned in this article.
Command Line Vs. GUI For SQL File Import
Which one to prefer?
Why should you even talk about the command line method when we have got the GUI for importing SQL files in MySQL? This is one of the most asked questions among SQL beginners. Well, there’s a strong reason behind it.
Using the phpMyAdmin or any other GUI for importing small SQL files (Less than 10 MB) is cool. Even I personally prefer using the GUI for importing any of the SQL files that are small in size. But when it comes to large files (larger than 10 MB), the GUI becomes non-responsive and does not show any progress of how much percentage of SQL file is being imported and how much is left.
Even sometimes, when I try to import very large databases (Larger than 100 MB), I often encounter non-ending non-responsiveness, and it simply annoys a lot. Therefore in the case of large SQL files, I prefer using the command-line interface, which shows the progress of queries being executed while importing the SQL file. Even if any point of the time an error encounters, we can find out at what point it occurs.
Therefore, you may use the command line method for importing larger SQL files, while for smaller ones, the GUI is all good.
Importing SQL File Using Command Line in MySQL
There are several different methods to import SQL files using the command line in MySQL. The steps are given below for the easiest method that I found.
- Open Command line and log in with your MySQL username and password.
Type the following command to log into MySQL using your Database username and password.
mysql -u [username] -p
Note. Replace [username] with your Database username. As I am demonstrating on the localhost, I am logging into MySQL as the root user.
After entering the above command and hitting enter, it will ask you for the user password. Enter it and hit enter. You will now be successfully logged into MySQL for the current user.
- Select A Database (Incase, the SQL file is for a single database import)
Most of the time, we import single database SQL files; therefore before you can actually start importing the SQL file, you must choose a database to import the SQL file. If you have not one, create one using the
CREATE DATABASE [database-name] command.
For database selection, use the following command.
As soon as you hit enter, the database will be selected, and you’re now ready to perform any actions or operations on this database.
- Start Importing the database.
Write the following command to start the database import.
I am using the XAMPP stack on Windows for demonstration. Therefore, here the default file directory it assumes is
C:\XAMPP. So, I put the SQL file to import in this directory. The file was named
school.sql, therefore I entered the command
source school.sql directly without specifying the absolute path directory. It may take time for execution, depending upon the SQL import file size. In my case, the database was only of a few KBs, and it hardly took a second to complete its query execution.
The following command is the shortcut command for the same method followed above,
mysql -u [username] -p [database_name] < file-to-import.sql
The above command will ask you the user password and will start the import right after detecting the right user password.
Importing SQL File Using phpMyAdmin GUI
The steps to import an SQL file in MySQL using phpMyAdmin GUI are given below.
- Choose the database from the Left Pane or Sidebar of the phpMyAdmin Home window that appears after successfully logging into the dashboard using your username and password.
- After selecting the right database, go to the Import tab.
- Use the Choose File button to select the SQL file to upload and import. There are plenty of other options provided on this page as well, which you can configure according to your import requirements.
Most of the time, we need not change any of these options. The default configuration is perfectly ok.
- Once chosen the right file and configured everything right, simply click on the Go button present at the bottom of the Import tab page.
It will take some time to complete the import process, and when done, it will show you the success message as shown in the screenshot given below as well.
I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.
Helpful Associated Guide.
Also, don’t forget to Subscribe to WTMatter!