How to restore MySQL database from sql dump file?
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: database, mysql, mysql client, mysql command, MySQL Database, mysql restore, phpMyAdmin, restore database, restore db, restore mysql, restore mysql database, restore mysql databases, restore mysql db
Posted at September 19th, 2006 by chua
If you think this article helps you to solve your problem and clear your headache, feel free to buy me a drink :)
September 11th, 2007 at 3:53 pm
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!
January 11th, 2008 at 2:45 am
Thanks a lot. This was the fast solution for me.
May 9th, 2008 at 11:58 am
but how to restore if the file dump is more than one?
October 24th, 2008 at 4:22 pm
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!
November 22nd, 2008 at 6:09 am
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.
November 24th, 2008 at 12:08 am
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>
January 6th, 2009 at 5:19 pm
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 !
January 8th, 2009 at 8:03 am
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
March 31st, 2009 at 9:29 pm
Worked for me. Thanks.
April 1st, 2009 at 3:00 pm
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;
April 2nd, 2009 at 4:23 pm
Sameer, if you have more than 200MB db,
import it using mysql command
mysql -u #username# -p #database# abc.sql; before.
May 14th, 2009 at 11:16 am
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.
June 5th, 2009 at 2:01 am
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!
August 21st, 2009 at 9:24 pm
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
October 18th, 2009 at 2:38 pm
Anyone know of a powershell way to do the above? – Arunabh Das
October 29th, 2009 at 5:25 pm
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
November 2nd, 2009 at 1:08 pm
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.
December 3rd, 2009 at 5:30 pm
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.
February 6th, 2010 at 11:38 am
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!