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.
- Open the MySQL Command line
The first step is to connect to the MySQL database through the command line. This can be done using:
Where you replace “username” with your MySQL username, and you will then be prompted to enter your MySQL password.
- 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:
Where you replace “my_database” with the name of your database.
- Select the database
Once the database is created, you can select it by running the following command:
Note that this step can be skipped if the [.inline-code]USE my_database;[.inline-code] instruction is the first line of your SQL file.
- Import the SQL file
Now that you’ve selected the database, you can import the SQL file using the following command:
If the import is successful you should see a message indicating how many queries were executed.
[#common-errors]Common errors when importing[#common-errors]
While hopefully the above process should run smoothly, there are several errors that you may encounter along the way:
- "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.
- "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.
- "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.
- "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-csv]Import a CSV file into MySQL[#import-csv]
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:
This command loads the data from the CSV file in the path specified into the table “mytable”. The [.inline-code]FIELDS TERMINATED BY[.inline-code] clause specifies that the fields in the CSV file are separated by commas. The [.inline-code]ENCLOSED BY[.inline-code] clause specifies that fields that contain commas are enclosed in double quotes. The [.inline-code]LINES TERMINATED BY[.inline-code] clause specifies that each row is terminated by a new line character. The [.inline-code]IGNORE 1 ROWS[.inline-code] clause skips the header row in the CSV file.
You can then verify that the data was imported successfully by querying the table:
Which will display all the data in the “my_table” table.
[#mysql-dump]Generate a MySQL dump - which you can import elsewhere[#mysql-dump]
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:
Which will then create a SQL file that you can then import into a new server or database using the steps mentioned above.