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.
This question is related to
mysql
phpmyadmin
command-line-arguments
wampserver
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
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
----------------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 -------------------
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
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'
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!
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
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
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
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
mysql -u root -ppassword
In MySQL, change the database you want to import in: mysql>use databasename;
Import the SQL file using source command: mysql>source path\to\the\file\filename.sql;
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.
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)
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
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
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
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
Source: Stackoverflow.com