MySQL Database Backup using mysqldump command.
- By Viral Patel on January 20, 2009
Since its release in 1995, MySQL has became one of the most commonly used database in Internet world. A lot of small and medium businesses uses MySQL as their backend db. Its popularity for use with web applications is closely tied to the popularity of PHP, which is often combined with MySQL. Wikipedia runs on MediaWiki software, which is written in PHP and uses a MySQL database. Several high-traffic web sites use MySQL for its data storage and logging of user data, including Flickr, Facebook, Wikipedia, Google, Nokia and YouTube.
MySQL provide a great command line utility to take backup of your MySQL database and restore it. mysqldump command line utility is available with MySQL installation (bin directory) that can be used to achieve this.
1. Getting backup of a MySQL database using mysqldump.
Use following command line for taking backup of your MySQL database using mysqldump utility.
mysqldump –-user [user name] –-password=[password] [database name] > [dump file] or mysqldump –u[user name] –p[password] [database name] > [dump file]
Example:
mysqldump –-user root –-password=myrootpassword db_test > db_test.sql or mysqldump –uroot –pmyrootpassword db_test > db_test.sql
2. Backup multiple databases in MySQL.
mysqldump –u[user name] –p[password] [database name 1] [database name 2] .. > [dump file]
Example:
mysqldump –-user root –-password=myrootpassword db_test db_second db_third > db_test.sql
3. Backup all databases in MySQL.
shell> mysqldump –u[user name] –p[password] –all-databases > [dump file]
4. Backup a specific table in MySQL.
shell> mysqldump --user [username] --password=[password] [database name] [table name] \ > /tmp/sugarcrm_accounts_contacts.sql
Example:
shell> mysqldump --user root --password=myrootpassword db_test customers \ > db_test_customers.sql
5. Restoring MySQL database.
The mysqldump utility is used only to take the MySQL dump. To restore the database from the dump file that you created in previous step, use mysql command.
shell> mysql --u [username] --password=[password] [database name] < [dump file]
Example:
shell> mysql --user root --password=myrootpassword new_db < db_test.sql
Do you know the other uses of mysqldump utility? Comment on this post.
Get our Articles via Email. Enter your email address.
Hi
I need examples to take database backup from online server.
I worked your examples query but it was not working.
i am using MySql. In MySql i use test database i need how to take backup in local folders
and how to run the restore database.
There was a problem like this error occur : “#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘mysqldump –-user root password = root db_test > db_test.sql’ at line 1 ”
Thank you
S.Gopalakrishnan
Email : gsskkrish@yahoo.co.in
@S.Gopala krishnan
try this:
mysqldump -u root -psecret db_test > db_test.sql
Please tell me how to restore MySql database file using java class. I have written a code but it cant restore here is the code:
import java.io.File; import javax.swing.JFileChooser; import javax.swing.JPanel; public class restoreDB extends JPanel { public restoreDB() { myRestore(); } public void myRestore() { File file=null; JFileChooser filechooser=new JFileChooser(); int selection=filechooser.showOpenDialog(new MainWindow()); if(selection==JFileChooser.APPROVE_OPTION) { file=filechooser.getSelectedFile(); String r=file.getAbsolutePath(); String s=r.replace('\\', '/'); // String s=r.replaceAll(" ", "\" \""); // String[] executeCmd = new String[]{"C:/Program Files/MySQL/MySQL Server 6.0/bin/mysql", "userlogin", "--user=root" , "--password=root", "-e", s }; System.out.println(s); try { // Process runtimeProcess =Runtime.getRuntime().exec(executeCmd); Process p=Runtime.getRuntime().exec("C:/Program Files/MySQL/MySQL Server 6.0/"+ "bin/mysql -u root -proot < "+s); }catch(Exception e) { System.out.println(e); } } } }please give me an example.
to back up all databases use –all-databases, not -all-databases. At least in my browser that’s how the code appears from this page (as an en-dash maybe?).
I think this blog forces two hyphens to become an en-dash.
to back up all databases use – - all-databases (two hyphens), not -all-databases (one hyphen). At least in my browser that’s how the code appears from this page.
Hi Viral,
Thnks for your article.
I want to share something.
We can also take Dump only rows selected by the given WHERE condition.
shell> mysqldump –user [username] –password=[password] [database name] [table name] where = “[condition]“\ > /tmp/sugarcrm_accounts_contacts.sql
Example:
shell> mysqldump –user root –password=myrootpassword db_test customers where = ” id=’15′ ” \ > db_test_customers.sql
@Niraj: Thanks for sharing that snippet. Definitely useful to many of us.
Good work. Much appreciated
Thank you for Backup all databases in MySQL command
You should take a look at this instead:
https://sourceforge.net/projects/automysqlbackup/
I just installed it and it works as a charm
, Just what I was looking for!
This script is even in the ubuntu repositories! Just use:
apt-get install automysqlbackup
hi..
i took mysql database backup in windows server and i restored that mysql db in linux pc.. its sucessfully restored ..i connected that mysql db in client xp mechine ..i compiled that db procedure but its show tables doesn’t exit..will u pls tel how to solve this problem
Restoring a dump table into the MySQL master server can lead to serious replication delay.
To overcome the replication delay, caused by restoring the dump table on the master, there is a need to widespread the massive inserts. This can be done by the MySQL SLEEP command.
See As restoring a dump table into the MySQL master – you better get some sleep
What does the -h command do? I have been trying to figure it out and I don’t know what it does in the command line. See, like on this tutorial they do it: http://www.fortasse.com/tutorials/mysqldump-mysql-database-backup/
But on others they don’t… Anyone know?
i tried mysqldump to backup it worked ie file .sql is created but when i try to restore it it also shows sucess but tables are not created
mysqldump is a comment.. don run it in mysql…..
mysqldump -u root -p password db_test > db_test.sql
The only problem with the mysqldump command is that you need privileges to execute this command on the server. I had this problem before in a shared enviroment and I found this tool: MySQLBackupFTP (http://mysqlbackupftp.com). It has a free version that allows you to connect to MySQL through phpMyAdmin so you don’t need admin access to the server. I hope this help others with the same problem.
Dear I am using IBatis with Spring 3.0 for database for a web applicantion . Now can anyone tell me how can i take the back up of database ?
thanks a lot this helped me lot
Very nice explanation.
Your article are really awesome. Actually I was in search for some good articles on MySQL Database Backup file and finally I got one.
The most important thing of this article is simplicity and understandable. You’ve elaborate it very well. Check this links too it also having good explanation on MySQL database backup…..
http://mindstick.com/Blog/239/How%20to%20Back%20Up%20and%20Restore%20a%20MySQL%20Database
http://stackoverflow.com/questions/823841/mysql-backup-and-restore-from-command-line
Thanks Everyone for your valuable post!