How to Import SQL File in MySQL

Often database programmers and administrators need to create databases from files. Sometimes, they may need to import data from a file into databases. For these purposes, you need to import SQL file in MySQL. There are several ways to do this. In this article, we will learn how to import SQL file in MySQL.

How to Import SQL File in MySQL

Here are the different ways to import SQL file in MySQL. First of all, create an empty database where you can import data. Log into MySQL.

$ mysql -u <username> -p <password>

Run the following command to create an empty database testdb.

mysql> create database testdb;

1. Using Command Line Interface (CLI)

The MySQL command used to log into your MySQL database, can also be used to import file into a database. You need to run the command from terminal (Mac/Linux) or command prompt, without logging into MySQL console. Here is its syntax.

$ mysql -u <username> -p <password> <database_name> < /path/to/sql/file

In the above command, we need to mention MySQL username and password, along with the database name. Lastly, we need to mention the path to SQL file. Here is a sample command to import file testdb.sql.

$ mysql -u test_user -p test_password testdb < /home/ubuntu/test.sql

2. Using Source Command

Source command allows you to import SQL file to database from within MySQL console. Here is its syntax.

mysql> source /path/to/file

Before we run the above command, we need to switch our target database.

mysql> use testdb;

Then we run the source command.

mysql> source /home/ubuntu/data.sql

If the above command runs successfully, it will display how many queries were successfully executed.

3. Using Load Data

LOAD DATA is another MySQL command that can be run from within MySQL console. It is used to import a csv or tab delimited file into MySQL table, instead of database. Here is its syntax.

mysql> LOAD DATA INFILE /path/to/file
 INTO TABLE <table_name>
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 ROWS;

In the above command we mention the path to CSV file after LOAD DATA INFILE. We also specify the table name. We further mention the delimiter after ‘FIELDS TERMINATED BY’. If our data is enclosed in quotes, then you can mention the enclosing character. This is useful if your file has multi word data items enclosed in quotes. Lastly, we mention the character that is used as newline character. If the first row of our data consists of column headers and you want to ignore them, then you can mention ‘IGNORE 1 ROWS’.

You can also use it to import data from any file where data is separated using delimiters by simply changing the delimiter in above command. This is a great way to import many different kinds of files, whether they are delimited by pipe, tab, comma or some other character. Also it allows you to import data into individual tables, instead of importing the entire database. So it gives more control.

4. Using PHPMyAdmin

PHPMyAdmin is a free, open-source, popular database management tool for MySQL databases. It is used by millions of website owners and system administrators. If you are using PHPMyAdmin to manage your MySQL database, then here are the steps to import database from file.

Open PHPMyAdmin. On left pane, you will see the list of all databases currently present in your MySQL installation. Click the testdb database in that list. Alternatively, click Databases on right pane, you will see a list of databases in your system.

Click testdb database in the list that is displayed.

Next, click on Import tab, on the right pane.

Click Choose File and you will see a file browser window open. Browse to .sql or .sql.zip file and click Go.

PHPMyAdmin will start importing the database. After the import is complete, it will show a success message on right pane, and a list of all database tables in left pane.

5. Using MySQL Workbench

MySQL Workbench is a free, graphical tool to manage MySQL databases. It is very popular and used by many organizations. While there may be slight variation across different versions, here are the basic steps to import SQL file in Workbench.

Under ‘Server Administration‘ or ‘Management‘ on Home Window, click ‘New Server Instance’ to create a new empty database. If it already exists, then click the server instance’s name.

Click Manage Import/Export.

Click Data Import/Restore on the left pane of the screen.

On right pane of screen, you will see a radio button ‘Import from Self-Contained File‘. Select path of .sql file.

Lastly, click Start Import button at the bottom right corner of window.

Common Errors

There are several common problems we face while trying to import SQL file into database.

1. Access denied – Often users get access denied error while trying to import file in MySQL database. This happens if the MySQL user does not have access to database. So ensure that the MySQL user you use for this purpose has appropriate permissions to access the empty database.
2. Unknown database – You get this error if you are using command line interface and have accidentally typed the wrong database name. This can also happen if the database that you are trying to import the file to, does not exist. So make sure to create the empty database before you import SQL file, as well as check if you have used the right database name in your MySQL command.
3. Syntax Error – This happens if you have made some syntax error, especially in command line arguments and options. Make sure you are using the correct syntax for your data import commands.
4. Table exists – This error is rare but occurs if, while importing SQL file, MySQL tries to create a table that already exists. In this case, you can either drop the table before importing SQL file, or modify the SQL file to exclude the table.

Conclusion

In this article, we have learnt how to import SQL file into MySQL. Some of them are useful importing entire database, while some are useful for importing individual tables. Depending on your requirement, you can use any of these methods.

Also read:

How to Concatenate Multiple MySQL Rows Into Column
How to Reset Auto Increment in MySQL
How to Get Character Set of MySQL Database Column