Reputation: 4688
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.
Upvotes: 205
Views: 522005
Reputation: 341
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)
C:\Program Files\MySQL\MySQL Server 8.0\bin > mysql -u [username] -p
use [database_name]
;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.
Upvotes: 0
Reputation: 2580
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
.
Upvotes: 7
Reputation: 2662
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
Upvotes: 1
Reputation: 23211
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
Upvotes: 428
Reputation: 5228
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.
Upvotes: 7
Reputation: 921
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.
Upvotes: 18
Reputation: 589
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
Upvotes: 3
Reputation: 271
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;
Upvotes: 27
Reputation: 1513
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 '\'.
Upvotes: 0
Reputation: 943
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 ))
Upvotes: 4
Reputation: 1279
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
Upvotes: 2
Reputation: 16307
If you are using xampp
C:\xampp\mysql\bin\mysql -uroot -p nitm < nitm.sql
Upvotes: 9
Reputation: 561
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.
Upvotes: 56
Reputation: 4688
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.
Upvotes: 92
Reputation: 54
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)?
Upvotes: 2
Reputation: 48387
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
Upvotes: 1
Reputation: 2564
You are almost there use
mysql> \. c:/nitm.sql;
You may also access help by
mysql> \?
Upvotes: 7
Reputation: 33686
from the command line (cmd.exe, not from within mysql shell) try something like:
type c:/nite.sql | mysql -uuser -ppassword dbname
Upvotes: 2