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.

Answer 1

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)

Answer 2

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

Answer 3

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

Answer 4

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/

Answer 5

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 

Answer 6

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