MySQL Database Backup using mysqldump command.
- By Viral Patel on January 20, 2009
- How-To, MySQL
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 :D , Just what I was looking for!
This script is even in the ubuntu repositories! Just use:
apt-get install automysqlbackup