![[mysqldump - MySQL Backup & Restore]](https://static.thegeekstuff.com/wp-content/uploads/2008/09/mysqldump1.jpg "[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.
My name is Ramesh Natarajan. I will be posting instruction guides, how-to, troubleshooting tips and tricks on Linux, database, hardware, security and web. My focus is to write articles that will either teach you or help you resolve a problem. Read more about
Comments on this entry are closed.
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
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.
Nice informative tip dude
I use a Perl Script to Backup MySQL Databases
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
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.
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
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 🙂
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.
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?
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.
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
Nice stuff. Easy to understand.
Thanks! This article has been very helpful for me 🙂
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!
how to restore mysql files and please send the total mysql meterial,mysql deployment.
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.
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
to restore from .sql file follow command:
mysql –user=xxxx –password=xxxx < all_databases_s1.sql
it will restore all ur databases.
njoy!!!
Ashish
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
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.
hai..
how to restore a single database from a dumpfile where i had the backup of my entire databases ..???
rinshad.
mysql -u root -p –one-database db_name < dump_file
R!n5h@D !!
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..
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
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
Very useful reference. Thanks!
Its a good tutorial. I really like this.
Thanks for this!
Thank you ! This has been very helpful for me!!
Very Informative . .
Thanks Much
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.
How to restore *.db file.
I know how to restore the *.sql dump file, but not aware of this one.
please help..
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?
thanks
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 ?
Wow understandable documentation. Well done.Thank You.
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.
Very useful post, thank you !
Very well organized and informative! Good work!
Yeah very good article.
how can we restore multiple database please help me
Good article and well explained.
I refer to this often. Thanks!
how to continuous backup and offsite delete after 12 years pls send code
Just kidding, awesome write-up, works like a charm!
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.
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
This was probably the best explained and displayed help and information page I have ever used.
thanks it is help full. i am new for this database.
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
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?
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’.
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.
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.
Personally I prefer to make a backup by using MySQL GUI tool like dbForge Studio for MySQL.
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.
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
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? 🙂
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
very nice and informative man…
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.
Hi RN,
Very good and use full article for mysql database backup/restore (using mysqldump), Appreciate!!!
\Regards
_Wahid
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! 😀
Excellent post/tutorial, very clear and complete, thank you much!
Thank you, Ramesh ! this tutorial helps me a lot. It works great for my database backup.
I use mysqldump -u root -p –databases db1 db2 db3 > db.sql to backup 3 databases.
How to restore db.sql to 3 databases?
Thank you for the detailed description!
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.
It’s working like a charm,. Thanks
I followed your steps to copy a database from one server to another. It appeared to have run very well, but when I use “show tables” or any select statement on the new server, I get 0 rows returned. What did I do wrong?
still relevant years later. thank you!
Thanks for the backup tips! Exactly what I’m looking for 🙂
Is it possible to take back up of db with where condition?
Small suggestion: I’m completely new to mySQL and spent hours trying to get it to work. My mistake: I was running the commands out of mySQL command line client instead of from windows command line. One line at the top of your article might help others avoid this mistake.
i have sourced one big mysql file in to one database
mysql>use db
mysql> source path name
now i need to spilit in to 4 databases for each district form the state dtabase how?
Muchas gracias
well…Sir I want to take backup from AWS RDS to my localhost ?? any solution pls?
Hi,
I’m getting error “Incorrect database name ‘#mysql50#.ssh’ when selecting all the databases” while executing mysqldump using below cmd
CMD> sudo mysqldump –all-databases -u root -p –flush-logs –single-transaction > all_databases.sql
Using Server version: 5.1.56
Please help me in resolving this issue