Import a SQL File in MYSQL

Importing SQL files into MySQL is a common task for developers and database administrators. The SQL file contains SQL statements meant to load new data into a database, create a new database, or restore data from a previous MySQL dump.

This post is about MySQL. If you’re using Postgres, read our post on how to run a SQL file using psql.

Import a SQL file into MySQL

There are several steps in the process of importing a SQL file into a MySQL database.

1. 1. Open the MySQL Command line

The first step is to connect to the MySQL database through the command line. This can be done using:

 $ mysql -u username -p

Run in Warp

Where you replace “username” with your MySQL username, and you will then be prompted to enter your MySQL password.

1. 1. Create a Database in MySQL

Before importing a SQL file you need to have a database created in MySQL. To create a new database, you can run the following command:

 mysql> CREATE DATABASE my_database;

Run in Warp

Where you replace “my_database” with the name of your database.

1. 1. Select the database

Once the database is created, you can select it by running the following command:

 mysql> USE my_database;

Run in Warp

Note that this step can be skipped if the USE my_database; instruction is the first line of your SQL file.

1. 1. Import the SQL file

Now that you’ve selected the database, you can import the SQL file using the following command:

 mysql> SOURCE /path/to/file.sql

Run in Warp

If the import is successful you should see a message indicating how many queries were executed.

Common errors when importing

While hopefully the above process should run smoothly, there are several errors that you may encounter along the way:

1. 1. "Access denied for user 'username'@'localhost'" - This error occurs when you don't have the necessary permissions to access the database. Make sure that your MySQL username has the proper permissions.
2. 2. "Unknown database 'my_database'" - This error occurs when the database you're trying to import the SQL file into doesn't exist. Make sure that you've created the database before trying to import the SQL file.
3. 3. "Syntax error" - This error occurs when there's a syntax error in the SQL file. Check the SQL file for any syntax errors and fix them before trying to import the file again.
4. 4. "Table already exists" - This error occurs when you're trying to create a table that already exists in the database. You can either drop the table before importing the SQL file or modify the SQL file to exclude the table creation statement.

Import a CSV file into MySQL

An alternative to running a SQL file in MySQL is to import a CSV file into an existing database. For that, you can follow steps 1 to 3 from above, and then run:

 mysql> LOAD DATA INFILE '/var/lib/mysql-files/myfile.csv'
 INTO TABLE my_table
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 ROWS;

Run in Warp

This command loads the data from the CSV file in the path specified into the table “mytable”. The FIELDS TERMINATED BY clause specifies that the fields in the CSV file are separated by commas. The ENCLOSED BY clause specifies that fields that contain commas are enclosed in double quotes. The LINES TERMINATED BY clause specifies that each row is terminated by a new line character. The IGNORE 1 ROWS clause skips the header row in the CSV file.

You can then verify that the data was imported successfully by querying the table:

 mysql> SELECT * FROM my_table

Run in Warp

Which will display all the data in the “my_table” table.

Generate a MySQL dump - which you can import elsewhere

A MySQL dump file is a file that contains a backup of a MySQL database. It is essentially a text file that contains a series of SQL statements that can be used to recreate the database, including its structure and data. A MySQL dump file is useful for creating backups of databases, moving data between servers, or reseting a database to a previous state.

To generate a MySQL dump file you can run the command:

 $ mysqldump -u username -p database_name > backup_file.sql

Run in Warp

Which will then create a SQL file that you can then import into a new server or database using the steps mentioned above.