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.
# 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
The best tool for exporting a MySQL database to a text file is
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
-uflag indicates that the MySQL
-pflag indicates we should be prompted for the
passwordassociated with the above
database_nameis of course the exact name of the database to export.
>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
It is generally advised to input the fully qualified path and filename for the file.sql, so the resulting file is generated exactly where you want it.
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
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.
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.
CREATE DATABASE statements can be safely removed because you should have already created the database.
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
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: