How to restore MySQL database from sql dump file?

advertisement

mysql, mysql database, sql database, sql, database server, server, sql database server, mysql database server
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:-

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.

Technorati Tags: , , , , , , , , , , , , ,

Related posts:

How to backup MySQL Database in command line with compression
How to install apache, php, mysql with macport in Mac OS X
How to monitor sql query in mysql?
How to import mysql innodb with foreign key constraint error?
MySQL replication stop at Last Errno 1062
MySQL: Full text search for multiple words
Maximum length for MySQL TEXT field types
phpmyadmin disable 1440 timeout with auto login






44 Responses to “How to restore MySQL database from sql dump file?”

  1. MisterRX says:

    Thanks!

  2. Bikash says:

    Thanks its worked fine.One more query regarding importing of multiple database at a time.Is it possible to do so through command prompt

  3. ankur banik says:

    bonder5678 u r a real guru
    thanks

  4. chua says:

    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.

  5. kiran says:

    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

  6. Chescales says:

    Thank you very much. Very useful =)

    It worked perfectly on Mac OS X Snow Leopard.

  7. chua says:

    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.

  8. Chris says:

    @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

  9. bonder5678 says:

    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”

  10. Chris says:

    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

  11. Chris says:

    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

  12. Chris says:

    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,

  13. chua says:

    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.

  14. Chris says:

    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.

  15. chua says:

    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.

  16. Chris says:

    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.

  17. chua says:

    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.

  18. Chris says:

    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

  19. chua says:

    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/

  20. Angela says:

    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

  21. Kyle says:

    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)

  22. chua says:

    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.

  23. Kyle says:

    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!

  24. GrandaumG2 says:

    THanks man! this help-me a lot!

  25. Basheer says:

    Thanks. This helped me restore my db!

  26. makandal says:

    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!

  27. Amyou says:

    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.

  28. chua says:

    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.

  29. Zoli says:

    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

  30. Arunabh Das says:

    Anyone know of a powershell way to do the above? – Arunabh Das

  31. The Edifier says:

    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

  32. mark says:

    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!

  33. Dawn says:

    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.

  34. chua says:

    Sameer, if you have more than 200MB db,
    import it using mysql command
    mysql -u #username# -p #database# abc.sql; before.

  35. Sameer says:

    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;

  36. Moeti says:

    Worked for me. Thanks.

  37. sogua says:

    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

  38. Dennis Quek says:

    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 !

  39. sogua says:

    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>

  40. Brian says:

    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.

  41. Amit Verma says:

    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!

  42. sigit says:

    but how to restore if the file dump is more than one?

  43. Chris Adams says:

    Thanks a lot. This was the fast solution for me.

  44. Jaui says:

    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!

Leave a Reply