How can I import an SQL file using the command line in MySQL? [closed]

Question

Closed. This question needs debugging details. It is not currently accepting answers.

---

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question.

Closed last month.

The community reviewed whether to reopen this question last month and left it closed:

Original close reason(s) were not resolved

Improve this question

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line.

I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and I tried this command

database_name < file.sql

It is not working. I get syntax errors.

• How can I import this file without a problem?
• Do I need to create a database first?

• Bill Karwin: possible duplicate of Restore MYSQL Dump File with Command Line
• AZinkey: possible duplicate of stackoverflow.com/questions/11407349/…
• Nico Haase: Can you share a reproducable example? database < file.sql does not look like any command to me, and if you see some syntax errors, please share them
• Light.G: After I have checked all answers below, I must say you missed a very important clue for those people who wants to help. You failed to specify the exact command when you dump data out of the database.
• Nico Haase: "I get syntax errors" - what does that mean?

Answer 1

Try:

mysql -u username -p database_name < file.sql

Check MySQL Options.

Note 1: It is better to use the full path of the SQL file file.sql.

Note 2: Use -R and --triggers with mysqldump to keep the routines and triggers of the original database. They are not copied by default.

Note 3 You may have to create the (empty) database from MySQL if it doesn't exist already and the exported SQL doesn't contain CREATE DATABASE (exported with --no-create-db or -n option) before you can import it.

• Sergio Reis: sudo mysql -u username -p database_name < file.sql works in some cases.
• panda-byte: So, -R and --triggers seem to be options for mysqldump, which wasn't immediately clear to me, based on the answer. Additionally, --triggers is enabled by default "This option is enabled by default; disable it with --skip-triggers."
• Raul Chiarella: What if I am using docker? :(
• bansi: You can use docker exec -i <container_id> mysql -u <user> --password=<password> database_name < file.sql Reference: Backup (and Restore) MySQL Data in Docker

Answer 2

A common use of mysqldump is for making a backup of an entire database:

mysqldump db_name > backup-file.sql

You can load the dump file back into the server like this:

Unix

mysql db_name < backup-file.sql

The same in the Windows command prompt:

mysql -p -u [user] [database] < backup-file.sql

PowerShell

cmd.exe /c "mysql -u root -p db_name < backup-file.sql"

MySQL command line

mysql> use db_name;
mysql> source backup-file.sql;

• T.Todua: Is it me only one who has never been able to use < operator in mysql? (ubuntu18/20)
• Stijn de Witt: No idea why the Windows examples include params -u and -p while the Unix example does not. The interface for mysql is the same on both, so most likely you need the same command in Unix as is presented here for Windows.
• temirbek: where we put backup-file.sql? what path it looks by default?
• Swati: I go to C:\Program Files\MySQL\MySQL Server 8.0\bin and run the mysql.exe. Login to MySQL and did the above changes. It worked. Thank you.
• Christian: I wasn't aware of the source command, so this was really helpful for me, esp when the SQL queries are so numerous that they can't be easily copy/pasted

Answer 3

Regarding the time taken for importing huge files: most importantly, it takes more time because the default setting of MySQL is autocommit = true. You must set that off before importing your file and then check how import works like a gem.

You just need to do the following thing:

mysql> use db_name;
mysql> SET autocommit=0 ; source the_sql_file.sql ; COMMIT ;

• Volomike: Is there a way to do that in a single command line on the mysql command used for import?
• ᴍᴇʜᴏᴠ: I agree that this is the best answer. The autocommit=0 portion made a world of difference in terms of the speed.
• hashchange: It's not always necessary to turn off autocommit. It's worth checking the database dump in an editor, it might already begin with SET autocommit=0;.
• iateadonut: @Volomike { echo "SET autocommit=0;"; cat db.sql; echo "COMMIT;";} | mysql -u what -p - that's for posix-compliant command lines, not sure about windows
• Peter Mortensen: This works, and not only for huge files. For a particular (very simple) 5 megabyte SQL file with about 30,000 rows for the single table, it improved the import time from 31 minutes 35 seconds to 11 seconds. That is nearly 200 times faster!!!

Answer 4

We can use this command to import SQL from the command line:

mysql -u username -p password db_name < file.sql

For example, if the username is root and password is password. And you have a database name as bank and the SQL file is bank.sql. Then, simply do like this:

mysql -u root -p password bank < bank.sql

Remember where your SQL file is. If your SQL file is in the Desktop folder/directory then go the desktop directory and enter the command like this:

cd ~/Desktop
mysql -u root -p password bank < bank.sql

And if you are in the Project directory and your SQL file is in the Desktop directory. If you want to access it from the Project directory then you can do like this:

cd ~/Project
mysql -u root -p password bank < ~/Desktop/bank.sql

• Ejaz: There shouldn't be a space between -p and password
• Armin: Jap. This would not work. Correct would be mysql -u root -p"password" bank < bank.sql
• Naveed: why you simply can't answer in one line? mysql -u username -ppassword db_name < file.sql
• asgs: while this is completely unrelated to this question/answer, when you're working with non-trivial databases, prefer NOT entering the password on the same command in plain text. Not specifying the password as part of the command will prompt you for password which you can enter securely
• Neil: Especially because of .bash_history

Answer 5

If you already have the database, use the following to import the dump or the sql file:

mysql -u username -p database_name < file.sql

If you don't you need to create the relevant database(empty) in MySQL, for that first log on to the MySQL console by running the following command in terminal or in cmd

mysql -u userName -p;

And when prompted provide the password.

Next, create a database and use it:

mysql>create database yourDatabaseName;
mysql>use yourDatabaseName;

Then import the sql or the dump file to the database from

mysql> source pathToYourSQLFile;

Note: if your terminal is not in the location where the dump or sql file exists, use the relative path in above.

Answer 6

1. Open the MySQL command line
2. Type the path of your mysql bin directory and press Enter
3. Paste your SQL file inside the bin folder of mysql server.
4. Create a database in MySQL.
5. Use that particular database where you want to import the SQL file.
6. Type source databasefilename.sql and Enter
7. Your SQL file upload successfully.

• Ramesh Pareek: ype the path of your mysql bin directory and press

Answer 7

A solution that worked for me is below:

Use your_database_name;
SOURCE path_to_db_sql_file_on_your_local;

• klewis: This worked for me using MySQL Command Line Client, after placing my sql file in the proper /bin directory view windows explorer. Thanks
• Jaskaran Singh: Little slow but does not stop in between and don't say that MySQL server has gone away.

Answer 8

While most answers here just mention the simple command

mysql -u database_user -p [db_name] < database_file.sql

today it's quite common that databases and tables have utf8-collation where this command is not sufficient.
Having utf8-collation in the exported tables it's required to use this command:

mysql -u database_user -p --default-character-set=utf8 [db_name] < database_file.sql

An according export can be done with

mysqldump -u database_user -p --default-character-set=utf8 [db_name] > database_file.sql

Surely this works for other charsets too, how to show the right notation can be seen here:

https://dev.mysql.com/doc/refman/5.7/en/show-collation.html

One comment mentioned also that if a database never exists an empty database had to be created first. This might be right in some cases but depends on the export file. If the exported file includes already the command to create the database then the database never has to be created in a separate step, which even could cause an error on import. So on import, it's advisable to have a look first in the file to know which commands are included there, on export, it's advisable to note the settings, especially if the file is very large and hard to read in an editor.

There are still more parameters for the command which are listed and explained here:

https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html

If you use another database version consider searching for the corresponding version of the manual too. The mentioned links refer to MySQL version 5.7.

EDIT:
The same parameters are working for mysqldump too. So while the commands for export and import are different, the mentioned parameters are not. Nevertheless there exists a special site in the manual that describes the options for mysqldump: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

Answer 9

I think it's worth mentioning that you can also load a gzipped (compressed) file with zcat like shown below:

zcat database_file.sql.gz | mysql -u username -p -h localhost database_name

• Melebius: On macOS, I had to use gzcat instead of zcat.

Answer 10

To dump a database into an SQL file use the following command.

mysqldump -u username -p database_name > database_name.sql

To import an SQL file into a database (make sure you are in the same directory as the SQL file or supply the full path to the file), do:

mysql -u username -p database_name < database_name.sql

Answer 11

Go to the directory where you have the MySQL executable. -u for username and -p to prompt for the password:

cd C:\xampp\mysql\bin
mysql -u username -ppassword databasename < C:\file.sql

• Reporter: I think it would be more helpful for the OP and further questions, when you add some explaination to your intension.
• wappy: That would work only if you have mysql.exe defined in your windows environment variables. If not, you should type all the path to the mysql.exe file. And Your syntax is wrong. Eg: "d:\wamp\bin\mysql\mysql5.5.8\bin\mysql.exe -u YOUR_USERNAME -p DB_NAME < FILENAME.SQL" More info here: wpy.me/en/blog/…
• Raham: D:\xampp\mysql\bin>mysql -u root -p opfedu_campuses < C:\Users\Raham\Desktop\opfedu_campuses.sql

Answer 12

To import a database, use the following command.

mysql> create new_database;
mysql> use new_database;
mysql> source (Here you need to import the path of the SQL file);
E.g.:
mysql> source E:/test/dump.sql;

You need to use forward slashes (/) even on Windows, e.g., E:/test/dump.sql instead of E:\test\dump.sql

Or double backslashes (\\) because of escaping, i.e., E:\\test\\dump.sql

• Mladen: This actually worked for me. The suggestion with 4000+ votes didn't.
• Zdeněk Gromnica: You need to use forward slashes (/) even on Windows, e.g. E:/test/dump.sql instead of E:\test\dump.sql or double backslashes (\\) because of escaping, i.e. E:\\test\\dump.sql
• Peter Mortensen: But this is not from the command line in the spirit of the question. It is using the MySQL shell interactively.
• reformed: source is not intended for importing databases, but rather for running a small number of SQL queries. stackoverflow.com/a/6163842
• Gad Iradufasha: I support your answer as relevant and clear!

Answer 13

To import a single database, use the following command.

mysql -u username -p password dbname < dump.sql

To import multiple database dumps, use the following command.

mysql -u username -p password < dump.sql

• Abhishek Singh: Thanks! I was looking for this. Importing all MySQL database dumps at once. A more concise and short version of the above command mysql -u root -p < all.sql

Answer 14

mysql --user=[user] --password=[password] [database] < news_ml_all.sql

Answer 15

I kept running into the problem where the database wasn't created.

I fixed it like this:

mysql -u root -e "CREATE DATABASE db_name"
mysql db_name --force < import_script.sql

• Joshua Pinter: What's the need to have --force here?

Answer 16

For exporting a database:

mysqldump -u username -p database_name > file.sql

For importing a database:

mysql -u username -p database_name < file.sql

Answer 17

For importing multiple SQL files at one time, use this:

# Unix-based solution
for i in *.sql ; do mysql -u root -pPassword DataBase < $i ; done

For simple importing:

# Unix-based solution
mysql -u root -pPassword DataBase < data.sql

For WAMP:

REM mysqlVersion - replace with your own version
C:\wamp\bin\mysql\mysqlVersion\bin\mysql.exe -u root -pPassword DataBase < data.sql

For XAMPP:

C:\xampp\mysql\bin\mysql -u root -pPassword DataBase < data.sql

Answer 18

You do not need to specify the name of the database on the command line if the .sql file contains CREATE DATABASE IF NOT EXISTS db_name and USE db_name statements.

Just make sure you are connecting with a user that has the permissions to create the database, if the database mentioned in the .sql file does not exist.

Answer 19

Use:

mysql -u root -p password -D database_name << import.sql

Use the MySQL help for details - mysql --help.

I think these will be useful options in our context:

[~]$ mysql --help
mysql Ver 14.14 Distrib 5.7.20, for osx10.12 (x86_64) using EditLine wrapper
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Usage: mysql [OPTIONS] [database]
 -?, --help Display this help and exit.
 -I, --help Synonym for -?
 --bind-address=name IP address to bind to.
 -D, --database=name Database to use.
 --delimiter=name Delimiter to be used.
 --default-character-set=name Set the default character set.
 -f, --force Continue even if we get an SQL error.
 -p, --password[=name] Password to use when connecting to server.
 -h, --host=name Connect to host.
 -P, --port=# Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306).
 --protocol=name The protocol to use for connection (tcp, socket, pipe,
 -s, --silent Be more silent. Print results with a tab as separator, each row on new line.
 -v, --verbose Write more. (-v -v -v gives the table output format).
 -V, --version Output version information and exit.
 -w, --wait Wait and retry if connection is down.

What is fun, if we are importing a large database and not having a progress bar. Use Pipe Viewer and see the data transfer through the pipe

For Mac, brew install pv

For Debian/Ubuntu, apt-get install pv.

For others, refer to pv - Pipe Viewer

pv import.sql | mysql -u root -p password -D database_name
1.45GiB 1:50:07 [339.0KiB/s] [=============> ] 14% ETA 11:09:36
1.46GiB 1:50:14 [ 246KiB/s] [=============> ] 14% ETA 11:09:15
1.47GiB 1:53:00 [ 385KiB/s] [=============> ] 14% ETA 11:05:36

• Jonny: For Centos: yum install pv
• Ashok G: There should be no space between "-p password" the command should be like "mysql -u username -ppassword dbname < sqlfile" else "mysql -u username -p db < sqlfile" this will prompt for password

Answer 20

Import a database

1. Go to drive:

   d:
   

2. MySQL login

   c:\xampp\mysql\bin\mysql -u root -p
   

3. It will ask for pwd. Enter it:

   pwd
   

4. Select the database

   use DbName;
   

5. Provide the file name

   \.DbName.sql
   

• Peter Mortensen: On Windows, presumably? Why is it necessary to change to drive D:? Is file DbName.sql presumed to be at the root of drive D:? Can you elaborate? Please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the question/answer should appear as if it was written today).

Answer 21

For example, you export the schema and data or only the schema of the tables of apple database to backup.sql with mysqldump as shown below. *My answer explains how to export the schema and data of the tables of a database:

mysqldump -u john -p apple > backup.sql

Or:

mysqldump -u john -p -d apple > backup.sql

Or, you export only the data of the specific tables person and animal of apple database to backup.sql with INSERT statement which has column names as shown below. *By default, INSERT statement doesn't have column names and my answer explains how to export only data more:

mysqldump -u john -p -t -c apple person animal > backup.sql

Then, you need to input a password after running the command above:

Enter password:

Now, you can import backup.sql into orange database with MySQL as shown below. *When importing the schema and data or only the schema, selected orange database must exist and when importing only the data, selected orange database and the tables must exist otherwise there is error and when importing only the data, you need to delete all the data from apple database otherwise there will be error. The documentation explains how to import databases and my answer explains how to create a database:

mysql -u john -p orange < backup.sql

Or:

mysql -u john -p -D orange < backup.sql

Or:

mysql -u john -p --database orange < backup.sql

Then, you need to input a password after running the command above:

Enter password:

Or, after login, you can import backup.sql into orange database with \. or source selecting orange database as shown below:

mysql -u john -p
...
mysql> USE orange;
mysql> \. backup.sql

Or:

mysql -u john -p
...
mysql> USE orange;
mysql> source backup.sql

Be careful, you cannot import backup.sql into orange database not selecting orange database as shown below:

So, this below gets error:

mysql -u john -p < backup.sql

ERROR 1046 (3D000) at line 22: No database selected

And, these below get error:

mysql -u john -p
...
mysql> \. backup.sql

Or:

mysql -u john -p
...
mysql> source backup.sql

ERROR 1046 (3D000): No database selected

In addition, you can import backup.sql into orange database without a password prompt by setting a password(e.g., banana) to -p(--password=) as shown below. *Don't put any space just after -p(--password=) because there is error and my answer explains how to import a database without a password prompt in detail:

mysql -u john -pbanana orange < backup.sql

• Peter Mortensen: Are the two asterisks intentional?

Answer 22

You can try this query.

Export:

mysqldump -u username –-password=your_password database_name > file.sql

Import:

mysql -u username –-password=your_password database_name < file.sql

and detail following this link:

https://chartio.com/resources/tutorials/importing-from-and-exporting-to-files-using-the-mysql-command-line/

Answer 23

Go to the directory where you have MySQL.

cd c:\mysql\bin\
mysql -u username -p password database_name <
filename.sql

Also to dump all databases, use the -all-databases option, and no databases’ name needs to be specified anymore.

mysqldump -u username -ppassword –all-databases > dump.sql

Or you can use some GUI clients, like SQLyog, to do this.

• Peter Mortensen: Does it work with the strange dash (–) - near "all-databases" (also in the first revision)?
• Peter Mortensen: "–" is UTF-8 sequence for Unicode code point U+2013 (EN DASH)

Answer 24

Add the --force option:

mysql -u username -p database_name --force < file.sql

Answer 25

Import into the database:

mysql -u username -p database_name < /file path/file_name.sql

Export from the database:

mysqldump -u username -p database_name> /file path/file_name.sql

After these commands, a prompt will ask for your MySQL password.

Answer 26

Sometimes the port defined as well as the server IP address of that database also matters...

mysql -u user -p user -h <Server IP address> -P<port> (DBNAME) < DB.sql

Answer 27

The following command works for me from the command line (cmd) on Windows 7 on WAMP.

d:/wamp/bin/mysql/mysql5.6.17/bin/mysql.exe -u root -p db_name < database.sql

Answer 28

Providing credentials on the command line is not a good idea. The above answers are great, but neglect to mention

mysql --defaults-extra-file=etc/myhost.cnf database_name < file.sql

Where etc/myhost.cnf is a file that contains host, user, password, and you avoid exposing the password on the command line. Here is a sample,

[client]
host=hostname.domainname
user=dbusername
password=dbpassword

• George Birbilis: Command-line is volatile though (and unless you have a keylogger or a man-behind-your-back I'd expect it to be safe when executed locally), whereas a file is permanent, thus should be a higher risk, esp. when it is in plain text
• George Birbilis: ...however, the mysql command does indeed warn "mysql: [Warning] Using a password on the command line interface can be insecure."

Answer 29

Similarly to vladkras's answer to How do import an SQL file using the command line in MySQL?.

Key differences for me:

1. The database has to exist first
2. No space between -p and the password

---

shell> mysql -u root -ppassword #note: no space between -p and password
mysql> CREATE DATABASE databasename;
mysql> using databasename;
mysql> source /path/to/backup.sql

I am running Fedora 26 with MariaDB.

• Peter Mortensen: But this is not from the command line in the spirit of the question. It is using the MySQL shell interactively.

Answer 30

For information, I just had the default root + without password. It didn't work with all previous answers.

• I created a new user with all privileges and a password. It worked.

• -ppassword WITHOUT SPACE.