By Jaylen


2013-07-16 00:43:48 8 Comments

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?

30 comments

@Hasib Kamal 2019-10-28 09:50:06

Easy solution : Access mysql from terminal and then....

1) Database selection

mysql> use database_name;

2) Importing execution

mysql> source exported_file.sql;
example : mysql> source /var/www/html/db/my_database.sql;

@Naveen dev 2019-07-29 07:50:47

1) Go to your wamp or xampp directory Example

cd d:/wamp/bin/mysql/mysql5.7.24/bin

2) mysql -u root -p DATABASENAME < PATHYOUDATABASE_FILE

@Ammad 2018-11-07 01:59:56

Easiest way to import into your schema:

Login to mysql and issue below mention commands.

mysql> use your_db_name;

mysql> source /opt/file.sql;

@Hayden Thring 2018-11-11 21:24:00

This will work without the 'use' command for dumps with multilpe db in it

@Wuelber Castillo 2018-11-13 17:10:34

I was trying to import a dump from a database with a different name but with the same structure, the right answer picked by the author didn't work, it created a new database named after the database in the dump file. This answer right here did what I wanted, thanks man

@Sergei Zahharenko 2018-11-23 08:24:43

great! exaclty what i was looking for!

@refex 2019-10-11 08:08:18

this also monitors the script execution, much better than the other answers

@Amarat 2019-07-11 05:47:45

Try this:-

C:\xampp\mysql\bin>mysql -u root -p database_name --force < C:\file.sql

@Paul Leclerc 2016-12-29 14:40:42

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.

@Adeleke Akinade 2017-01-04 17:42:50

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

@Fery Wardiyanto 2017-03-12 22:33:25

I'm using Windows 10 with PowerShell 5 and I found almost all "Unix-like" solutions not working for me.

> mysql -u[username] [database-name] < my-database.sql
At line:1 char:31
+ mysql -u[username] [database-name] < my-database.sql
+                               ~
The '<' operator is reserved for future use.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : RedirectionNotSupported

I ends up using this command:

> type my-database.sql | mysql -u[username] -h[localhost] -p [database-name]

And it works perfectly, and hopefully it helps.

Thanks to @Francesco Casula's answer, BTW.

@jozxyqk 2017-08-08 19:30:03

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.

@YumYumYum 2017-11-19 21:23:16

VERY GOOD. I need this way of import.

@Kasun Siyambalapitiya 2017-09-01 10:21:04

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.

@Sumanth Lazarus 2019-10-29 07:44:22

Your answer had a ground-up solution for MySQL beginners like me: 1. Create the Database, if one doesn't exist. 2. Point the source file to the created Database to import data/meta-data.

@Siva Praveen 2017-12-18 18:40:09

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 2019-03-28 02:51:33

For Centos: yum install pv

@NeeruSingh 2018-01-05 13:28:03

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.

@Edgencio Da Calista 2018-02-13 09:44:00

If you are importing to your local database server, you can do the following:

mysql -u database_user -p < database_file.sql

For a remote database server do the follwing:

mysql -u database_user -p -h remote_server_url < database_file.sql

@Ripudaman Singh 2018-09-24 13:55:07

For Windows OS, you can use the below command to import data from an SQL dump.

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u<> -p<> DBName < filelocation\query.sql

Where -u is the username, and -p is the MySQL password. Then enter your password and wait for data to import.

@Rafael Andrs Cspedes Basterio 2018-10-12 14:01:05

You can use:

mysql -u<user> -p<pass> <db> < db.sql

Example:

mysql -uroot -proot db < db.sql

@Hamfri 2019-03-11 14:30:22

If importing data into a Docker container use the following command. Adjust user(-u), database(-D), port(-P) and host(-h) to fit your configuration.

mysql -u root -D database_name -P 4406 -h localhost --protocol=tcp -p < sample_dump.sql

@Himanshu Upadhyay 2019-03-26 09:17:47

I needed the host name to mention because localhost was not the DB host name in my case. So this syntax helped me. Voting it up.

@Dev Semicolon 2019-05-22 06:44:01

To import a database via the terminal

Navigate to folder where the .sql file is located

Then run the below command:

mysql -u database_user_name -p database_name < sql_file_name.sql

It will ask for a password. Enter the database password. It will take a few seconds to import the data into the database.

@Shabeer Sha 2019-06-23 03:13:33

In Ubuntu

 mysql -u root -p
 CREATE database dbname;
 use dbname;
 source /home/computername/Downloads/merchantapp.sql
 exit;

In Windows

Download the SQL file and save it in C:\xampp\mysql\bin.

After that, open the command prompt with C:\xampp\mysql\bin:

 C:\xampp\mysql\bin> mysql -u username -p database_name < file.sql

@Md Shariful Islam 2018-04-08 09:32:02

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/

@Mahdi Bashirpour 2018-08-09 13:22:49

For example

I installed the software on C:\xampp\mysql\bin and my file is at address C:\Users\file.sql

Just set username and database_name

Note: First you need to create the database(database_name) manually

Run this command:

cd C:\xampp\mysql\bin && mysql -u username -p database_name < C:\Users\file.sql

And then enter password 😊

@Imanali Mamadiev 2018-10-11 09:01:23

first smile that I see in stOverF

@bansi 2013-07-16 00:48:01

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 to keep the routines and triggers of 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 don't contain CREATE DATABASE (exported with --no-create-db or -n option), before you can import it.

@Jaylen 2013-07-16 01:37:45

When I run the file from a program like Toad I get no error but when I run it from the command line i get the error I mentioned

@shasi kanth 2014-04-21 12:25:24

I guess we have to run this command as linux root user.

@bansi 2014-04-21 12:43:45

This is no way related to OS user, so no need for linux root user, username just need create database permission in mysql.

@Skippy le Grand Gourou 2015-08-08 14:29:51

Note that yes, you have to create the (empty) database from mysql if it doesn't exist already, before you can import it.

@user3383675 2016-04-06 06:29:56

How to do this command, but with limit for number of rows?

@bansi 2016-04-06 06:41:38

@user3383675 I don't think there is an option for limiting the number of rows. You may have to modify the sql file or create a new file with only the rows you need.

@IlPADlI 2016-06-29 09:14:23

but becareful file charset or encoding, it may cause execute fail on half.

@polkovnikov.ph 2016-10-31 11:30:46

@bansi Typing is OK unless someone unwanted is behind or you forget to add a space symbol ahead of the command so that it doesn't appear in bash history.

@Qasim 2016-12-20 06:06:12

Yups, remember to create an empty Database (if the db doesn't previously exist), from the MySQL console like so: CREATE DATABASE db-name;

@Shaakir 2017-02-28 11:55:27

Be sure to check that the backup file does not have "use 'original_db'" in it. Although i specified restore to new DB "mysql new_db < dump.sql" , the sql dump file had that in, and it overwrote my live DB. Not sure if there is an option to force it to use the DB specified and disregard the "use 'original_db'" in the sql file

@jx12345 2017-03-08 07:09:35

Why is it better to type the full path to the sql file?

@Walf 2017-05-25 02:50:12

@jx12345 It's not, mysql never even sees the filename when doing shell redirection. You should instead be using the source command from the other answer.

@bansi 2017-05-25 03:47:05

@Walf source command won't work unless you are using an interactive shell, or emulating one. shell redirection works on all standard operating systems, if you have read permission on the sql file.

@bansi 2017-05-25 03:48:54

@jx12345 if you won't specify the full path mysql will look for the sql file in the current directory (where mysql command was started).

@Joe 2017-09-08 20:32:39

Worked for me, but I had to include a password (since my root has a password). It only seemed to work if I had no space between the user and the -u and password and the -p. Just like this sudo mysql -uroot -ppassword mydb < /home/self/Desktop/mysql.sql

@NotJay 2017-09-13 19:54:11

@Joe, yes, if you have a space between '-p' and 'password', it thinks that 'password' is the database name. Alternatively, you can type '-p' without your password and then you'll be prompted for a password when you hit enter.

@Ian 2017-10-31 07:48:28

How do you stop the operation if I forgot to set the database or table?

@bansi 2017-11-01 04:22:25

you can send SIGINT (Ctrl + C) to stop the process in middle, and in my experience MySQL always handles the signals gracefully. But caution your database may have partial data if the SQL is not inside a transaction.

@Ciantic 2018-01-31 10:59:03

It is recommended to include --default-character-set=utf8 if your charset is utf8, otherwise bad things may happen.

@briankip 2018-02-14 06:32:05

If the db is not yet created you can run mysql -u username -p -e 'create schema databasename', et voila.

@user9050678 2018-11-09 11:01:17

don't forget the put double quotes around the file path if it has space

@Low 2019-05-31 07:57:11

This worked so well for me where imports failed in every GUI application I used.

@Freedo 2019-07-26 07:29:01

Where in the syntax I add the -R and --triggers? be more specific please

@David 2018-03-21 21:04:47

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

Surley 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 separated 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 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.

@vladkras 2013-07-16 00:48:35

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

shell> mysqldump db_name > backup-file.sql

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

UNIX

shell> mysql db_name < backup-file.sql

The same in Windows command prompt:

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

PowerShell

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

MySQL command line

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

@vladkras 2013-07-16 01:27:21

@Mike Are you trying to launch it in mysql> command line? You should use shell instead.

@Jaylen 2013-07-16 01:34:58

How do I use Shell? I am using mysql5.6 on windows Server 2008

@Jaylen 2013-07-16 01:41:10

Using Windows PowerShell typing MySQL db_name < file.sql I get the following error the '<' operator is reserved fir future use

@vladkras 2013-07-16 01:57:34

Jackie advises & cmd.exe /c "mysql -u root -p Tutorials < tut_backup.sql"

@rikkitikkitumbo 2016-11-10 00:17:47

+1 for not having to use mysqlimport... unfortunately I just installed mysql-client... I knew there was a better way.

@Bernard Chen 2016-11-29 19:53:58

Be careful about using the > to redirect the mysqldump output to a file, especially if you want control over the encoding. Instead, you probably want to use the --result-file parameter so that the encoding is not controlled by the shell/OS.

@Sahil Patel 2017-11-20 09:51:34

I never thought about this easiest way to restore the database (1.5GB) in mysql. Thank you for sharing

@JacobRossDev 2018-05-26 22:15:39

Thank you for showing command source at mysql prompt

@Yeshu Kochher 2019-04-11 07:38:45

MySQL command line works for me. Thank you for the help.

@Charles Wood 2019-10-29 14:17:58

Why the cmd /c in Powershell?

@Abdul Rehman Janjua 2015-05-16 11:13:43

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:

#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

@Shiks 2016-08-24 06:02:39

A solution that worked for me is below:

Use your_database_name;
SOURCE path_to_db_sql_file_on_your_local;

@klewis 2017-07-12 13:29:33

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 2017-12-19 11:12:43

Little slow but does not stop in between and don't say that MySQL server has gone away.

@Leopathu 2015-05-25 05:14:00

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

@BharathRao 2018-09-25 07:19:25

mysql -u username -p password < dump.sql helped me a lot thank you!

@Francesco Casula 2016-08-24 13:45:38

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

@ChuckCottrill 2017-02-25 01:18:48

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 2018-03-29 22:08:52

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 2018-03-29 22:24:47

...however, the mysql command does indeed warn "mysql: [Warning] Using a password on the command line interface can be insecure."

@Paresh Behede 2014-04-04 06:47:33

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 2015-01-21 20:12:44

Is there a way to do that in a single command line on the mysql command used for import?

@davidkonrad 2016-03-10 12:05:25

Best answer inho. It was the source command I have forgotten. Most of us want to do this while we are logged in as standalone command among other commands, not the standard login>inject>logout oneliner in top of google serps.

@aexl 2016-05-23 21:36:27

I agree that this is the best answer. The autocommit=0 portion made a world of difference in terms of the speed.

@newbie 2016-12-09 05:06:11

will the autocommit=0 will work on larger files? like 8gb sql file.

@hashchange 2018-07-02 12:32:19

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;.

@Akshay 2018-09-28 07:06:54

what about routines and triggers?

@Manoj Kumar 2016-08-18 12:12:06

Among all the answers, for the problem above, this is the best one:

 mysql> use db_name;
 mysql> source file_name.sql;

@luky 2018-09-30 16:41:06

advise above failed on error that db already exists but this seems to works

@David Silva Smith 2016-08-11 05:02:36

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

Related Questions

Sponsored Content

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

58 Answered Questions

[SOLVED] How do I include a JavaScript file in another JavaScript file?

24 Answered Questions

[SOLVED] Is there an equivalent of 'which' on the Windows command line?

22 Answered Questions

[SOLVED] How can I extract a predetermined range of lines from a text file on Unix?

8 Answered Questions

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

31 Answered Questions

[SOLVED] How do I parse command line arguments in Bash?

17 Answered Questions

[SOLVED] How do I restore a dump file from mysqldump?

  • 2008-09-19 21:27:00
  • Zack Peterson
  • 797022 View
  • 524 Score
  • 17 Answer
  • Tags:   mysql sql database

11 Answered Questions

[SOLVED] Run a PostgreSQL .sql file using command line arguments

15 Answered Questions

[SOLVED] How to get a list of user accounts using the command line in MySQL?

Sponsored Content