MySQL Import SQL File

tl;dr

Getting a timeout error when importing SQL files via phpMyAdmin? Or maybe you don’t have phpMyAdmin and looking for a quick way to export and import SQL files.

Shell

# Import .sql files
mysql -u username -p database_name < file.sql
# Import .sql files with the DB password
mysql -u username –-password=your_password database_name < file.sql
# Import .bz2 files
bunzip2 < file.sql.bz2 | mysql -u root -p database_name
# Export DB to a .sql file
mysqldump -u username –p database_name > file.sql
# Export DB to a .sql file with the password
mysqldump -u username –-password=your_password database_name > file.sql
# Export .bz2 files
mysqldump -u username -p database_name | bzip2 > file.sql.bz2

MySQL export to .sql file.

The best tool for exporting a MySQL database to a text file is mysqldump.

To use mysqldump, you will need to know the login credentials of an appropriate MySQL user that has the necessary privileges to export the database in question.

With that information, enter this mysqldump command with any flags or options you may need:

mysqldump -u username -p database_name > file.sql

• -u flag indicates that the MySQL username will follow.
• -p flag indicates we should be prompted for the password associated with the above username.
• database_name is of course the exact name of the database to export.
• The > symbol is a Unix directive for STDOUT, which allows Unix commands to output the test results of the issued command to another location. In this case, that output location is a file path, specified by file.sql.

This command won’t product visual output, but you can inspect the contents of file.sql to check if it’s a legitimate SQL dump file by running the following command:

head -n 5 data-dump.sql

The top of the file should look similar to this, showing that it’s a MySQL dump for a database named database_name.

SQL dump fragment
-- MySQL dump 10.13  Distrib 5.7.16, for Linux (x86_64)
--
-- Host: localhost    Database: database_name
-- ------------------------------------------------------
-- Server version       5.7.16-0ubuntu0.16.04.1

If any errors happen during the export process, mysqldump will print them clearly to the screen instead.

MySQL import sql file.

You can MySQL import sql file with the following command:

mysql -u username -p database_name < file.sql

• username – the username you login to the database with
• database_name – name of the freshly created database
• file.sql – is the data dump file to be imported, located in the current directory

If the command runs successfully, it won’t produce any output. If any errors occur during the process, mysql will print them to the terminal instead.

Possible MySQL import errors.

ERROR 1044: Access denied for user ‘username1’@’localhost‘ to database ‘username2_database_name’

This error message occurs when the import file contains an SQL statement that attempts to access a database for the wrong username. Note in this example that username2 in username2_database_name does not match username1 in ‘username1‘@’localhost‘. You must edit the import file and change username2 to your new username1.

ERROR 1049: Unknown database ‘username_database‘

This error message occurs when the target database does not exist. Make sure you create the database first as described above, and then try to it import again.

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘username_database‘ at line x.

This error message occurs when the import file does not contain backup data for a database or there is a MySQL syntax error in the file. Alternatively, the import file may be altered, corrupt, or in an unsupported format. (Import files must contain SQL statements; other file formats such as CSV do not work with the mysql program.) Try exporting the database again, and then try to import it.

ERROR 1227: Access denied; you need (at least one of) the SUPER privilege(s) for this operation.

This error message occurs when the import file contains one or more SQL statements that require superuser privileges (such as SET GLOBAL or CREATE DEFINER statements). In some cases, you can just delete these statements from the .sql file and rerun the import process.

For example, CREATE DATABASE statements can be safely removed because you should have already created the database.

Import & export bzip2 files.

A comparison between a normal dump and a bzip2 results in a 78.35% decrease in file size. Importing & exporting .bz2 MySQL files with these commands:

Import .bz2 MySQL File

bunzip2 < db_filename.sql.bz2 | mysql -u root -p target_db_name

Export .bz2 MySQL File

mysqldump -u username -p databasename | bzip2 > db_filename.sql.bz2

In Conclusion

It’s simple to export and MySQL import sql file using shell. There are many options available for mysqldump that allow you to customize the exported data. Check out MySQL’s documentation for more information and these other articles on using MySQL in the terminal:

• Using WordPress? Check out these useful WordPress SQL queries.
• Using PHP to connect to MySQL? Learn how to do it the right way.