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