Import SQL file into mysql

Question

I have a database called nitm. I haven't created any tables there. But I have a SQL file which contains all the necessary data for the database. The file is nitm.sql which is in C:\ drive. This file has size of about 103 MB. I am using wamp server.

I have used the following syntax in MySQL console to import the file:

mysql>c:/nitm.sql;

But this didn't work.

See https://stackoverflow.com/questions/17666249/how-to-import-an-sql-file-using-the-command-line-in-mysql — That Brazilian Guy, Jun 22 '17 at 14:22
check https://stackoverflow.com/questions/11407349/mysql-how-to-export-and-import-a-sql-file-from-command-line — AZinkey, Nov 11 '17 at 13:07

score 412 · Answer 1 · edited Apr 18 '16 at 06:31

412

From the mysql console:

mysql> use DATABASE_NAME;

mysql> source path/to/file.sql;

make sure there is no slash before path if you are referring to a relative path... it took me a while to realize that! lol

edited Apr 18 '16 at 06:31

chanchal118

3,151
2
22
49

answered Sep 28 '12 at 23:01

d-_-b

18,782
33
120
200

36

You don't need a slash before the path if you're making a relative reference. If you're going from the root you will, ie `/home/user/file.sql` – Wes Johnson Aug 15 '13 at 21:41
5

What a pity there's no way to add an answer to favourites :( – baldrs Dec 17 '13 at 18:44
4

In Chrome they are called bookmarks, and you can just click the star in address bar ;) – Andrew Dec 29 '13 at 00:17
you saved my life :) thanks! ... this was for me also solution for Error Code: 1406. Data too long for column - MySQL – rackom Jan 16 '14 at 09:16
2

@ArseniuszŁozicki add the question and you will be able to reach answer :) that is what i do. – mandza Jan 30 '14 at 07:48
Thanks for this answer, it's much easier to remember than the other ways to import – Michael Camden Dec 15 '14 at 18:59
2

Just to add to this answer that the `path/to/file.sql` is the local file system path where the mysql *client* is at. There are times where MySQL server expect the path to be on the server side, I.E. `SELECT ... INTO OUTFILE` which drops the file into the path on the MySQL server! – Devy May 26 '15 at 16:17
I found that the relative path is where you invoke the mysql command. – Dan Sandland Sep 23 '15 at 22:55
is there a way to disable the verbosity when doing it like that? – fritzmg Jan 25 '16 at 16:40
Also,don't forget to delete the .sql file after upload – phil Aug 02 '17 at 06:21
I wonder if `source` command works on other mysql clients, like `libmysqlclient` and other embedded clients in various languages. – CMCDragonkai Aug 04 '17 at 12:34

score 91 · Accepted Answer · answered Mar 01 '11 at 11:40

91

Finally, i solved the problem. I placed the `nitm.sql` file in `bin` file of the `mysql` folder and used the following syntax.

C:\wamp\bin\mysql\mysql5.0.51b\bin>mysql -u root nitm < nitm.sql

And this worked.

answered Mar 01 '11 at 11:40

kushalbhaktajoshi

4,462
3
20
37

8

This is not from the mysql console as OP asked, but from a console into mysql. The answer from Robert Anthony Tribiana does describe how to do it from the mysql console. – halfpastfour.am Jun 19 '14 at 09:10
This is not the exact way to solve the problem, it's not a solution. – Ashish Ratan Nov 12 '15 at 10:50
4

You guys know that this was the OP who answered this? – Wes Feb 01 '16 at 14:20
If your mysql need a password, then is method won't work, you need the top answer of @d-_-b – shellbye Apr 29 '16 at 12:08

score 52 · Answer 3 · edited Mar 14 '13 at 02:12

If you are using wamp you can try this. Just type use your_Database_name first.

Click your wamp server icon then look for MYSQL > MSQL Console then run it.
If you dont have password, just hit enter and type :
```
mysql> use database_name;
mysql> source location_of_your_file;
```
If you have password, you will promt to enter a password. Enter you password first then type:
```
mysql> use database_name;
mysql> source location_of_your_file;
```

location_of_your_file should look like C:\mydb.sql

so the commend is mysql>source C:\mydb.sql;

This kind of importing sql dump is very helpful for BIG SQL FILE.

I copied my file mydb.sq to directory C: .It should be capital C: in order to run

and that's it.

This answer seems WAMP specific while the shown code works from any environment running MySQL where you can access MySQL via a terminal or a console. — halfpastfour.am, Jun 19 '14 at 09:12

score 26 · Answer 4 · edited Apr 24 '15 at 10:56

26

In windows, if the above suggestion gives you an error (file not found or unknown db) you may want to double the forward slashes:

In the mysql console:

mysql> use DATABASE_NAME;

mysql> source C://path//to//file.sql;

edited Apr 24 '15 at 10:56

Tamil Selvan C

18,342
12
44
63

answered Jan 21 '14 at 12:38

user3219217

261
3
2

1

Single forward slash worked for me in my Windows machine. – Manoj Shrestha May 16 '16 at 18:08

score 17 · Answer 5 · answered Oct 10 '15 at 12:02

Ok so, I'm using Linux but I think this holds true for Windows too. You can do this either directly from the command prompt

> mysql -u <user name> -p<password> <database name> < sqlfilename.sql

Or from within the mysql prompt, you can use:

mysql>source sqlfilename.sql

But both these approaches have their own benefits in the results they display. In the first approach, the script exits as soon as it encounters an error. And the better part, is that it tells you the exact line number in the source file where the error occurred. However, it ONLY displays errors. If it didn't encounter any errors, the scripts displays NOTHING. Which can be a little unnerving. Because you're most often running a script with a whole pile of commands.

Now second approach (from within the mysql prompt) has the benefit that it displays a message for every different MySQL command in the script. If it encounters errors, it displays the mysql error message but continues on through the scripts. This can be good, because you can then go back and fix all the errors before you run the script again. The downside is that it does NOT display the line numbers in the script where the errors were encountered. This can be a bit of a pain. But the error messages are as descriptive so you could probably figure out where the problem is.

I, for one, prefer the directly-from-OS-command line approach.

score 9 · Answer 6 · answered Sep 24 '13 at 10:21

9

If you are using xampp

C:\xampp\mysql\bin\mysql -uroot -p nitm < nitm.sql

answered Sep 24 '13 at 10:21

Nanhe Kumar

12,767
3
67
60

score 7 · Answer 7 · answered Mar 01 '11 at 10:04

7

You are almost there use

mysql> \. c:/nitm.sql;

You may also access help by

mysql> \?

answered Mar 01 '11 at 10:04

Lmwangi

2,203
1
16
25

1

This didn't work. When I used mysql> \. c:/nitm.sql; , it threw error like Failed to open file 'c:\nitm.sql;', error: 2 – kushalbhaktajoshi Mar 01 '11 at 10:20
Have you tried variations of this? Some applications respect C:\nitm.sql – Lmwangi Mar 01 '11 at 10:25
Also note the semicolon at the end is not required. So remove it. Another option is 'cd c:' then try "\. nitm.sql" – Lmwangi Mar 01 '11 at 10:26
1

Please think before you type. 'cd c:' is the command for DOS prompt not mySql console. – kushalbhaktajoshi Mar 01 '11 at 10:48
1

`mysql> \. ~/Downloads/backups/file.sql` worked perfectly within mysql> and from terminal `coosal` answer worked also. – gmuhammad Sep 02 '12 at 09:05

Adrian P. · Answer 8 · 2016-01-06T18:21:56.533

6

For localhost on XAMPP. Open a cmd window and type

cd C:\xampp\mysql\bin
mysql.exe -u root -p

Attention! No semi-colon after -p Enter your password and type

use database_name;

to select the database you need.

Check if your table is there

show tables;

Import from your sql file

source sqlfile.sql;

I have put my file on C:\xampp\mysql\bin location in order to don't mix up with locations of sql file.

edited Jan 06 '16 at 18:21

answered Jan 06 '16 at 18:08

Adrian P.

4,484
1
40
43

score 4 · Answer 9 · answered Apr 21 '17 at 19:42

4

Try:

mysql -u username -p database_name < file.sql

Check MySQL Options.

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

answered Apr 21 '17 at 19:42

Akshay Pethani

1,802
2
25
35

score 3 · Answer 10 · edited Apr 24 '15 at 10:56

3

In Linux I navigated to the directory containing the .sql file before starting mysql. The system cursor is now in the same location as the file and you won't need a path. Use source myData.sql where my date is replaced with the name of your file.

cd whatever directory

mysql - p

connect targetDB

source myData.sql

Done

edited Apr 24 '15 at 10:56

Tamil Selvan C

18,342
12
44
63

answered May 09 '14 at 18:25

Robert Quinn

582
5
10

score 3 · Answer 11 · answered Jan 24 '15 at 14:34

3

Don't forget to use

charset utf8

If your sql file is in utf-8 :)

So you need to do:

cmd.exe

mysql -u root

mysql> charset utf8

mysql> use mydbname

mysql> source C:\myfolder\myfile.sql

Good luck ))

answered Jan 24 '15 at 14:34

Олег Всильдеревьев

875
10
11

score 2 · Answer 12 · answered Nov 18 '13 at 10:26

I have installed my wamp server in D: drive so u have to go to the following path from ur command line->(and if u have installed ur wamp in c: drive then just replace the d: wtih c: here)

D:\>cd wamp
D:\wamp>cd bin
D:\wamp\bin>cd mysql
D:\wamp\bin\mysql>cd mysql5.5.8 (whatever ur verserion will be displayed here use keyboard Tab button)
D:\wamp\bin\mysql\mysql5.5.8>cd bin
D:\wamp\bin\mysql\mysql5.5.8\bin>mysql -u root -p password db_name < "d:\backupfile.sql"

here root is user of my phpmyadmin password is the password for phpmyadmin so if u haven't set any password for root just nothing type at that place, db_name is the database (for which database u r taking the backup) ,backupfile.sql is the file from which u want ur backup of ur database and u can also change the backup file location(d:\backupfile.sql) from to any other place on your computer

score 2 · Answer 13 · answered Mar 01 '11 at 09:58

2

from the command line (cmd.exe, not from within mysql shell) try something like:

type c:/nite.sql | mysql -uuser -ppassword dbname

answered Mar 01 '11 at 09:58

Omry Yadan

25,948
16
54
77

I haven't created any password for database so it's blank in password field. And I have used the above syntax like this c:/nitm.sql | mysql -u root -p nitm but this also didn't work. Can you help me with this some more? – kushalbhaktajoshi Mar 01 '11 at 10:14
Omitting -p didn't work as well. This threw error like 'c:' is not recognized as an internal or external command, operable program or batch file. – kushalbhaktajoshi Mar 01 '11 at 10:29

score 2 · Answer 14 · answered Mar 01 '11 at 11:06

2

Does your dump contain features that are not supported in your version of MySQL? You can also try to remove the starting (and ending) MySQL commented SET-statements.

I don't know if your dump comes from a Linux version of MySQL (line endings)?

answered Mar 01 '11 at 11:06

Maickel

54
2

I have already deleted the mysql commented SET-statements. But still the error occurs. – kushalbhaktajoshi Mar 01 '11 at 11:15
You can try to login to the MySQL command shell and the use then statement: "source c:\nite.sql". Don't forget to select the correct database. – Maickel Mar 01 '11 at 12:01
I really appreciate your effort. Thanks. – kushalbhaktajoshi Mar 01 '11 at 12:18

score 1 · Answer 15 · answered Apr 19 '16 at 12:34

Export Particular DataBases

 djimi:> 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 DB to Particular Mysql Instance (You have to be where your dump file is)

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

score 1 · Answer 16 · answered Mar 01 '11 at 10:13

1

mysql>c:/nitm.sql;

That would write the output of the mysql command to 'nitm.sql;' (What's the ';' supposed to do?) Assuming you've got a copy of the original file (before you overwrote it) then:

mysql < c:/nitm.sql

answered Mar 01 '11 at 10:13

symcbean

45,607
5
49
83

This didn't work either. It threw error like ERROR 1064 <42000>: 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 'mysql < c:/nitm.sql' at line 1 – kushalbhaktajoshi Mar 01 '11 at 10:24

score 0 · Answer 17 · answered Feb 21 '15 at 07:10

0

In Windows OS the following commands works for me.

mysql>Use <DatabaseName>
mysql>SOURCE C:/data/ScriptFile.sql;

No single quotes or double quotes around file name. Path would contain '/' instead of '\'.

answered Feb 21 '15 at 07:10

Pabitra Dash

1,249
1
19
24

score 0 · Answer 18 · answered Feb 19 '19 at 02:58

For those of you struggling with getting this done trying every possible answer you can find on SO. Here's what worked for me on a VPS running Windows 2012 R2 :

Place your sql file wherever the bin is for me it is located at C:\Program Files\MySQL\MySQL Server 8.0\bin
Open windows command prompt (cmd)
Run C:\Program Files\MySQL\MySQL Server 8.0\bin > mysql -u [username] -p
Enter your password
Run command use [database_name];
Import your file with command source C://Program Files//MySQL//MySQL Server 8.0//bin//mydatabasename.sql

It did it for me as everything else had failed. It might help you too.

Import SQL file into mysql

18 Answers18

Linked