Import SQL file by command line in Windows 7

Question

I want to import an SQL file (size > 500MB) into a database. I have wamp on my PC. Phpmyadmin does not work well with this size. I changed all parameters in php.ini (max_upload_size etc.). But it does not work for me. So I want to import it by command line.

I used these commands for import the file:

mysql -u root -p sysdat < D:\Nisarg\ISPC\Database\sysdat.sql 
mysql -u root -p -D sysdat < D:\Nisarg\ISPC\Database\sysdat.sql
mysql -u root sysdat < D:\Nisarg\ISPC\Database\sysdat.sql -p

These all are not working.

score 104 · Accepted Answer · answered Feb 13 '13 at 05:27

104

Try like this:

I think you need to use the full path at the command line, something like this, perhaps:

C:\xampp\mysql\bin\mysql -u {username} -p {databasename} < file_name.sql

Refer this link also:

http://www.ryantetek.com/2011/09/importing-large-sql-files-through-command-line-when-using-phpmyadminxampp/

answered Feb 13 '13 at 05:27

user2001117

3,727
1
18
18

3

Tried this and it looks like it's working but I'm not getting any feedback in the command prompt. Are there any flags you can use to recieve feedback about the progress of the operation? – Ogier Schelvis Jul 03 '15 at 10:33
1

link page not found – Andhi Irawan Jan 05 '18 at 09:29

score 8 · Answer 2 · answered Sep 24 '13 at 10:25

8

To import database from dump file use:

mysql -u UserName -p Password DatabaseName < FileName.sql

In wamp

C:\wamp\bin\mysql\mysql5.0.51b\bin>mysql mysql -uroot -p DatabaseName < FileName.sql

answered Sep 24 '13 at 10:25

Nanhe Kumar

15,498
5
79
71

score 7 · Answer 3 · answered Jan 19 '14 at 18:52

If you have wamp installed then go to command prompt , go to the path where mysql.exe exists , like for me it was : C:\wamp\bin\mysql\mysql5.0.51b\bin , then paste the sql file in the same location and then run this command in cmd :

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

score 7 · Answer 4 · answered Jul 24 '15 at 10:39

7

If you are using Windows PowerShell you may get the error:

The '<' operator is reserved for future use.

In that case just type the command:

cmd

To switch to the cmd shell and then retype the command and it will work.

c:\xampp\mysql\bin\mysql -u root -p my_database < my_database_dump.sql

To get back to PowerShell type:

exit

answered Jul 24 '15 at 10:39

Kevin Sadler

2,306
25
33

3

Sir you saved a life in 2022! – talha2k Jun 30 '22 at 14:07
1

Your are a life saver. Thanks – kamasuPaul Jun 05 '23 at 11:40

score 3 · Answer 5 · answered Dec 04 '14 at 08:43

----------------WARM server.

step 1: go to cmd go to directory C:\wamp\bin\mysql\mysql5.6.17 hold Shift + right click (choose "open command window here")

step 2: C:\wamp\bin\mysql\mysql5.6.17\bin>mysql -u root -p SellProduct < D:\file.sql

in this case
+ Root is username database  
+ SellProduct is name database.
+ D:\file.sql is file you want to import

---------------It's work with me -------------------

score 2 · Answer 6 · answered Jul 12 '14 at 07:19

To import database from dump file (in this case called filename.sql)

    use: mysql -u username -p password database_name < filename.sql

you are on Windows you will need to open CMD and go to directory where mysql.exe is installed. you are using WAMP server then this is usually located in: C:\wamp\bin\mysql\mysql5.5.8\bin (*note the version of mysql might be different)

So you will: cd C:\wamp\bin\mysql\mysql5.5.8\bin

and then execute one of the above commands. Final command like this

    C:\wamp\bin\mysql\mysql5.5.8\bin>mysql -u rootss -p pwdroot testdatabasename < D:\test\Projects\test_demo_db.sql

This is wrong syntax. You should not use space between -p and password. — Alexey Muravyov, Jul 17 '19 at 09:47

score 2 · Answer 7 · answered May 19 '15 at 13:48

2

TRY THIS

  C:\xampp\mysql\bin\mysql -u {username} -p {databasename} < {filepath}

if username=root ,filepath='C:/test.sql', databasename='test' ,password ='' then command will be

  C:\xampp\mysql\bin\mysql -u root  test < C:/test.sql

answered May 19 '15 at 13:48

Shal

613
6
9

score 2 · Answer 8 · edited Apr 23 '18 at 23:54

I use mysql -u root -ppassword databasename < filename.sql in batch process. For an individual file, I like to use source more because it shows the progress and any errors like

Query OK, 6717 rows affected (0.18 sec)
Records: 6717  Duplicates: 0  Warnings: 0

Log in to MySQL using mysql -u root -ppassword
In MySQL, change the database you want to import in: mysql>use databasename;
- This is very important otherwise it will import to the default database
Import the SQL file using source command: mysql>source path\to\the\file\filename.sql;

score 1 · Answer 9 · edited Apr 16 '14 at 11:51

1

Try this it will work. Do not enter password it will ask one you execute the following cmd

C:\xampp\mysql\bin\mysql -u xxxxx -p -h localhost your_database_name < c:\yourfile.sql

edited Apr 16 '14 at 11:51

Rahil Wazir

10,007
11
42
64

answered Apr 16 '14 at 11:32

Chetan

39
3

score 1 · Answer 10 · answered Jul 10 '18 at 17:02

To import SQL file what works for me

For Wamp-Server

Find mysql in wamp. In my computer it's location is "C:\wamp64\bin\mysql\mysql5.7.21\bin"

Open cmd and once you get inside bin you have to write " mysql -uroot -p database_name < filename.sql"

remember to put sql file under bin.

in nutshell you have to do this:-

C:\wamp64\bin\mysql\mysql5.7.21\bin>mysql -uroot -p database_name < filename.sql

After this, it will ask for the password, mine password was nothing(BLANK).

hope it helps someone.

ashish patel · Answer 11 · 2021-08-17T11:52:50.843

If you are using XAMPP SERVER on your local machine, follow the below steps to import the table inside your database

Create a database where you want to import those tables using cmd (either through PhpMyAdmin or using the command line)
Go to the command line by going to Start -> Run and typing in cmd. Change the directory to the MySQL bin directory. It will be like if you have used C drive to install this application C:\Windows\System32\cmd.exe OR Go to your MySQL bin directory inside XAMPP/WAMP/LAMP & there in the address bar just type cmd and press ENTER
In cmd the directory will be displayed like this C:\xampp\mysql\bin> Then type the below command:

mysql -u {user_name} -p {user_password} {database_name} < file_path.sql

OR If you do not have set the password then use below command:

mysql -u {user_name} {database_name} < file_path.sql

ANOTHER METHOD: Follow up to above point 2 & followed by given steps below:

Step 01:

mysql -u {user_name} -p {user_password}

example: C:\xampp\mysql\bin>mysql -u root -p password

Step 02:

use {database_name}

example: MariaDB [(none)]> use database_name

Step 03:

Provide the source from where the file will be imported

source file_path.sql

example: MariaDB [database_name]> source sql_file.sql

The output will be as attached image:

Hope it will work, Enjoy!!

score 0 · Answer 12 · answered Apr 03 '14 at 17:23

Related to importing, if you are having issues importing a file with bulk inserts and you're getting MYSQL GONE AWAY, lost connection or similar error, open your my.cnf / my.ini and temporarily set your max_allowed_packet to something large like 400M

Remember to set it back again after your import!

score 0 · Answer 13 · answered May 16 '15 at 12:17

mysql : < (for import) > (for export)

in windows, you want to take backup or import the sql file, then goto cmd prompt type the address were the mysql is installed eg:C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin> after this

C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin> mysql -u UserName -p Password DatabaseName < FileName.sql (import)

C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin> mysql -u UserName -p Password DatabaseName > FileName.sql (export)

score 0 · Answer 14 · answered May 21 '15 at 19:05

If those commands don't seems to work -- I assure you they do --, check the top of your sql dump file for the use of :

CREATE DATABASE {mydbname}

and

USE {mydbname}

The last parameter {mydbname} of the mysql command can be misleading : if CREATE DATABASE an USE are in your dump file, the import will in fact be done in this database, not in the one in the mysql command.

The mysqldump command that will prompt CREATE DATABASE and USE is :

mysqldump.exe -h localhost -u root --databases xxx > xxx.sql

Use mysqldump without --databases to leave out CREATE DATABASE and USE :

mysqldump.exe -h localhost -u root xxx > xxx.sql

Mostafa Nawara · Answer 15 · 2016-08-26T00:32:29.603

0

If you don't have password you can use the command without

-u

Like this

C:\wamp>bin\mysql\mysql5.7.11\bin\mysql.exe -u {User Name} {Database Name} < C:\File.sql

Or on the SQL console

mysql -u {User Name} -p {Database Name} < C:/File.sql

edited Aug 26 '16 at 00:32

answered Jul 25 '16 at 12:51

Mostafa Nawara

782
9
21

score 0 · Answer 16 · answered Apr 24 '17 at 10:49

0

First open Your cmd pannel And enter mysql -u root -p (And Hit Enter) After cmd ask's for mysql password (if you have mysql password so enter now and hit enter again) now type source mysqldata.sql(Hit Enter) Your database will import without any error

answered Apr 24 '17 at 10:49

Amit Patel

11

Welcome to SO! Please read this [how-to-answer](http://stackoverflow.com/help/how-to-answer). Format codes would definitely help. – thewaywewere Apr 24 '17 at 11:23

score 0 · Answer 17 · answered Feb 12 '18 at 10:32

If you are running WampServer on your local machine, import means restoring the dump file that you have (in sql format)

Here are the steps

Go to command line by going to Start -> Run and typing in cmd.
Change the directory to Mysql bin directory. It will be like

c:\wamp\bin\mysql\mysql5.7.14\bin
It would be better to keep the dump file in the above directory( we can delete, after restoration)
Hope you have created the database (either through phpMyadmin or using command line)
Then type the command mysql.exe -u root -p databasename < filename.sql

Please note the difference, it is 'mysql.exe' not 'mysql'

score 0 · Answer 18 · answered Mar 29 '22 at 08:23

0

Just remove space between -p and your password. like this:

C:\xampp\mysql\bin\mysql -u {username} -p{databasename} < file_name.sql

answered Mar 29 '22 at 08:23

Nuri Akman

792
3
18
41

score 0 · Answer 19 · edited Jul 30 '22 at 23:21

0

Follow below steps for importing database with getting feedback in the command prompt.

Step 1: go to your mysql bin path C:\xampp\mysql\bin

Step 2:

mysql -u {username} -p -v {databasename} < "file_name.sql"

Note: -v will show you insert logs in cmd

edited Jul 30 '22 at 23:21

cursorrux

1,382
4
9
20

answered Jul 24 '22 at 14:24

DIXIT SATHWARA

1

Import SQL file by command line in Windows 7

19 Answers19

Linked