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