{"id":4669,"date":"2014-09-15T14:43:15","date_gmt":"2014-09-15T19:43:15","guid":{"rendered":"https:\/\/secure.rosehosting.com\/blog\/?p=4669"},"modified":"2022-06-03T03:46:40","modified_gmt":"2022-06-03T08:46:40","slug":"backup-and-restore-your-mysql-database","status":"publish","type":"post","link":"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/","title":{"rendered":"How to backup and restore MySQL database"},"content":{"rendered":"
<\/div>

\"How<\/p>\n

We’ll show you, How to backup and restore MySQL database. If you are a computer user then you probably know how bad is to accidentally lose some of your important data. To avoid such situations you should create a backup of your data and store the backup file in a safe place. By doing this you can easily restore the lost data and you can get back on track very quickly. Today, we will show you how to create a backup of your MySQL database and restore it later on your Linux VPS<\/a>. The procedure is very easy and could be very helpful especially if you store all your website data into a single database.<\/p>\n

<\/p>\n

Back up your MySQL database<\/strong><\/p>\n

The very first thing you need to do is to log in to your server via SSH. Once you log in, you will need to execute few commands in order to create a backup file of your database and copy the backup file on your local machine or different directory on your server. One of the commands is:<\/p>\n

mysqldump -u username -p database-name > backup-name.sql<\/pre>\n
Important notes:
\nDo not forget to replace the username with your actual username, the database-name with the name of the database you want to back up and the backup-name.sql with the name of the backup file.<\/p>\n
After you execute this command, you will be asked to enter the password for the username you are using to back up the database.<\/p>\n
Enter the password and that’s it! You have created a backup file of your database.<\/p>\n
For instance, if you like to create a backup file of your WordPressDB\u00a0 on your WordPress VPS<\/a> and save it as wordpress.sql using WordPressUser as user, you can enter:<\/p>\n
mysqldump -u WordPressUser -p WordPressDB > wordpress.sql<\/pre>\n
You should now insert the password of the WordPressUser.<\/p>\n
Now, it is important to copy or move the backup file to a safe directory on your server or on your local machine. You can do this using the ‘rsync’ command. It is very easy, so let’s do this.<\/p>\n
In order to copy the file to a different directory on your server use the following command:<\/p>\n
rsync -Waq backup-name.sql \/path\/to\/directory\/<\/pre>\n
Again, do not forget to replace the backup-name.sql with the name of your backup file as well as the \/path\/to\/directory\/ with the path to the directory where you want to copy your backup file.<\/p>\n
If you want to copy the backup-name.sql to your local machine you can use the command:<\/p>\n
rsync -Waq -e 'ssh -p port-number' username@IP-Address:\/backup-name.sql \/path\/to\/local\/directory<\/pre>\n
Here, you need to change the port-number with your SSH listening port, the username with the username you are using to connect to your server, the IP-Address with the IP address of your server, the backup-name.sql with the name of the backup file and the \/path\/to\/local\/directory with the path of the directory on your local machine where you want to save the backup file. Of course, you will have to execute this command from your local machine and you will be asked to enter the password for the username you are using to connect to your server.<\/p>\n
Restore your MySQL database<\/strong><\/p>\n
Once you create a backup file of your database you can easily restore it in case of an accident. To restore the backup file you need to execute the following command:<\/p>\n
mysql -u username -p database-name < backup-name.sql<\/pre>\n
Change the username, database-name and backup-name.sql with the actual values, enter your password and that’s it. You have successfully restored your database.<\/p>\n
Of course, you don\u2019t have to backup and restore MySQL database, if you use one of our Managed hosting services<\/a>, in which case you can simply ask our expert Linux admins to back up or restore your database for you. They are available 24\u00d77 and will take care of your request immediately. You can also try reading our guide on How to Create a Backup of MySQL Databases Using mysqldump on Ubuntu 20.04<\/a>.<\/p>\n
PS<\/span>. If you liked this post, on\u00a0How to backup and restore MySQL database, please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.<\/p>\n","protected":false},"excerpt":{"rendered":"
We’ll show you, How to backup and restore MySQL database. If you are a computer user then you probably know … <\/p>\n
Read More<\/a><\/p>\n","protected":false},"author":4,"featured_media":25785,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1702,21,13],"tags":[457,39],"yoast_head":"\nHow to backup and restore MySQL database - RoseHosting<\/title>\n<meta name=\"description\" content=\"How to backup and restore MySQL database - RoseHosting\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to backup and restore MySQL database - RoseHosting\" \/>\n<meta property=\"og:description\" content=\"How to backup and restore MySQL database - RoseHosting\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/\" \/>\n<meta property=\"og:site_name\" content=\"RoseHosting\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/RoseHosting\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/rosehosting.helpdesk\" \/>\n<meta property=\"article:published_time\" content=\"2014-09-15T19:43:15+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-06-03T08:46:40+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.rosehosting.com\/blog\/wp-content\/uploads\/2014\/09\/How-to-backup-and-restore-MySQL-database.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"600\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Jeff Wilson\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@rosehosting\" \/>\n<meta name=\"twitter:site\" content=\"@rosehosting\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jeff Wilson\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/\"},\"author\":{\"name\":\"Jeff Wilson\",\"@id\":\"https:\/\/www.rosehosting.com\/blog\/#\/schema\/person\/7ce77a842fa6a9a7f8efa186f2353713\"},\"headline\":\"How to backup and restore MySQL database\",\"datePublished\":\"2014-09-15T19:43:15+00:00\",\"dateModified\":\"2022-06-03T08:46:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/\"},\"wordCount\":682,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.rosehosting.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.rosehosting.com\/blog\/wp-content\/uploads\/2014\/09\/How-to-backup-and-restore-MySQL-database.jpg\",\"keywords\":[\"backup\",\"mysql\"],\"articleSection\":[\"Databases\",\"Tips and Tricks\",\"Tutorials\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/\",\"url\":\"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/\",\"name\":\"How to backup and restore MySQL database - RoseHosting\",\"isPartOf\":{\"@id\":\"https:\/\/www.rosehosting.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.rosehosting.com\/blog\/wp-content\/uploads\/2014\/09\/How-to-backup-and-restore-MySQL-database.jpg\",\"datePublished\":\"2014-09-15T19:43:15+00:00\",\"dateModified\":\"2022-06-03T08:46:40+00:00\",\"description\":\"How to backup and restore MySQL database - RoseHosting\",\"breadcrumb\":{\"@id\":\"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/#primaryimage\",\"url\":\"https:\/\/www.rosehosting.com\/blog\/wp-content\/uploads\/2014\/09\/How-to-backup-and-restore-MySQL-database.jpg\",\"contentUrl\":\"https:\/\/www.rosehosting.com\/blog\/wp-content\/uploads\/2014\/09\/How-to-backup-and-restore-MySQL-database.jpg\",\"width\":1200,\"height\":600,\"caption\":\"How to backup and restore MySQL database\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.rosehosting.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to backup and restore MySQL database\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.rosehosting.com\/blog\/#website\",\"url\":\"https:\/\/www.rosehosting.com\/blog\/\",\"name\":\"RoseHosting\",\"description\":\"Premium Linux Tutorials Since 2001\",\"publisher\":{\"@id\":\"https:\/\/www.rosehosting.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.rosehosting.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.rosehosting.com\/blog\/#organization\",\"name\":\"RoseHosting\",\"url\":\"https:\/\/www.rosehosting.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.rosehosting.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.rosehosting.com\/blog\/wp-content\/uploads\/2022\/03\/android-chrome-192x192-1.png\",\"contentUrl\":\"https:\/\/www.rosehosting.com\/blog\/wp-content\/uploads\/2022\/03\/android-chrome-192x192-1.png\",\"width\":192,\"height\":192,\"caption\":\"RoseHosting\"},\"image\":{\"@id\":\"https:\/\/www.rosehosting.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/RoseHosting\",\"https:\/\/x.com\/rosehosting\",\"https:\/\/www.linkedin.com\/in\/rosehosting\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.rosehosting.com\/blog\/#\/schema\/person\/7ce77a842fa6a9a7f8efa186f2353713\",\"name\":\"Jeff Wilson\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.rosehosting.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/09271207587f897ab46faaed9b355252?s=96&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/09271207587f897ab46faaed9b355252?s=96&r=g\",\"caption\":\"Jeff Wilson\"},\"description\":\"An experienced Linux veteran with many years of experience. Helping other Linux admins with frequent Linux and business-related blog posts on the RoseHosting blog. Techie by choice. Loving nature and travel. Happily married and father of two lovely children.\",\"sameAs\":[\"https:\/\/www.rosehosting.com\",\"https:\/\/www.facebook.com\/rosehosting.helpdesk\"],\"url\":\"https:\/\/www.rosehosting.com\/blog\/author\/jwilson\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to backup and restore MySQL database - RoseHosting","description":"How to backup and restore MySQL database - RoseHosting","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/","og_locale":"en_US","og_type":"article","og_title":"How to backup and restore MySQL database - RoseHosting","og_description":"How to backup and restore MySQL database - RoseHosting","og_url":"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/","og_site_name":"RoseHosting","article_publisher":"https:\/\/www.facebook.com\/RoseHosting","article_author":"https:\/\/www.facebook.com\/rosehosting.helpdesk","article_published_time":"2014-09-15T19:43:15+00:00","article_modified_time":"2022-06-03T08:46:40+00:00","og_image":[{"width":1200,"height":600,"url":"https:\/\/www.rosehosting.com\/blog\/wp-content\/uploads\/2014\/09\/How-to-backup-and-restore-MySQL-database.jpg","type":"image\/jpeg"}],"author":"Jeff Wilson","twitter_card":"summary_large_image","twitter_creator":"@rosehosting","twitter_site":"@rosehosting","twitter_misc":{"Written by":"Jeff Wilson","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/#article","isPartOf":{"@id":"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/"},"author":{"name":"Jeff Wilson","@id":"https:\/\/www.rosehosting.com\/blog\/#\/schema\/person\/7ce77a842fa6a9a7f8efa186f2353713"},"headline":"How to backup and restore MySQL database","datePublished":"2014-09-15T19:43:15+00:00","dateModified":"2022-06-03T08:46:40+00:00","mainEntityOfPage":{"@id":"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/"},"wordCount":682,"commentCount":0,"publisher":{"@id":"https:\/\/www.rosehosting.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/#primaryimage"},"thumbnailUrl":"https:\/\/www.rosehosting.com\/blog\/wp-content\/uploads\/2014\/09\/How-to-backup-and-restore-MySQL-database.jpg","keywords":["backup","mysql"],"articleSection":["Databases","Tips and Tricks","Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/","url":"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/","name":"How to backup and restore MySQL database - RoseHosting","isPartOf":{"@id":"https:\/\/www.rosehosting.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/#primaryimage"},"image":{"@id":"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/#primaryimage"},"thumbnailUrl":"https:\/\/www.rosehosting.com\/blog\/wp-content\/uploads\/2014\/09\/How-to-backup-and-restore-MySQL-database.jpg","datePublished":"2014-09-15T19:43:15+00:00","dateModified":"2022-06-03T08:46:40+00:00","description":"How to backup and restore MySQL database - RoseHosting","breadcrumb":{"@id":"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/#primaryimage","url":"https:\/\/www.rosehosting.com\/blog\/wp-content\/uploads\/2014\/09\/How-to-backup-and-restore-MySQL-database.jpg","contentUrl":"https:\/\/www.rosehosting.com\/blog\/wp-content\/uploads\/2014\/09\/How-to-backup-and-restore-MySQL-database.jpg","width":1200,"height":600,"caption":"How to backup and restore MySQL database"},{"@type":"BreadcrumbList","@id":"https:\/\/www.rosehosting.com\/blog\/backup-and-restore-your-mysql-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.rosehosting.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to backup and restore MySQL database"}]},{"@type":"WebSite","@id":"https:\/\/www.rosehosting.com\/blog\/#website","url":"https:\/\/www.rosehosting.com\/blog\/","name":"RoseHosting","description":"Premium Linux Tutorials Since 2001","publisher":{"@id":"https:\/\/www.rosehosting.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.rosehosting.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.rosehosting.com\/blog\/#organization","name":"RoseHosting","url":"https:\/\/www.rosehosting.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.rosehosting.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.rosehosting.com\/blog\/wp-content\/uploads\/2022\/03\/android-chrome-192x192-1.png","contentUrl":"https:\/\/www.rosehosting.com\/blog\/wp-content\/uploads\/2022\/03\/android-chrome-192x192-1.png","width":192,"height":192,"caption":"RoseHosting"},"image":{"@id":"https:\/\/www.rosehosting.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/RoseHosting","https:\/\/x.com\/rosehosting","https:\/\/www.linkedin.com\/in\/rosehosting\/"]},{"@type":"Person","@id":"https:\/\/www.rosehosting.com\/blog\/#\/schema\/person\/7ce77a842fa6a9a7f8efa186f2353713","name":"Jeff Wilson","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.rosehosting.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/09271207587f897ab46faaed9b355252?s=96&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/09271207587f897ab46faaed9b355252?s=96&r=g","caption":"Jeff Wilson"},"description":"An experienced Linux veteran with many years of experience. Helping other Linux admins with frequent Linux and business-related blog posts on the RoseHosting blog. Techie by choice. Loving nature and travel. Happily married and father of two lovely children.","sameAs":["https:\/\/www.rosehosting.com","https:\/\/www.facebook.com\/rosehosting.helpdesk"],"url":"https:\/\/www.rosehosting.com\/blog\/author\/jwilson\/"}]}},"_links":{"self":[{"href":"https:\/\/www.rosehosting.com\/blog\/wp-json\/wp\/v2\/posts\/4669"}],"collection":[{"href":"https:\/\/www.rosehosting.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rosehosting.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rosehosting.com\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rosehosting.com\/blog\/wp-json\/wp\/v2\/comments?post=4669"}],"version-history":[{"count":1,"href":"https:\/\/www.rosehosting.com\/blog\/wp-json\/wp\/v2\/posts\/4669\/revisions"}],"predecessor-version":[{"id":36646,"href":"https:\/\/www.rosehosting.com\/blog\/wp-json\/wp\/v2\/posts\/4669\/revisions\/36646"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rosehosting.com\/blog\/wp-json\/wp\/v2\/media\/25785"}],"wp:attachment":[{"href":"https:\/\/www.rosehosting.com\/blog\/wp-json\/wp\/v2\/media?parent=4669"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rosehosting.com\/blog\/wp-json\/wp\/v2\/categories?post=4669"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rosehosting.com\/blog\/wp-json\/wp\/v2\/tags?post=4669"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}