How do I import an SQL file using the command line in MySQL?

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?

possible duplicate of [Restore MYSQL Dump File with Command Line](http://stackoverflow.com/questions/5429748/restore-mysql-dump-file-with-command-line) — Bill Karwin, Jul 16 '13 at 00:47
possible duplicate of https://stackoverflow.com/questions/11407349/mysql-how-to-export-and-import-an-sql-file-from-command-line — AZinkey, Aug 29 '17 at 14:01
Whats with these duplicate guys? This indeed is a helpful question with its own purpose — Valentino Pereira, Jun 29 '18 at 12:59
@ValentinoPereira have you checked original question dates before determine duplicate guys — AZinkey, Jul 18 '19 at 07:36
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 — Nico Haase, Dec 10 '19 at 11:28
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. — Light.G, Oct 10 '20 at 16:26
To import ***efficiently*** (200 times faster), see [Paresh Behede's answer](https://stackoverflow.com/questions/17666249/how-do-i-import-an-sql-file-using-the-command-line-in-mysql/22855514#22855514). — Peter Mortensen, Oct 06 '21 at 13:22

score 4872 · Accepted Answer · edited Jan 27 '23 at 15:46

4872

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.

edited Jan 27 '23 at 15:46

reformed

4,505
11
62
88

answered Jul 16 '13 at 00:48

bansi

55,591
6
41
52

`sudo mysql -u username -p database_name < file.sql` works in some cases. – Christian Soto Dec 29 '21 at 16:31
8

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`."](https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_triggers) – panda-byte Mar 07 '22 at 15:03
What if I am using docker? :( – Raul Chiarella Jun 23 '23 at 19:29
You can use `docker exec -i mysql -u --password= database_name < file.sql` Reference: [Backup (and Restore) MySQL Data in Docker](https://makeshiftinsights.com/blog/backup-restore-docker-mysql-data/) – bansi Jun 26 '23 at 06:13

score 1028 · Answer 2 · edited Oct 06 '21 at 11:31

1028

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;

edited Oct 06 '21 at 11:31

Peter Mortensen

30,738
21
105
131

answered Jul 16 '13 at 00:48

vladkras

16,483
4
45
55

Is it me only one who has never been able to use `<` operator in mysql? (ubuntu18/20) – T.Todua Jun 10 '21 at 17:58
11

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. – Stijn de Witt Jul 21 '21 at 12:55
where we put backup-file.sql? what path it looks by default? – temirbek Apr 11 '22 at 09:33
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. – Swati Jun 24 '22 at 11:10
@StijndeWitt chances are that his linux distro has configured credentials in e.g. ~/.my.cnf file – MTP Oct 26 '22 at 04:16
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 – Christian Mar 15 '23 at 16:04

score 489 · Answer 3 · edited Oct 06 '21 at 12:28

489

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 ;

edited Oct 06 '21 at 12:28

Peter Mortensen

30,738
21
105
131

answered Apr 04 '14 at 06:47

Paresh Behede

5,987
1
18
13

10

Is there a way to do that in a single command line on the mysql command used for import? – Volomike Jan 21 '15 at 20:12
1

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. – davidkonrad Mar 10 '16 at 12:05
37

I agree that this is **the best answer**. The `autocommit=0` portion made a world of difference in terms of the speed. – ᴍᴇʜᴏᴠ May 23 '16 at 21:36
2

will the `autocommit=0` will work on larger files? like 8gb sql file. – newbie Dec 09 '16 at 05:06
5

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;`. – hashchange Jul 02 '18 at 12:32
1

what about routines and triggers? – Akshay Sep 28 '18 at 07:06
3

@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 – iateadonut Mar 17 '20 at 07:11
4

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***!!! – Peter Mortensen Oct 06 '21 at 13:17
For the sql path on windows, don't forget to use double backslash such as `C:\\Users\\soner\\Desktop\\file.sql` – Soner from The Ottoman Empire Feb 27 '23 at 21:49
how would you do this when importing to a new server where the files are creating the db as part of the import process? since the db doesn't yet exist, how can you turn of autocommit during import? – WhiteRau Jul 25 '23 at 13:14

score 201 · Answer 4 · answered Aug 18 '16 at 12:12

201

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

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

answered Aug 18 '16 at 12:12

Manoj Kumar

5,273
1
26
33

1

But [still very slow](https://stackoverflow.com/questions/17666249/how-do-i-import-an-sql-file-using-the-command-line-in-mysql/22855514#22855514)(?). – Peter Mortensen Oct 06 '21 at 16:22
@PeterMortensen - will depend upon the size of data, else normally it is fast. – Manoj Kumar Oct 07 '21 at 04:52

Ammad · Answer 5 · 2019-07-16T18:38:09.213

129

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;

edited Jul 16 '19 at 18:38

answered Nov 07 '18 at 01:59

Ammad

4,031
12
39
62

7

This will work without the 'use' command for dumps with multilpe db in it – Hayden Thring Nov 11 '18 at 21:24
1

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 – Wuelber Castillo Nov 13 '18 at 17:10
4

this also monitors the script execution, much better than the other answers – refex Oct 11 '19 at 08:08
But this is not from the command line in the spirit of the question. It is using the MySQL shell interactively. – Peter Mortensen Sep 19 '21 at 15:55
Duplicate of above answer – BSUK Apr 15 '22 at 22:54

score 95 · Answer 6 · edited Oct 06 '21 at 11:40

95

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

edited Oct 06 '21 at 11:40

Peter Mortensen

30,738
21
105
131

answered Mar 11 '14 at 11:26

Amrit Dhungana

4,371
5
31
36

29

There shouldn't be a space between `-p` and `password` – Ejaz Aug 19 '14 at 12:00
Jap. This would not work. Correct would be `mysql -u root -p"password" bank < bank.sql` – Armin Feb 22 '15 at 14:11
5

why you simply can't answer in one line? `mysql -u username -ppassword db_name < file.sql` – Naveed Jul 02 '15 at 11:11
7

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 – asgs Nov 14 '17 at 13:22
7

Especially because of `.bash_history` – Neil Aug 09 '18 at 23:03
I agree. Anycase in mysql 8.0 is possile to do this: [Disabling Interactive History](https://dev.mysql.com/doc/refman/8.0/en/mysql-tips.html#mysql-history) : mysql supports disabling the interactive history partially or fully, depending on the host platform. – GabrieleMartini Dec 10 '19 at 12:11
not just because of `.bash_history`, but also because anyone with the ability to view your running command line (via `/proc` or `ps`, etc) can see the password you entered. just say no to command line passwords – philraj Apr 09 '20 at 00:25

score 75 · Answer 7 · edited Jul 10 '19 at 14:40

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.

score 66 · Answer 8 · edited Mar 19 '15 at 18:10

66

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

edited Mar 19 '15 at 18:10

Peter Mortensen

30,738
21
105
131

answered Jan 02 '15 at 15:58

user4412947

677
5
2

2

ype the path of your mysql bin directory and press – Ramesh Pareek Jul 03 '17 at 11:03

score 53 · Answer 9 · edited Mar 21 '18 at 18:31

53

A solution that worked for me is below:

Use your_database_name;
SOURCE path_to_db_sql_file_on_your_local;

edited Mar 21 '18 at 18:31

Peter Mortensen

30,738
21
105
131

answered Aug 24 '16 at 06:02

Shiks

673
5
6

This worked for me using MySQL Command Line Client, after placing my sql file in the proper /bin directory view windows explorer. Thanks – klewis Jul 12 '17 at 13:29
3

Little slow but does not stop in between and don't say that MySQL server has gone away. – Jaskaran Singh Dec 19 '17 at 11:12

David · Answer 10 · 2022-04-13T14:19:22.917

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

score 46 · Answer 11 · edited Jul 10 '19 at 14:44

46

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

edited Jul 10 '19 at 14:44

Peter Mortensen

30,738
21
105
131

answered Jan 04 '17 at 17:42

Adeleke Akinade

640
6
13

Francesco Casula · Answer 12 · 2017-03-01T10:55:38.247

43

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

edited Mar 01 '17 at 10:55

answered Aug 24 '16 at 13:45

Francesco Casula

26,184
15
132
131

On macOS, I had to use `gzcat` instead of `zcat`. – Melebius Mar 10 '22 at 11:46

score 42 · Answer 13 · edited Mar 19 '15 at 17:57

42

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

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

edited Mar 19 '15 at 17:57

Peter Mortensen

30,738
21
105
131

answered Sep 08 '14 at 12:17

Tanmay Patel

1,770
21
28

5

I think it would be more helpful for the OP and further questions, when you add some explaination to your intension. – Reporter Sep 08 '14 at 13:59
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: http://wpy.me/en/blog/2-how-to-export-and-import-a-mysql-database-from-the-command-line-shell – wappy Oct 13 '14 at 12:25
D:\xampp\mysql\bin>mysql -u root -p opfedu_campuses < C:\Users\Raham\Desktop\opfedu_campuses.sql – Raham Feb 17 '23 at 07:14

score 38 · Answer 14 · edited Oct 06 '21 at 13:59

38

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

edited Oct 06 '21 at 13:59

Peter Mortensen

30,738
21
105
131

answered Dec 07 '20 at 05:31

Madhusanka Edirimanna

1,318
10
9

This actually worked for me. The suggestion with 4000+ votes didn't. – Mladen Jan 19 '21 at 13:50
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 – Zdeněk Gromnica Feb 08 '21 at 13:05
But this is not from the command line in the spirit of the question. It is using the MySQL shell interactively. – Peter Mortensen Sep 19 '21 at 15:56
`source` is not intended for importing databases, but rather for running a small number of SQL queries. https://stackoverflow.com/a/6163842 – reformed Jan 27 '23 at 15:54

score 36 · Answer 15 · edited Feb 08 '18 at 17:50

36

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

edited Feb 08 '18 at 17:50

Adeleke Akinade

640
6
13

answered May 25 '15 at 05:14

Leopathu

624
6
11

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 – Abhishek Singh Aug 09 '22 at 18:34

score 24 · Answer 16 · edited Oct 06 '21 at 12:43

24

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

edited Oct 06 '21 at 12:43

Peter Mortensen

30,738
21
105
131

answered Apr 17 '14 at 19:20

user3546602

241
2
2

score 23 · Answer 17 · edited Oct 06 '21 at 16:21

23

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

edited Oct 06 '21 at 16:21

Peter Mortensen

30,738
21
105
131

answered Aug 11 '16 at 05:02

David Silva Smith

11,498
11
67
91

What's the need to have `--force` here? – Joshua Pinter Aug 24 '23 at 18:39

score 20 · Answer 18 · edited Oct 06 '21 at 12:57

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

score 20 · Answer 19 · edited Feb 17 '20 at 14:50

20

For exporting a database:

mysqldump -u username -p database_name > file.sql

For importing a database:

mysql -u username -p database_name < file.sql

edited Feb 17 '20 at 14:50

Das_Geek

2,775
7
20
26

answered Feb 17 '20 at 09:41

user777388

311
2
5

score 18 · Answer 20 · edited Jul 02 '16 at 09:37

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.

edited Jul 02 '16 at 09:37

Peter Mortensen

30,738
21
105
131

answered Sep 02 '15 at 23:41

Reuben

4,136
2
48
57

score 17 · Answer 21 · edited Oct 06 '21 at 12:46

17

Import a database

Go to drive:
```
 d:
```
MySQL login
```
 c:\xampp\mysql\bin\mysql -u root -p
```
It will ask for pwd. Enter it:
```
 pwd
```
Select the database
```
 use DbName;
```
Provide the file name
```
 \.DbName.sql
```

edited Oct 06 '21 at 12:46

Peter Mortensen

30,738
21
105
131

answered Jul 31 '14 at 05:55

Pritam Chaudhari

789
10
10

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](https://stackoverflow.com/posts/25051722/edit), not here in comments (***without*** "Edit:", "Update:", or similar - the question/answer should appear as if it was written today). – Peter Mortensen Oct 06 '21 at 12:45

score 17 · Answer 22 · edited Jul 10 '19 at 14:39

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

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 — Ashok G, Dec 09 '22 at 02:03

score 11 · Answer 23 · edited Mar 19 '15 at 17:49

11

Go to the directory where you have MySQL.

 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.

edited Mar 19 '15 at 17:49

Peter Mortensen

30,738
21
105
131

answered Jul 16 '13 at 04:26

Sathish D

4,854
31
44

Does it work with the strange dash (–) - near *"all-databases"* (also in the first revision)? – Peter Mortensen Sep 21 '21 at 20:44

score 11 · Answer 24 · edited Apr 11 '19 at 12:24

11

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/

edited Apr 11 '19 at 12:24

answered Apr 08 '18 at 09:32

Md Shariful Islam

157
2
8

score 9 · Answer 25 · edited Jul 02 '16 at 09:40

9

Add the --force option:

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

edited Jul 02 '16 at 09:40

Peter Mortensen

30,738
21
105
131

answered Aug 24 '14 at 18:17

ktaria

423
6
16

score 8 · Answer 26 · edited Sep 21 '21 at 21:43

8

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

edited Sep 21 '21 at 21:43

Peter Mortensen

30,738
21
105
131

answered May 13 '14 at 07:49

JohnSharath

101
1
1

score 8 · Answer 27 · edited Mar 19 '15 at 18:14

8

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

edited Mar 19 '15 at 18:14

Peter Mortensen

30,738
21
105
131

answered Mar 11 '15 at 15:38

victor

81
1
1

score 8 · Answer 28 · answered Feb 25 '17 at 01:18

8

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

answered Feb 25 '17 at 01:18

ChuckCottrill

4,360
2
24
42

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 Mar 29 '18 at 22:08
2

...however, the mysql command does indeed warn "mysql: [Warning] Using a password on the command line interface can be insecure." – George Birbilis Mar 29 '18 at 22:24

score 8 · Answer 29 · edited Jul 10 '19 at 14:42

8

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

Key differences for me:

The database has to exist first
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.

edited Jul 10 '19 at 14:42

Peter Mortensen

30,738
21
105
131

answered Aug 08 '17 at 19:30

jozxyqk

16,424
12
91
180

But this is not from the command line in the spirit of the question. It is using the MySQL shell interactively. – Peter Mortensen Sep 19 '21 at 15:54

score 8 · Answer 30 · edited Jul 10 '19 at 14:37

8

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.

edited Jul 10 '19 at 14:37

Peter Mortensen

30,738
21
105
131

answered Jan 05 '18 at 13:28

NeeruKSingh

1,545
3
22
26

score 7 · Answer 31 · edited Mar 19 '15 at 17:58

7

I thought it could be useful for those who are using Mac OS X:

/Applications/xampp/xamppfiles/bin/mysql -u root -p database < database.sql

Replace xampp with mamp or other web servers.

edited Mar 19 '15 at 17:58

Peter Mortensen

30,738
21
105
131

answered Nov 09 '14 at 18:49

Giri

2,704
2
25
27

score 7 · Answer 32 · edited Jul 10 '19 at 14:46

7

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.

edited Jul 10 '19 at 14:46

Peter Mortensen

30,738
21
105
131

answered Dec 29 '16 at 14:40

Paul Leclerc

1,117
1
14
18

score 6 · Answer 33 · edited Mar 19 '15 at 17:56

For backup purposes, make a BAT file and run this BAT file using Task Scheduler. It will take a backup of the database; just copy the following line and paste in Notepad and then save the .bat file, and run it on your system.

@echo off
for /f "tokens=1" %%i in ('date /t') do set DATE_DOW=%%i
for /f "tokens=2" %%i in ('date /t') do set DATE_DAY=%%i
for /f %%i in ('echo %date_day:/=-%') do set DATE_DAY=%%i
for /f %%i in ('time /t') do set DATE_TIME=%%i
for /f %%i in ('echo %date_time::=-%') do set DATE_TIME=%%i

"C:\Program Files\MySQL\mysql server 5.5\bin\mysqldump" -u username -ppassword mysql>C:/%DATE_DAY%_%DATE_TIME%_database.sql

score 5 · Answer 34 · edited Jul 10 '19 at 14:43

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.

score 4 · Answer 35 · edited Mar 19 '15 at 18:12

4

The following steps help to upload file.sql to the MySQL database.

Step 1: Upload file.sql.zip to any directory and unzip there
Note: sudo apt-get install unzip : sudo apt-get unzip file.sql.zip
Step 2: Now navigate to that directory. Example: cd /var/www/html

Step 3: mysql -u username -p database-name < file.sql
Enter the password and wait till uploading is completed.

edited Mar 19 '15 at 18:12

Peter Mortensen

30,738
21
105
131

answered Jan 16 '15 at 10:22

Ramesh Sinha

61
1
1

score 4 · Answer 36 · edited Jul 10 '19 at 14:33

4

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

edited Jul 10 '19 at 14:33

Peter Mortensen

30,738
21
105
131

answered Mar 11 '19 at 14:30

Hamfri

1,979
24
28

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. – Himanshu Upadhyay Mar 26 '19 at 09:17

score 4 · Answer 37 · edited Jun 20 '20 at 09:12

4

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered May 22 '19 at 06:44

Dev Semicolon

444
3
11

score 4 · Answer 38 · edited Oct 06 '21 at 13:43

This line imports the dump file in the local database, under Linux.

mysql -u dbuser -p'password including spaces' dbname < path/to/dump_file.sql

This line imports the dump file in the remote database, under Linux. Note: -P is for the port and is required if the MySQL port is different than the default.

mysql -h dbhost -u dbuser -p'password including spaces' -P 3306 dbname < path/to/dump_file.sql

Note: the password includes spaces and this is the reason of the single quotes. Just change the path style for using the command under Windows (C:\windows\path\dump_file.sql).

score 4 · Answer 39 · answered Jan 10 '21 at 07:09

4

Using MySQL Secure Shell:

mysqlsh -u <username> -p -h <host> -D <database name> -f dump.sql

answered Jan 10 '21 at 07:09

skoll

2,272
4
30
33

Mahdi Bashirpour · Answer 40 · 2021-06-02T08:12:24.940

3

If you use XAMPP on the windows, first, you must manually create the database and then run the following commands:

cd C:\xampp\mysql\bin && 
mysql -u YOUR_USERNAME -p YOUR_DATABASE_NAME < PATH_TO_YOUR_SQL_FILE\YOUR_SQL_FILE.sql

And then enter the password

edited Jun 02 '21 at 08:12

answered Aug 09 '18 at 13:22

Mahdi Bashirpour

17,147
12
117
144

score 2 · Answer 41 · edited Jul 10 '19 at 14:35

2

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.

edited Jul 10 '19 at 14:35

Peter Mortensen

30,738
21
105
131

answered Sep 24 '18 at 13:55

Ripudaman Singh

363
3
10

score 2 · Answer 42 · edited Jul 10 '19 at 14:34

2

You can use:

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

Example:

mysql -uroot -proot db < db.sql

edited Jul 10 '19 at 14:34

Peter Mortensen

30,738
21
105
131

answered Oct 12 '18 at 14:01

Rafael Andrs Cspedes Basterio

3,953
1
17
12

score 2 · Answer 43 · edited Oct 06 '21 at 13:50

2

Create a database in MySQL.
Then go to your computer directory C:\xampp\mysql\bin, write cmd in the address bar, and hit Enter.
Unzip your SQL file
Then write: mysql -u root -p dbname and press Enter.
Write: source sql.file. Like Source C:\xampp\htdocs\amarbazarltd\st1159.sql
Done

edited Oct 06 '21 at 13:50

Peter Mortensen

30,738
21
105
131

answered Sep 23 '20 at 10:12

nafischonchol

104
1
9

Upvote for the Windows trick of getting cmd to open in the correct directory. – Clinton May 26 '21 at 14:58
Re *"go to your computer directory"*: Do you mean in [File Explorer](https://en.wikipedia.org/wiki/File_Explorer)? – Peter Mortensen Oct 06 '21 at 13:51
@PeterMortensen Yes File Explorer – nafischonchol Jan 27 '22 at 14:54

score 2 · Answer 44 · edited Oct 06 '21 at 13:52

2

Simple. Just use this command in cmd:

use databasename
\. C:/test/data.mysql

edited Oct 06 '21 at 13:52

Peter Mortensen

30,738
21
105
131

answered Dec 05 '20 at 03:10

BugsCreator

433
3
10

There [isn't a "`use`" in cmd](https://ss64.com/nt/) (Windows) as far as I know (though there is [`NET USE`](https://ss64.com/nt/net-use.html)). Do you mean inside the MySQL client? Or something else? Can you elaborate? Please respond by [editing (changing) your answer](https://stackoverflow.com/posts/65153385/edit), not here in comments (***without*** "Edit:", "Update:", or similar - the answer should appear as if it was written today). – Peter Mortensen Oct 06 '21 at 13:57

score 1 · Answer 45 · edited Mar 19 '15 at 18:06

1

If you are using MAMP on Mac OS X, this may be helpful:

/applications/MAMP/library/bin/mysql -u MYSQL_USER -p DATABASE_NAME < path/to/database_sql/FILE.sql

MYSQL_USER is root by default.

edited Mar 19 '15 at 18:06

Peter Mortensen

30,738
21
105
131

answered Dec 03 '14 at 23:36

NicO

67
1
2

score 1 · Answer 46 · edited May 23 '17 at 12:18

1

If your folder has multiple SQL files, and you've installed Git Bash you can use this command to import multiple files:

cd /my-project/data

cat *.sql | /c/xampp/mysql/bin/mysql -u root -p 1234 myProjectDbName

edited May 23 '17 at 12:18

Community

1
1

answered Dec 20 '15 at 02:22

o0omycomputero0o

3,316
4
31
45

score 1 · Answer 47 · edited Oct 06 '21 at 16:21

1

Export particular databases:

mysqldump --user=root --host=localhost --port=3306 --password=test -B CCR KIT > ccr_kit_local.sql

This will export CCR and KIT databases...

Import all exported databases to a particular MySQL instance (you have to be where your dump file is):

mysql --user=root --host=localhost --port=3306 --password=test < ccr_kit_local.sql

edited Oct 06 '21 at 16:21

Peter Mortensen

30,738
21
105
131

answered Apr 19 '16 at 12:36

Musa

2,596
26
25

score 1 · Answer 48 · edited Jul 10 '19 at 14:36

1

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

edited Jul 10 '19 at 14:36

Peter Mortensen

30,738
21
105
131

answered Feb 13 '18 at 09:44

Edgencio Da Calista

505
3
8

score 1 · Answer 49 · edited Jul 10 '19 at 14:29

1

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

edited Jul 10 '19 at 14:29

Peter Mortensen

30,738
21
105
131

answered Jun 23 '19 at 03:13

Shabeer K

1,489
16
23

But this is not from the command line in the spirit of the question. It is using the MySQL shell interactively. – Peter Mortensen Sep 19 '21 at 15:56

score 0 · Answer 50 · edited Oct 06 '21 at 13:46

0

If you are using XAMPP then go to folder xappp → mysql → bin. Open cmd here and paste this:

mysql -u root -p dbname < dbfilename.sql

edited Oct 06 '21 at 13:46

Peter Mortensen

30,738
21
105
131

answered Jul 01 '20 at 08:04

Abid Shah

325
3
5

score 0 · Answer 51 · edited Oct 06 '21 at 14:03

You can use these steps as easily.

Download the SQL file into your "mysql/bin" folder.
Open the "mysql/bin" folder using CMD.
If not exists required database, then create the database first.
Type this in the CMD and run:

mysql -u <user> -p<password> <dbname> < file.sql

"file.sql" is an SQL file that you want to insert into the target database. examples: If your "password" is "1234", "user" is "root", and "dbname" is "test":
```
 mysql -u root -p1234 test < file.sql
```
If your "password" is null & "user" is "root" & "dbname" is "test"
```
 mysql -u root test < file.sql
```
Check the target data successfully uploaded or not.

This method can be used to upload the large size data using SQL files in the CMD.

Make sure in step 4, if you use that password, insert "-p" as a single word without any spaces.

score 0 · Answer 52 · answered Jan 28 '23 at 03:45

Most of the answers include > or < characters which is not a proper method for all the cases. I recommend using mysqlimport while you may make the dump file using mysqldump.

These tools will be installed with the mysql service and both are available for backup and restore in a database or multiple databases in MySQL.

Here is the way you could leverage it for importing to the mysql

mysqlimport -u database_admin -p database_name ~/path/to/dump_file.sql

In case you do not have it, please install it via:

sudo apt update sudo apt install mysql-client

In the same way, you make a backup to a dump file as follows:

mysqldump [options] --result-file=dump_file.sql

score -2 · Answer 53 · answered May 15 '23 at 04:50

-2

1- copy db file to C:\xampp\mysql\bin

2- open terminal from same path

3- type in terminal

.\mysql.exe -uroot

4- type use DB_NAME;//change to your DB Name

5- type source DB_FILE.sql;

answered May 15 '23 at 04:50

Mostafa Mahmoud

153
3
7

score -3 · Answer 54 · edited Oct 06 '21 at 13:38

-3

Try this:

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

edited Oct 06 '21 at 13:38

Peter Mortensen

30,738
21
105
131

answered Jul 11 '19 at 05:47

Amarat

602
5
11

3

Please add some explanation to your answer - why did the given call resolve syntax errors? – Nico Haase Dec 10 '19 at 11:27

score -4 · Answer 55 · edited Oct 06 '21 at 13:39

-4

Go to your wamp or xampp directory

Example
```
cd d:/wamp/bin/mysql/mysql5.7.24/bin
```
mysql -u root -p DATABASENAME < PATHYOUDATABASE_FILE

edited Oct 06 '21 at 13:39

Peter Mortensen

30,738
21
105
131

answered Jul 29 '19 at 07:50

Naveen Gaur

421
4
4

How do I import an SQL file using the command line in MySQL?

55 Answers55

Import a database

To import a database via the terminal

Linked

Related