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

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

1. Go to command line by going to Start -> Run and typing in cmd.

2. Change the directory to Mysql bin directory. It will be like

   c:\wamp\bin\mysql\mysql5.7.14\bin

3. It would be better to keep the dump file in the above directory( we can delete, after restoration)

4. Hope you have created the database (either through phpMyadmin or using command line)

5. Then type the command mysql.exe -u root -p databasename < filename.sql

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

Answer 2

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

Answer 3

To import SQL file what works for me

For Wamp-Server

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

Answer 4

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

Answer 5

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

Answer 6

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

1. Log in to MySQL using mysql -u root -ppassword

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

3. Import the SQL file using source command: mysql>source path\to\the\file\filename.sql;