There are 2 ways to restore your MySQL database from sql dump file.
1st way to restore mysql database from sql dump file is using mysql web control panel – phpMyAdmin
– Log into phpMyAdmin.
– Select your preference database on the left database navigation drop down list.
– Click on Import tab on the top.
– Select your sql dumb file at File to import
– Then select your mysql database charset (ex: Latin1, utf-8)
– and click GO and it’s done!
Do not use phpMyAdmin to import or restore your MySQL database if your MySQL database file is large. This is because, phpMyAdmin has limit on total upload size which depend on php setting. Besides, there is also maximum execution time which may cause browser to time out.
The solution to restore large mysql database from sql dump file is using unix/linux shell command.
To restore mysql database from a dump file, just type the command below:-
mysql -u #username# -p #database# < #dump_file#
Of course you need to replace #username# to your database username and #database# to your target database. and rename #dump_file# to your dump file file name (Ex: dump.sql) Once you enter the command, the linux/unix shell will prompt you for your database user password, just key in your database password and you are done.
[tags]restore mysql, restore mysql database, restore mysql db, mysql, mysql database, mysql restore, restore mysql databases, database, restore database, mysql command, phpMyAdmin, mysql client, restore mysql, restore db[/tags]
Thanks!
Thanks its worked fine.One more query regarding importing of multiple database at a time.Is it possible to do so through command prompt
bonder5678 u r a real guru
thanks
Hi kiran,
the error means there is some problem while you export the data.
if possible try to run a mysqldump again and run the restore.
if this continue, i think u need to check what’s the error in lin 2685.
most of the time is because of data problem.
Please help me to restore data base dump file. I have used mysql -u username -p dbname > backup.sql to restore the sql file but there is the problem shown unknown command : ‘\’ and error line 2685 what its mean? I will be very happy if I got the solution. Thank you
Thank you very much. Very useful =)
It worked perfectly on Mac OS X Snow Leopard.
Chris, just open a command prompt from Start -> Run -> type “cmd.exe” and enter
then u’ll see a command prompt
now enter the command below:-
C:\xampp\mysql\bin\mysql.exe -u root -p #yourdbname# < c:\xamp\tmp\dump.sql
it should be able to restore your db.
@bonder5678 – thanks for your guidance.
I open the mysql.exe just by clicking on it inside xampp/mysql/bin/mysql.exe and it opens the mysql shell command window – and I can execute the command in there :
first try:
mysql> socialsub (forgot to type the word “use” in front)
mysql> C:\\xampp\\tmp\\dump.sql
and it runs the process but showing database no found or specified.
2nd try:
mysql> use socialsub
it showed “Access denied” so I use:
mysql> -h localhost -u root -p password (notice it just go to the next line showing –> prompt)
–> source C:\\xampp\\tmp\\dump.sql
result: Error
Unknown command : ‘\\’
error
Unknown command: ‘\\’
error
Unknown command: ‘\\’
I am puzzled why the 2nd try showing ‘\\’ as unknown command
I also tried using single ‘\” and it showed:
error
unknown command : ‘\x’
outfile disabled
outfile disabled
Please help me by giving me the full command line strings for both the USE and Source that will work and run the command to restore the dump file and import it into the database.
Also guide me on what else I have to do.
Thank you in advance
Chris
There is a third option…If you’re already logged into the MySQL command shell, you can use the source command:
mysql> use #database#
mysql> source #file name#;
Where you replace #database# and #file name# with the database name and file name, respectively. This works in both Windows and *nix, but in Windows you have to escape the directory delimiters, so “C:\temp\sqldump.sql” would be “C:\\temp\\sqldump.sql”
Hi Chua,
One last thing, where can I obtain a copy of all the shell commands relating to managing and maintaining databases and dump files.
for example: what’s the exact command to search….say a domain name and then
what’s the command to replace it with another domain name ……..so on…
Thanks in advance
Chris
Hi Chua,
Thanks a million for your guidance and I have found mysql.exe inside xampp/mysql/bin/
Tried the command and it works fine.
Now I can have my hands-on experiements using shell command and this could help solved my “huge” dump files which I intend to break it down to multiple smaller files for easy importing into databases.
I also hope can use the search and replace to change domain name, database name and admin details as well.
Once again, thank you and best wishes.
Chris
Hi Chua,
I have decided to install xampp in my pc running windows7 Ultimate SP1.
You mentioned about using shell command – so having installed xampp on pc, where is the shell command that I can input commands to work on the database such as search and replace, etc.??
I can’t find this shell command inside xampp or mysql or phpmyadmin inside xampp. So I presumed it has to be the shell in windows7 – however I noted there are three such :
1. the usual command prompt
2. There’s a folder in windows Accessories called “windows powershell” in which there are the
“WindowsPowershell” and “Windows Powershell ISE”
Which is the one to use?
If I am still wrong with the above, then please guide me to where is the shell command on my pc that I can use to input command to manage the databases and dump files such as search and replace and import and export of dump files, etc.
Thank you in advance and best regards,
Hi Chris,
Sorry chris, this command in the post is running on Linux.
However, the command to use in Win7 is the same.
Just start your command prompt thru Start -> Run -> cmd.exe
you can restore your db using command below:-
C:\> mysql -u root -p yourdbname < c:\dump\file.sql
if you get command not found error,
then you need to find mysql.exe from your c:/xampp directory and execute mysql from there
Hope this helps.
Hi Chua,
I have decided to install xampp in my desktop running Windows 7 ultimate in c:/xampp
Now, being a noob in this, you have mentioned that to handle larger dump file, using shell command is the better way. In my case, which shell should I use for managing dump files and database:
1. The usual command prompt of windows 7?
2. The Windows 7’s Powershell ISE ?
3. The Windows 7’s Powershell?
Also, where can I obtain a copy of all the shell commands to perform each task? These would be very handy for me when using shell command.
Thank you in advance and best wishes.
hi Chris,
if you do not have access to ssh, then you might need to export use phpMyAdmin.
Since you already have the backup file (i assume the backup is the full db of your site),
try to import it to your own local development machine using ssh.
once imported to your local development machine,
now you select your table -> the restore table -> export (remember to export it into zip or tar.gz)
this way, your dump file will be smaller as it only consist of 1 table.
Now you have the file and you can import it thru phpMyAdmin -> select DB -> Import.
Good luck to you.
Thanks Chua for your guidance and information. Unfortunately, most webhost don’t allow ssh access.
I understand another way is to manually “import” (copy and paste) them with sql query inside SQL tab of phpmyadmin.
I wish to import a particular table into an existing database. I have the backup copy of this said table on my pc. So I will need to copy the whole content of this table and then paste it into the sql query box and click “GO” …….and it will automatically creating/adding the table and the contents of the table, in the existing database. Is this correct?
Can you please guide me with your step-by-step procedure if above is incorrect or direct me to a page where such specific information is available.
Your kind assistance and guidance, would be much appreciated.
thank you in advance and best wishes to you and your loved ones.
Chris.
1. bigdump is still a web application, i doesnt sound strange for me if it hang or doesnt work.
2. congrats
3. u need to have ssh access and enter this command in the linux terminal. of course you must have the privilege to run this command too.
from my experience, the mysql command take less than a minutes to import 400mb dump file, depends on your machine resources.
Hi,
I have my share of hands on experience in the importing of mysql dump file to the database for my wordpress site.
It was a big dump file over 400mb –
1. I tried using bigdump – only half way to encounter some kind of errors and stopped. Just don’t know how to proceed. So gave up. Subsequently, had to seek assistance from webhost tech to help import the file.
2. Then there was another big dump file which I managed to backup from the site and I tried using the cpanel’s “restore” feature to restore but it just kept hanging there.
Funny thing is that while the import inside the phpmyadmin limit is 50mb, the compressed gz file is just below 50mb. So I tried to import ….and to my surprise, it worked.
3. Here’s my question for your guidance – when you say ::::
To restore mysql database from a dump file, just type the command below:-
mysql -u #username# -p #database# < #dump_file#
Where is the location exactly to type the command ? inside phpmyadmin or mysqladmin?
As a noob in this, your specific answers would be most grateful.
Thank you in advance
Chris
angela, normally this have to do with your configuration file for db connection.
Try to check on your configuration.php file and make sure your database connection, username and password is correct.
i think this will help u:-
http://help.joomla.org/content/view/1944/302/
I’m working with a joomla template. They instructed me to do this (import dump.sql) after installing the template to get all the modules working like the demo. Now I have a blank page with—Database Error: Unable to connect to the database:Could not connect to MySQL
Would love if someone could give me a little help?
Thanks,
Angela
Hey Chua,
Is it safe to use phpadmin to view the database while the import is taking place. It takes several hours for this to finish. I was able to get it to work but am currently having trouble as I am in the process of site migration. (The first time was a test to make sure I could do it, which worked fine of course! Now it doesnt work haha)
Kyle, check your data in your database.
normally will rename the current db to a new db name
then i’ll do the restore in a new database n check the data after restore.
Hey guys,
I performed the command it just takes me back to the prompt. How can I tell if this is working. My database is about 2.8GB.
Thanks!
THanks man! this help-me a lot!
Thanks. This helped me restore my db!
very very usefull.
i am using mysql on mc os snow leopard. going to try restore a database.
i am not very comfortable with unix. So i’m going to try!
In the save.sql file you will find a line like “Host: localhost Database: old”. Change the database name to “qwerty” instead of “old”. It will work.
it cant change the name as the db name already specified in the save.sql file. unless u edit the db name from the save.sql file then it will restore the mysql database to a new database name.
Hy,
is there a way to restore a saved database on a diffrent PC using a new name for it? (we use InnoDB)
example:
original database name is: old
mysqldump -u -p -Rl old > c:\save.sql
intended new database name: qwerty
mysql -u -p qwerty < c:\save.sql
it will not do what i want, it still restores the db with its original name
Anyone know of a powershell way to do the above? – Arunabh Das
Executing the script from within a DOS command window and from within the MYSQL shell …. that is the difference …
The examples given above all are relating to executing the command from within a DOS shell …
C:\mysql -u root -p humandb
then you can execute the program file by using just the file name … mysql in which case … the command along with the parameters would look like
C:\wamp\bin\mysql\mysql5.1.30\bin>mysql -u root -p humandb dbname < D:\sth\human.sql
using vista ultimate x64, i was struggling with importing sql file into my local database. what I eventually figured out is, that you need to be in root directory (c:\) and you can’t be anywhere further (c:\ mysql\bin) ! that at least worked for me.
co in my case command looked like that:
C:\mysql -u root -p humandb < D:\sth\human.sql
hope that helps!
Worked for me too! Thanks!
To just get one table, not the whole database you just need to add the table after the database name, as described below:
mysqldump original_databasename tablename > dumped_table.sql
mysql original_or_new_databasename < dumped_table.sql
This will create a table and insert any dumped contents from that table into the database.
Sameer, if you have more than 200MB db,
import it using mysql command
mysql -u #username# -p #database# abc.sql; before.
How to modify php.ini to allow to impoert databases of more than 200MB.
any suggestions..
what are the differences between
mysqldump -u name -ppass dbname > abc.sql;
and
mysql -u name -ppass dbname > abc.sql;
Worked for me. Thanks.
the large file upload is limit by php and not phpmyadmin itself.
if you want to allow large file upload, modify your php.ini file to allow bigger size of file upload
Just wondering why doesnt phpmyadmin allow a large file upload. Since it is on local hdd anyway.
Gonna try sqldump i hope its not going to corrupt my data base !
hi brain, here’s the example.
c:\mysql -u #username# -p #database# < #dump_file# this will solve your problem. you do not need to have mysql>
still confused…
I’m in the MySQL command shell under Windows XP Pro. The command prompt is mysql>
I’m already running as root and using my target database, so how much of that command do i need to use?
< dump_file.sql
-p databasename < dump_file.sql
-u root -p database_name -u root -p database_name and all I can do is give it \c which gets me back to the mysql> prompt but doesn’t seem to take any action.
I’m brand new to MySQL, and used to the GUI tools used with MSSQL*Server, so all this command line stuff is killing me! I tried a GUI version of MySQL Administrator but the restore option won’t take on a backup created with sqldump.
Please help.
Hi Sigit
To Store the dump which has more than one file, do as follows –
mysql -u username -p database_name < dump_file1
mysql -u username -p database_name < dump_file2
mysql -u username -p database_name < dump_file3
… and so on.
Thus finally you’ll get a complete database install and running with you!
but how to restore if the file dump is more than one?
Thanks a lot. This was the fast solution for me.
Hi, this is very much the same command in found in the MySQL instructions (i’m running the MySQL server 5.0 on Windows XP SP2) but all i get are error messages like “you have an error in your sql syntax…” for all variants of this loading command.
(i’m trying to load the wikipedia database (enwiki-20070802-page.sql) which is over 800 MB)
Any tips on what i should try next?
Thanks!