{"id":213,"date":"2013-01-18T15:32:45","date_gmt":"2013-01-18T15:32:45","guid":{"rendered":"http:\/\/a1webdesignteam.com\/blog\/?p=213"},"modified":"2013-01-18T15:32:45","modified_gmt":"2013-01-18T15:32:45","slug":"mysql-database-backup-mysqldump-command-2","status":"publish","type":"post","link":"https:\/\/a1webdesignteam.com\/blog\/mysql-database-backup-mysqldump-command-2\/","title":{"rendered":"MySQL Database Backup using mysqldump command."},"content":{"rendered":"

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.\u00a0 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.<\/p>\n

MySQL provide a great command line utility to take backup of your MySQL database and restore it. mysqldump <\/strong>command line utility is available with MySQL installation (bin directory) that can be used to achieve this.<\/p>\n

1. Getting backup of a MySQL database using mysqldump.<\/h2>\n

Use following command line for taking backup of your MySQL database using mysqldump utility.<\/p>\n

\n
\n\n\n\n
\n
\n
mysqldump \u2013-<\/code>user<\/code> [<\/code>user<\/code> name<\/code>] \u2013-<\/code>password<\/code>=[<\/code>password<\/code>] [<\/code>database<\/code> name<\/code>] > [dump file]<\/code><\/div>\n
<\/div>\n
or<\/code><\/div>\n
<\/div>\n
mysqldump \u2013u[<\/code>user<\/code> name<\/code>] \u2013p[<\/code>password<\/code>] [<\/code>database<\/code> name<\/code>] > [dump file]<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Example:<\/p>\n

\n
\n\n\n\n
\n
\n
mysqldump \u2013-<\/code>user<\/code> root \u2013-<\/code>password<\/code>=myrootpassword db_test > db_test.sql<\/code><\/div>\n
<\/div>\n
or<\/code><\/div>\n
<\/div>\n
mysqldump \u2013uroot \u2013pmyrootpassword db_test > db_test.sql<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

2. Backup multiple databases in MySQL.<\/h2>\n
\n
\n\n\n\n
\n
\n
mysqldump \u2013u[<\/code>user<\/code> name<\/code>] \u2013p[<\/code>password<\/code>] [<\/code>database<\/code> name<\/code> 1] [<\/code>database<\/code> name<\/code> 2] .. > [dump file]<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Example:<\/p>\n

\n
\n\n\n\n
\n
\n
mysqldump \u2013-<\/code>user<\/code> root \u2013-<\/code>password<\/code>=myrootpassword db_test db_second db_third > db_test.sql<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

3. Backup all databases in MySQL.<\/h2>\n
\n
\n\n\n\n
\n
\n
shell> mysqldump \u2013u[<\/code>user<\/code> name<\/code>] \u2013p[<\/code>password<\/code>] \u2013<\/code>all<\/code>-databases > [dump file]<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

4. Backup a specific table in MySQL.<\/h2>\n
\n
\n\n\n\n
\n
\n
shell> mysqldump <\/code>--user [username] --password=[password] [database name] [table name] \\<\/code><\/div>\n
> \/tmp\/sugarcrm_accounts_contacts.sql<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Example:<\/p>\n

\n
\n\n\n\n
\n
\n
shell> mysqldump <\/code>--user root --password=myrootpassword db_test customers \\<\/code><\/div>\n
> db_test_customers.sql<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

5. Restoring MySQL database.<\/h2>\n

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<\/strong> command.<\/p>\n

\n
\n\n\n\n
\n
\n
shell> mysql <\/code>--u [username] --password=[password] [database name] < [dump file]<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Example:<\/p>\n

\n
shell> mysql <\/code>--user root --password=myrootpassword new_db < db_test.sql<\/code><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

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.\u00a0 Its popularity for use with web applications is closely tied to the popularity of PHP, which is often combined with MySQL. Wikipedia runs on […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0},"categories":[9,7],"tags":[23],"_links":{"self":[{"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/posts\/213"}],"collection":[{"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/comments?post=213"}],"version-history":[{"count":0,"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/posts\/213\/revisions"}],"wp:attachment":[{"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/media?parent=213"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/categories?post=213"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/tags?post=213"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}