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