≡ Menu

Backup and Restore MySQL Database Using mysqldump

[mysqldump - MySQL Backup & Restore]mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT into sql-statements of the source database. To restore the database,  execute the *.sql file on destination database.  For MyISAM, use mysqlhotcopy method that we explained earlier, as it is faster for MyISAM tables.

Using mysqldump, you can backup a local database and restore it on a remote database at the same time, using a single command. In this article, let us review several practical examples on how to use mysqldump to backup and restore.

For the impatient, here is the quick snippet of how backup and restore MySQL database using mysqldump:

backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

How To Backup MySQL database

1. Backup a single database:

This example takes a backup of sugarcrm database and dumps the output to sugarcrm.sql

# mysqldump -u root -ptmppassword sugarcrm > sugarcrm.sql

# mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

The sugarcrm.sql will contain drop table, create table and insert command for all the tables in the sugarcrm database. Following is a partial output of sugarcrm.sql, showing the dump information of accounts_contacts table:

--
-- Table structure for table `accounts_contacts`
--

DROP TABLE IF EXISTS `accounts_contacts`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `accounts_contacts` (
`id` varchar(36) NOT NULL,
`contact_id` varchar(36) default NULL,
`account_id` varchar(36) default NULL,
`date_modified` datetime default NULL,
`deleted` tinyint(1) NOT NULL default '0',
PRIMARY KEY  (`id`),
KEY `idx_account_contact` (`account_id`,`contact_id`),
KEY `idx_contid_del_accid` (`contact_id`,`deleted`,`account_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `accounts_contacts`
--

LOCK TABLES `accounts_contacts` WRITE;
/*!40000 ALTER TABLE `accounts_contacts` DISABLE KEYS */;
INSERT INTO `accounts_contacts` VALUES ('6ff90374-26d1-5fd8-b844-4873b2e42091',
'11ba0239-c7cf-e87e-e266-4873b218a3f9','503a06a8-0650-6fdd-22ae-4873b245ae53',
'2008-07-23 05:24:30',1),
('83126e77-eeda-f335-dc1b-4873bc805541','7c525b1c-8a11-d803-94a5-4873bc4ff7d2',
'80a6add6-81ed-0266-6db5-4873bc54bfb5','2008-07-23 05:24:30',1),
('4e800b97-c09f-7896-d3d7-48751d81d5ee','f241c222-b91a-d7a9-f355-48751d6bc0f9',
'27060688-1f44-9f10-bdc4-48751db40009','2008-07-23 05:24:30',1),
('c94917ea-3664-8430-e003-487be0817f41','c564b7f3-2923-30b5-4861-487be0f70cb3',
'c71eff65-b76b-cbb0-d31a-487be06e4e0b','2008-07-23 05:24:30',1),
('7dab11e1-64d3-ea6a-c62c-487ce17e4e41','79d6f6e5-50e5-9b2b-034b-487ce1dae5af',
'7b886f23-571b-595b-19dd-487ce1eee867','2008-07-23 05:24:30',1);
/*!40000 ALTER TABLE `accounts_contacts` ENABLE KEYS */;
UNLOCK TABLES;

2. Backup multiple databases:

If you want to backup multiple databases, first identify the databases that you want to backup using the show databases as shown below:

# mysql -u root -ptmppassword

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bugs               |
| mysql              |
| sugarcr            |
+--------------------+
4 rows in set (0.00 sec)

For example, if you want to take backup of both sugarcrm and bugs database, execute the mysqldump as shown below:

# mysqldump -u root -ptmppassword --databases bugs sugarcrm > bugs_sugarcrm.sql

Verify the bugs_sugarcrm.sql dumpfile contains both the database backup.

# grep -i "Current database:" /tmp/bugs_sugarcrm.sql
-- Current Database: `mysql`
-- Current Database: `sugarcrm`

3. Backup all the databases:

The following example takes a backup of  all the database of the MySQL instance.

# mysqldump -u root -ptmppassword --all-databases > /tmp/all-database.sql

4. Backup a specific table:

In this example, we backup only the accounts_contacts table from sugarcrm database.

# mysqldump -u root -ptmppassword sugarcrm accounts_contacts \
      > /tmp/sugarcrm_accounts_contacts.sql

4. Different mysqldump group options:

  • –opt is a group option, which is same as –add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, and –disable-keys. opt is enabled by default, disable with –skip-opt.
  • –compact is a group option, which gives less verbose output (useful for debugging). Disables structure comments and header/footer constructs. Enables options –skip-add-drop-table –no-set-names –skip-disable-keys –skip-add-locks

How To Restore MySQL database

1. Restore a database

In this example, to restore the sugarcrm database, execute mysql with < as shown below. When you are restoring the dumpfilename.sql on a remote database, make sure to create the sugarcrm database before you can perform the restore.

# mysql -u root -ptmppassword

mysql> create database sugarcrm;
Query OK, 1 row affected (0.02 sec)

# mysql -u root -ptmppassword sugarcrm < /tmp/sugarcrm.sql

# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

2. Backup a local database and restore to remote server using single command:

This is a sleek option, if you want to keep a read-only database on the remote-server, which is a copy of the master database on local-server. The example below will backup the sugarcrm database on the local-server and restore it as sugarcrm1 database on the remote-server. Please note that you should first create the sugarcrm1 database on the remote-server before executing the following command.

[local-server]# mysqldump -u root -ptmppassword sugarcrm | mysql \
                 -u root -ptmppassword --host=remote-server -C sugarcrm1
[Note: There are two -- (hyphen) in front of host]

If you liked this article, please bookmark it on del.icio.us and Stumble it.

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 68 comments… add one }

  • Ajith Edassery September 22, 2008, 1:19 am

    Ramesh, thanks for the backup tips for MySQL, I guess it’s for more pro users and sysadmin?

    I am using phpMyAdmin GUI to take backup and my blog content back up is taken via the wordpress database backup plugin. Do you see any issue if backup is taken via a GUI too like phpmyadmin? The reason why I am asking this is due to the fact that though I take a lot of backup, I havent really tried to restore them and test the blog/tools.

    Cheers,
    Ajith

  • Saeed Pazoki September 22, 2008, 6:57 am

    Thanks ramesh. It was very informative. I do backup and restore on rare occasions since I am not a DBA but every time I need to google for a quick reference. This is exactly what I wanted.

  • subhash September 24, 2008, 12:26 am

    Nice informative tip dude

  • Binny V A September 26, 2008, 3:19 pm
  • Student November 27, 2008, 7:44 pm

    my friend told me that we also can backup mySQL database in .db extension instead of .sql

    as i can read from this page… i can this command in shell

    # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

    could you all help me on using command in shell to backup in .db

    thanks in advanced

  • mk_michael December 6, 2008, 3:20 pm

    In my experience, I’ve got some troubles when dumping tables and restoring it on another machine, because of UTF-8 and ISO formats. If you safe the dumpfile to your local machine, you must be sure to have the appropriate format. If not. the dump will be converted.

    Mostly, I open the phpmyadmin on both machines and copy STRG+C the dump into clipboard and paste it into the SQL tab on the target phpmyadmin.

  • michael December 18, 2008, 12:28 pm

    Hi,
    I have a MySql backup taken from a linux web server and I’m trying to resore it into my Windows MySql Admin. It said I couldn’t because it was created by mysqldump.

    Do you know how to restore it onto my local machine?

    Thanks

  • Girish April 1, 2009, 12:29 am

    check this out…

    –Use this to take only the Table structure of the database table in Linux —

    On the terminal
    [root@localhost ~]# mysqldump -uroot -proot123 -h200.200.200.12 mydatabasename tbl_mytable_name –no-data > /tmp/tbl_mytable_name_dump.sql

    Remember , if u dont redirect it to a file, it will stores the dump at pwd by the name “oot”


    To restore from the file use this … i

    mysql > source /path/to/the/directory/dumpfile.sql

    Regards,
    Girish :-)

  • hari April 14, 2009, 11:27 pm

    sir,
    I need single mysql database backup, store in text file and delete it after 3 years using java code.please help me.

    Thanks,
    Hari.

  • Rick August 6, 2009, 8:05 am

    When I used this dump # mysqldump -u root -ptmppassword –all-databases > /tmp/all-database.sql , I noticed and tested all is dumped into 1 large file. When I should restore how is the command set to split this large 1 file dump into the right databases again? I do not quit understand this?

  • Girish August 26, 2009, 9:40 am

    Hi Rick ,
    No matter, whether u are doing backup of a single database or multiple one.. once u proceed with mysql> source /path/to/dir/myalldatabasedumfile.sql
    u will get all ur database restored…..
    In case as such , if u use the same command on the db which already been there, u will get an error displaying while trying to perform some ddl statement ( eg. create.) coz , u already have the same source in the db.

  • Girish August 26, 2009, 9:44 am

    yeah , n if u want to get dump for a single database , u can mention there the single database name also , mysqldump -uroot -pmyrootpassword mydatabasename > /tmp/rugettingwhatisay.sql

  • Krishnakumar M September 10, 2009, 11:11 pm

    Nice stuff. Easy to understand.

  • Roc Boronat January 11, 2010, 9:44 am

    Thanks! This article has been very helpful for me :)

  • Ray May 13, 2010, 3:53 pm

    Hi,
    I got an error while I was trying to restore backed-up file,
    can you please help?

    ERROR 1146 (42S02) at line 12: Table ‘mysql.time_zone_name’ doesn’t exist

    Thanks!

  • vani June 2, 2010, 4:50 am

    how to restore mysql files and please send the total mysql meterial,mysql deployment.

  • Andrew July 12, 2010, 10:49 am

    Hi I got the same error as Ray, trying to restore a mysqldump file.

    ERROR 1146 (42S02) at line 12: Table ‘mysql.time_zone_name’ doesn’t exist

    The mysql versions are exactly the same.

  • Bret July 18, 2010, 6:09 pm

    how to restore all databases from one dumped file ?
    I did DB dump from server1,
    mysqldump –user=xxxx –password=xxxx –all-databases –lock-all-tables > all_databases_s1.sql

    now how to restore this dump to server2 ?

    Any idea?
    Bret

  • Ashish July 29, 2010, 1:12 am

    to restore from .sql file follow command:
    mysql –user=xxxx –password=xxxx < all_databases_s1.sql

    it will restore all ur databases.

    njoy!!!
    Ashish

  • Girish Mahajan September 3, 2010, 12:53 am

    Bret,
    Adding to Ashish comment,
    you can also restore the dump with following :

    1. Login to mysql terminal mysql -u -p -D -h -p

    2. source /home/girish.mahajan/opnsrc/mysql/dbdumps/test.sql ;

    Done.

    Thanks,
    Girish

  • DG September 23, 2010, 9:43 am

    Ray / Bret :

    I’m seeing the same problem; I’m wondering if it’s because the user I am using doesn’t have access to the ‘mysql’ database, OR the ‘mysql’ database is corrupt.

    In my case, connecting as the user i’m using, shows the ‘mysql’ database to be empty; this shouldn’t be possible (at least to my limited mysql understanding) – hence I suspect it’s a permissions issue.

  • rinshad December 9, 2010, 9:40 am

    hai..
    how to restore a single database from a dumpfile where i had the backup of my entire databases ..???
    rinshad.

  • rinshad December 9, 2010, 9:55 am

    mysql -u root -p –one-database db_name < dump_file

    R!n5h@D !!

  • Manoj January 6, 2011, 4:44 am

    Hi,
    Nice stuff guys..
    It helps me a lot…
    But i have query that before starting the backup & restore do i need to stop the mysql service

    Awaited for your comments & suggestion guys..

    Thanks..

  • binu March 16, 2011, 4:27 am

    how can i restore my mysql tables
    i use mysql -u root -p smsapp [table1 2 3] for dump tables
    how can i restore it to a new database
    plz help me

  • Girish Mahajan March 17, 2011, 12:11 am

    Ray / Bret ==>
    Two solutions for your problem :
    1. Prior to installation of MySQL, set timezone from “setup” command in linux and proceed with mysql installation, Or, Simply , You can edit the backup dump and remove the timestamp entry to ignore such kindof error.

    Manoj ==> Its important that you dont need to stop the mysql when you are about to take the backup of the database, if you do so, you will get the error “Failed to locate MySQL socket at port XXX”, Also, what does mysqldump will do is to copy the scattered control file+data files data in to sql statement and redirecting to the .sql files thats it, so when if your service is down, you dont have the data files loaded in memory to read upon …!!!

    BINU ==> To restore content to new database, refer comment #19 and #20 of this thread.

    Thank you,
    Regards,
    Girish Mahajan

  • Naz April 4, 2011, 12:11 am

    Very useful reference. Thanks!

  • mahendra May 23, 2011, 11:32 pm

    Its a good tutorial. I really like this.

  • Vick June 11, 2011, 8:22 pm

    Thanks for this!

  • Sachith July 14, 2011, 10:12 pm

    Thank you ! This has been very helpful for me!!

  • ubu_fan August 11, 2011, 11:36 am

    Very Informative . .
    Thanks Much

  • Raphael August 28, 2011, 8:59 pm

    Hi,

    What if the dump file outputs an big chunk of XML data of the entire database, how can I restore it back the entire schema + data back into the database? I have only manage to find out information on how to import back when the dump file is *.sql, not sure how can I import back if the output source file is a .xml.

    Any help is appreciated.

  • Chaitanya January 27, 2012, 12:09 pm

    How to restore *.db file.

    I know how to restore the *.sql dump file, but not aware of this one.
    please help..

  • Sathishkumar February 8, 2012, 5:03 am

    Hi Ramesh,
    i am working CentOS 4.1 with help of virtual box. installed mysql5.0 and i am installed groupware application , now i am backup the Application Database.
    i am import my db in mysql following this query,
    /usr/local/req/mysql-5.0/bin/mysql –defaults-file=/usr/local/req/mysql-5.0/etc/my.ini -u cbroot -p dbname.

    Now i am execute mysqldump command like following way,
    mysqldump -u cbroot -p dbname > filename.sql
    asking password,
    after giving password,
    showing error message like,
    mysqldump: Got error: 1045: Access denied for user ‘cbroot’@’localhost’ (using password: YES) when trying to connect.
    I am confused.
    I give a user privileges for cbroot user,
    +—————————————————————————————————————+
    | Grants for cbroot@localhost |
    +—————————————————————————————————————+
    | GRANT USAGE ON *.* TO ‘cbroot’@’localhost’ IDENTIFIED BY PASSWORD ‘*AFACA011891A17C03C93DA27B3D157E7A4964A22′ |
    | GRANT SELECT, LOCK TABLES ON `cb_grant`.* TO ‘cbroot’@’localhost’ |
    +——————————————————————————————————-+
    2 rows in set (0.00 sec)

    How can i solve this problem?

  • salazar March 2, 2012, 5:29 am

    thanks

  • San April 5, 2012, 2:41 pm

    How to restore a single table from the backup. I have the Production backups and user wants just that table to be cloned with another name and load it.

    Ho to restore the table backup to a different database in another server ?

  • Ron April 25, 2012, 4:23 pm

    Wow understandable documentation. Well done.Thank You.

  • daisy May 2, 2012, 1:09 pm

    Hi
    I have database this database contain on the many tables.some tables associated with each other by using (Id : unique and AUTO_INCREMENT) ,this database found on the many computers these computers associated (network) one of these computer as a server.I want make backup to these database on all computers and stored it in the unified database .
    Note:Each computer has same database independently. In the last day I want Compilation the databases information in the server computer database automatically.

    who can add new or active permissions for the system such as Interviewer,Supervisor and other privileges.I found his permissions in ohrm_user_role table.

  • Thomas June 26, 2012, 3:56 am

    Very useful post, thank you !

  • madhuri September 10, 2012, 2:20 am

    Very well organized and informative! Good work!

  • prusothaman October 31, 2012, 1:15 am

    Yeah very good article.

  • shridhara November 16, 2012, 2:54 am

    how can we restore multiple database please help me

  • Gurbrinder December 27, 2012, 4:10 pm

    Good article and well explained.

  • nikhil February 6, 2013, 3:26 pm

    I refer to this often. Thanks!

  • Tim March 8, 2013, 12:52 pm

    how to continuous backup and offsite delete after 12 years pls send code

    Just kidding, awesome write-up, works like a charm!

  • Clancy March 13, 2013, 7:20 pm

    Thanks for this article. I don’t remember what I was searching for when I found it, but it helped me to understand why the backup sql file I got from my hoster’s backup button never matched the export sql file I made from phpMyAdmin. :) Now, I use mysqldump instead.

  • Mike March 25, 2013, 12:34 pm

    Hi,
    if you want to save 1 schema to an outfile…

    mysqldump -u root -p “DATABASE” > outfile.sql;

    the -p is there to prompt you for a PW…. but you dont need to write it…

    (took me hours to figure this out…)
    good luck

  • SJ April 3, 2013, 7:48 am

    This was probably the best explained and displayed help and information page I have ever used.

  • prashant April 4, 2013, 11:01 am

    thanks it is help full. i am new for this database.

  • Radhakrishnan April 11, 2013, 3:33 am

    Hi Ramesh

    am using bugzilla and KTDMS in a windows box…how do i take backup of this since both are using MySql…..please suggest

  • Sowndharya April 23, 2013, 10:28 pm

    Hi can you tell how to take mysql db backup via tsm ?
    How to configure the tsm in mysql and take the db backup and send it to tsm?
    can you post the answer to me?

  • Rao M. June 11, 2013, 8:02 am

    Caution: I lost all permissions when I just did backup and restore using this article.

    Lesson to be learned, backup database using this article and save all permissions using ‘Show Grants’. Then perform Restore and apply ‘Grants’.

  • Thanh June 17, 2013, 9:38 pm

    Dear,
    I’ve tried the commend line
    mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

    However, at my localhost, i did not set password therefor, i dont know how to follow the commend line above.
    One more thing, could you help me to set the destination restore_file. It’s a bit inconvenient.

    Wait for your help.
    Thanks a lot.

  • John Mathis July 23, 2013, 12:17 am

    One change you might make on the backup… If your database happens to have stored procedures (I know, most mysql databases don’t have them…), but if you do, you have to use something like:

    # mysqldump -u root -p[root_password] –opt –routines –databases [database_name] > dumpfilename.sql

    Otherwise, the routines will be left behind, and not backed up.

  • Alim July 30, 2013, 6:07 am

    Personally I prefer to make a backup by using MySQL GUI tool like dbForge Studio for MySQL.

  • dangit August 19, 2013, 11:36 am

    I still have yet to find out how to backup all databases if the root password is blank.

    Because -p with no argument asks for a password, it can’t be automated.

    /Applications/XAMPP/xamppfiles/bin/mysqldump -u root -p”” –all-databases >/Applications/XAMPP/htdocs/mysql-dump.sql

    This does not work, either.

  • Rene August 26, 2013, 2:14 am

    Cool. Just one small comment: if you dump a single database there is no CREATE DATABASE (silly quirk, wondering why they don’t just fix this) but there will be if you add –databases, like this:

    # mysqldump -u root -p[root_password] –databases [database_name] > dumpfilename.sql

  • Lewis October 14, 2013, 2:46 pm

    I guess most people would be using this as some sort of scheduled task… does anyone have a script that they can share, that cleans up old backups? :)

  • Rene October 15, 2013, 2:55 am

    I use a bash script and schedule it from the root crontab:

    0 0 * * * /usr/local/bin/mysql_backup.sh

    The script generates a different filename for every weekday, thus implicitly cleaning up old backups. Mysqldump parameters may or may not work in your version of MySQL. As for the root password, I’m using .my.cnf for that so that’s why you don’t see it in the script.

    #!/bin/bash

    if [ $# -eq 0 ]; then
    echo “Usage: `basename $0` backup_dir”
    exit 1
    fi

    if [ ! -d “$1″ ]; then
    echo “Directory $1 does not exist.”
    exit 1
    fi

    backup_dir=$1
    day=`date +”%a”`
    dblist=`mysql -e “show databases” -B –skip-column-names`

    # Backup all databases except information_schema and performance_schema

    for dbname in $dblist
    do
    if [ “$dbname” != “information_schema” ] && [ “$dbname” != “performance_schema” ]; then
    backup_name=”${backup_dir}/${dbname}_${day}.sql.gz”
    mysqldump –events –single-transaction –opt –routines –triggers $dbname | gzip > $backup_name
    fi
    done

  • Hadi Ghoreyshi November 11, 2013, 12:13 am

    very nice and informative man…

  • kotesh November 15, 2013, 5:14 am

    Hi , when I use the command mysqldump it creates a dump file tilldate. later can we know till what timestamp the dump is present. because if a DB crashes and we want to restore it , we also use bin logs file. So i was bit confused in this, to get data with out missing/duplicating.

  • Wahid January 2, 2014, 5:42 am

    Hi RN,

    Very good and use full article for mysql database backup/restore (using mysqldump), Appreciate!!!

    \Regards
    _Wahid

  • Alan January 7, 2014, 7:02 am

    I keep using this as reference when I have to do this. I think you are world famous on the internet now, Ramesh. Way to go! 😀

  • Sol February 4, 2014, 1:19 am

    Excellent post/tutorial, very clear and complete, thank you much!

  • lampk April 11, 2014, 3:56 am

    Thank you, Ramesh ! this tutorial helps me a lot. It works great for my database backup.

  • indohub May 6, 2014, 5:46 am

    I use mysqldump -u root -p –databases db1 db2 db3 > db.sql to backup 3 databases.
    How to restore db.sql to 3 databases?

  • Todor June 12, 2014, 12:22 am

    Thank you for the detailed description!

  • Arun Chaudhary October 3, 2014, 4:13 am

    is this possible to create a database and source .sql file into it using a single command.
    i am trying this.
    mysql –user=”abc” –password=”abc” -e “create database DB;” < /home/chaudhary/d.sql

    Plz make me correct if using this in a wrong way.Thanks in advance.

Leave a Comment