MySQL Database Backup using mysqldump command.

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



43 Comments

  • S.Gopala krishnan 9 February, 2009, 13:13

    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

  • Wouter 2 July, 2009, 14:52

    @S.Gopala krishnan
    try this:
    mysqldump -u root -psecret db_test > db_test.sql

  • John 18 November, 2009, 12:41

    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.

  • Joe 26 January, 2010, 21:58

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

  • Joe 26 January, 2010, 22:00

    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.

  • NIraj Patel 11 February, 2010, 18:15

    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

    • Viral Patel 11 February, 2010, 18:22

      @Niraj: Thanks for sharing that snippet. Definitely useful to many of us.
      Good work. Much appreciated :)

  • Rara 11 June, 2010, 11:16

    Thank you for Backup all databases in MySQL command

  • Olof Larsson 5 August, 2010, 2:06

    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

  • gajendran 14 October, 2010, 11:08

    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

  • Ilan Hazan 7 April, 2011, 19:42

    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

  • Jennifer 29 April, 2011, 4:23

    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?

  • khan 28 August, 2011, 14:32

    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

  • raj 3 October, 2011, 16:28

    mysqldump is a comment.. don run it in mysql…..

    mysqldump -u root -p password db_test > db_test.sql

  • Alejandro Arauz 17 October, 2011, 22:18

    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.

  • Atul Goyal 12 January, 2012, 18:08

    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 ?

  • pranav 29 February, 2012, 11:44

    thanks a lot this helped me lot

  • Neo Cambell 13 March, 2012, 6:27

    Very nice explanation.

  • Kuldeep Singh 24 March, 2012, 19:15

    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!

  • vivek 5 June, 2012, 19:23

    mysqldump –opt –user=root –password db > db.sql try this it worked for me…

  • GIlbert 19 July, 2012, 18:16

    Hey friends where ll the backups be stored?….

  • Viral 9 August, 2012, 12:27

    Sir I am getting access denied problem while backing up database please give me alternate solution.

  • dhaval 12 October, 2012, 12:05

    I have query regarding to use mysqldump command. i have one mysql database and while i am creating .sql file using mysqldump commnad then it is creating not full backup. i don’t know how to take full data backup in mysql.

  • Lucy 10 December, 2012, 16:52

    Great article, directly to the point. Thanks for sharing!!!

  • Gurbrinder 28 December, 2012, 3:06

    Good work!
    Well explained, appreciate your efforts.

  • gude 10 January, 2013, 10:45

    hi i m able to take the backup and even zip the data
    now after i unzip the code i m not able to restore the same file.
    can u provide me some suggestions.

  • Gurdeep Singh 24 January, 2013, 17:00

    i just want to say please check your “syntaxes”. FOR Backup multiple databases the right syntax is . mysqldump –user root –password=myrootpassword –databases db_test db_second db_third > db_test.sql

    please don’t mind and i request you to provide right sysntaxes. Please go through once again..

  • Matt 28 January, 2013, 16:20

    Thanks for the tips. I was stuck on the correct syntax. This helped a lot.

  • Ahmed Khan 3 April, 2013, 13:06

    I think in second example you need to add “–databases” before the database names. so new command should look like:
    mysqldump –-user root –-password=myrootpassword –databases db_test db_second db_third > db_test.sql

  • luoly 16 April, 2013, 8:01

    I think you missed a space before the password, and the commands “–database” before [database name] can’t be omitted:

     
    mysqldump –uroot –pmyrootpassword db_test > db_test.sql
    

    should be:

     
    mysqldump –uroot –p myrootpassword -database db_test > db_test.sql
    

    mysqldump is too trouble to learn, you can try the software – Automatic Backup Scheduler for MySQL ( http://www.databasethink.com ) to back up and resotre your databases.

  • Oussama 20 June, 2013, 22:18

    I really liked your post
    i’m making a bash script to automatically backup db and send’em via ftp to a storage device :)

  • Abuzar 5 July, 2013, 16:30

    Nice job done…..!

  • anm 18 July, 2013, 20:03

    a really quick noob question. The command ‘mysqldump’ makes the backup and deletes the original database or does it leave it untouched? Thank you in advance.

  • Veerareddy Boddapati 7 August, 2013, 16:51

    export the database into our file using command prompt

    C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqldump -u root -p password bookch
    eque >bookcheque.sql;

  • NithinS 8 August, 2013, 14:28

    An example on how to use these commands from JSP code can be found here
    http://www.jvmhost.com/articles/mysql-postgresql-dump-restore-java-jsp-code

  • Vennam Anand 13 August, 2013, 19:11

    Hi Viral
    I ran both the below queries
    mysqldump –uroot –ppassword drf3 > db_test.sql
    mysqldump -u root -p password drf3 > db_test.sql
    My database name : drf3
    User : root
    Password : password
    Both the queries are not working..
    Kindly help me with this

    • Programmer 14 August, 2013, 16:43

      u’ll have to run it in command prompt not in MySql Cli…
      that simple

  • Sandeep 6 September, 2013, 10:55

    Hi,
    I want to take object-wise db backup. Like all tables, all views, all functions.
    I tried this with reference to one of the comments posted above:

    mysqldump -u [username] -p [password] [database_name] –all-tables

    but this didn’t work and mysql ignored “–all-tables”.
    Appreciate your help !!!
    Thanks

  • Steven Middlenight 12 September, 2013, 17:56

    Often I meet MySQL database when working on WordPress projects. As for me I use dbForge Studio for MySQL for all tasks of MySQL management and administration. Speaking about backup I want to say that its built in MySQL backup tool ( http://www.devart.com/dbforge/mysql/studio/mysql-backup.html ) is very fast and easy to use! Few steps and you’ll get a dump file of any size!

  • Ugesh 28 September, 2013, 21:10

    Hi all
    i try mysqldump –uroot –pmysql unidb > unidbusa.sql
    getting error ( mysqldump command not found in centos os

  • vz 3 October, 2013, 6:15

    Ran the string, no errors but nothing else either. Just goes to the next line from original prompt MySql> to simply –>. What the heck does that mean? Dont see dump file anywhere on the system either. Just starting to use MySql so maybe it is meaningful but… Anybody some ideas?

  • Magesh M 29 November, 2013, 15:17

    @ GIlbert,

    You can mention the path whihc you want to create the backup, if you not mentioned the backup location by default it was stored at “/root” directory.

    See the below comamnd for path.

    mysqldump -u root -p [password] [database-name] | gzip -9 > database-name.sql.gz

  • Neha Yadav 9 April, 2014, 18:48

    If i am trying mysqldump command for backup using command :

    mysql> mysqldump –user root –password=admin replication_mysql > replication_my
    sql.sql;

    then it show an error like :

    ERROR 1064 (42000): 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 ‘mysql
    dump –user root –password=admin replication_mysql > replication_mysql.sql’ at
    line 1

    can any one suggest me how to do this ………

Leave a Reply

Your email address will not be published. Required fields are marked *

Note

To post source code in comment, use [code language] [/code] tag, for example:

  • [code java] Java source code here [/code]
  • [code html] HTML here [/code]