Backup and Restore MySQL Database Using mysqldump

by Ramesh Natarajan on September 22, 2008

[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.


Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

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… read them below or add one }

1 Ajith Edassery September 22, 2008 at 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

2 Saeed Pazoki September 22, 2008 at 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.

3 subhash September 24, 2008 at 12:26 am

Nice informative tip dude

4 Binny V A September 26, 2008 at 3:19 pm
5 Student November 27, 2008 at 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

6 mk_michael December 6, 2008 at 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.

7 michael December 18, 2008 at 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

8 Girish April 1, 2009 at 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 :-)

9 hari April 14, 2009 at 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.

10 Rick August 6, 2009 at 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?

11 Girish August 26, 2009 at 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.

12 Girish August 26, 2009 at 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

13 Krishnakumar M September 10, 2009 at 11:11 pm

Nice stuff. Easy to understand.

14 Roc Boronat January 11, 2010 at 9:44 am

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

15 Ray May 13, 2010 at 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!

16 vani June 2, 2010 at 4:50 am

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

17 Andrew July 12, 2010 at 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.

18 Bret July 18, 2010 at 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

19 Ashish July 29, 2010 at 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

20 Girish Mahajan September 3, 2010 at 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

21 DG September 23, 2010 at 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.

22 rinshad December 9, 2010 at 9:40 am

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

23 rinshad December 9, 2010 at 9:55 am

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

R!n5h@D !!

24 Manoj January 6, 2011 at 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..

25 binu March 16, 2011 at 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

26 Girish Mahajan March 17, 2011 at 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

27 Naz April 4, 2011 at 12:11 am

Very useful reference. Thanks!

28 mahendra May 23, 2011 at 11:32 pm

Its a good tutorial. I really like this.

29 Vick June 11, 2011 at 8:22 pm

Thanks for this!

30 Sachith July 14, 2011 at 10:12 pm

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

31 ubu_fan August 11, 2011 at 11:36 am

Very Informative . .
Thanks Much

32 Raphael August 28, 2011 at 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.

33 Chaitanya January 27, 2012 at 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..

34 Sathishkumar February 8, 2012 at 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?

35 salazar March 2, 2012 at 5:29 am

thanks

36 San April 5, 2012 at 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 ?

37 Ron April 25, 2012 at 4:23 pm

Wow understandable documentation. Well done.Thank You.

38 daisy May 2, 2012 at 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.

39 Thomas June 26, 2012 at 3:56 am

Very useful post, thank you !

40 madhuri September 10, 2012 at 2:20 am

Very well organized and informative! Good work!

41 prusothaman October 31, 2012 at 1:15 am

Yeah very good article.

42 shridhara November 16, 2012 at 2:54 am

how can we restore multiple database please help me

43 Gurbrinder December 27, 2012 at 4:10 pm

Good article and well explained.

44 nikhil February 6, 2013 at 3:26 pm

I refer to this often. Thanks!

45 Tim March 8, 2013 at 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!

46 Clancy March 13, 2013 at 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.

47 Mike March 25, 2013 at 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

48 SJ April 3, 2013 at 7:48 am

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

49 prashant April 4, 2013 at 11:01 am

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

50 Radhakrishnan April 11, 2013 at 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

51 Sowndharya April 23, 2013 at 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?

52 Rao M. June 11, 2013 at 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’.

53 Thanh June 17, 2013 at 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.

54 John Mathis July 23, 2013 at 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.

55 Alim July 30, 2013 at 6:07 am

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

56 dangit August 19, 2013 at 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.

57 Rene August 26, 2013 at 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

58 Lewis October 14, 2013 at 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? :)

59 Rene October 15, 2013 at 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

60 Hadi Ghoreyshi November 11, 2013 at 12:13 am

very nice and informative man…

61 kotesh November 15, 2013 at 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.

62 Wahid January 2, 2014 at 5:42 am

Hi RN,

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

\Regards
_Wahid

63 Alan January 7, 2014 at 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! :D

64 Sol February 4, 2014 at 1:19 am

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

65 lampk April 11, 2014 at 3:56 am

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

66 indohub May 6, 2014 at 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?

67 Todor June 12, 2014 at 12:22 am

Thank you for the detailed description!

68 Arun Chaudhary October 3, 2014 at 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

Previous post:

Next post: