{"id":8005,"date":"2022-10-12T08:14:43","date_gmt":"2022-10-12T08:14:43","guid":{"rendered":"https:\/\/truehost.com\/support\/?post_type=ht_kb&p=8005"},"modified":"2024-06-07T13:02:47","modified_gmt":"2024-06-07T13:02:47","password":"","slug":"how-to-create-a-backup-of-mysql-databases-in-your-vps","status":"publish","type":"docs","link":"https:\/\/truehost.com\/support\/knowledge-base\/how-to-create-a-backup-of-mysql-databases-in-your-vps\/","title":{"rendered":"How to Create a Backup of MySQL Databases in your VPS"},"content":{"rendered":"\n

MySQL backups are mostly created as .sql dump files. These are flexible and can be imported to another MySQL instance or transferred$ mysql -N -e ‘show databases’ | while read dbname; do mysqldump –complete-insert –routines –triggers –single-transaction “$dbname” > \/var\/backups\/databases\/”$dbname”.sql; done around for safe keeping.<\/p>\n\n\n\n

The guide below will help you make backups of mysql<\/p>\n\n\n\n

Using phpMyAdmin<\/h2>\n\n\n\n

If your server is running phpMyAdmin, you can use it to create backups as follows<\/p>\n\n\n\n

1.Login to phpMyAdmin<\/p>\n\n\n\n

2.In the left column, click the name of the database you wish to backup.<\/p>\n\n\n\n

3.Click on the Export<\/strong> tab on the right. <\/p>\n\n\n\n

4.Choose the tables you wish to backup, or click Select All<\/strong> to backup all of your tables. <\/p>\n\n\n\n

5.Scroll down and ensure the Save as File<\/strong> box is checked off. Click the Go<\/strong> button found in the lower right corner. You will be prompted to find a place on your hard drive to store the backup file, and then the download will begin.<\/p>\n\n\n\n

Using SSH<\/h2>\n\n\n\n

You can run mysqldump command to backup your database. <\/p>\n\n\n\n

1.Login to the server via SSH as root user<\/p>\n\n\n\n

2.Create a folder you want to save your backup files. In this case, we will call this folder \/var\/backups\/databases<\/p>\n\n\n\n

To create a backup for a single database, run this command<\/p>\n\n\n\n

$ mysqldump database_name > database_name.sql<\/pre>\n\n\n\n
To create a backup of several databases, with each database having its own mysql file, run the command below<\/p>\n\n\n\n
$ mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction \"$dbname\" > \/var\/backups\/databases\/\"$dbname\".sql; done<\/pre>\n\n\n\n
You will see a list of .sql files created and saved on the specified folder.<\/p>\n\n\n\n
Note:<\/strong> The commands above need to be run as root user. Otherwise, you would need to specify database usernames and passwords on the commands. <\/p>\n\n\n\n
If you still get asked for a password, use the commands below and type in a password when prompted.<\/p>\n\n\n\n
For single database backup:<\/p>\n\n\n\n
$ mysqldump -uroot -p database_name<\/strong> > database_name.sql<\/pre>\n\n\n\n
For all databases, use this command<\/p>\n\n\n\n
$ mysql -uroot -pPASS_HERE<\/strong> -N -e 'show databases' | while read dbname; do mysqldump -uroot -pPASS_HERE <\/strong>--complete-insert --routines --triggers --single-transaction \"$dbname\" > \/var\/backups\/databases\/\"$dbname\".sql; done<\/pre>\n\n\n\n
Replace PASS_HERE<\/strong> with your actual mysql password. This prevents MySQL prompting you for passwords since if databases are many, you will have to enter the passwords very many times which is not practical.<\/p>\n","protected":false},"excerpt":{"rendered":"
MySQL backups are mostly created as .sql dump files. These are flexible and can be imported to another MySQL instance or transferred$ mysql -N -e ‘show databases’ | while read dbname; do mysqldump –complete-insert –routines –triggers –single-transaction “$dbname” > \/var\/backups\/databases\/”$dbname”.sql; done around for safe keeping. The guide below will help you make backups of mysql […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","template":"","meta":{"_eb_attr":"","_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"doc_category":[],"doc_tag":[],"class_list":["post-8005","docs","type-docs","status-publish","hentry"],"year_month":"2026-06","word_count":396,"total_views":0,"reactions":{"happy":0,"normal":0,"sad":0},"author_info":{"name":"w m","author_nicename":"wm","author_url":"https:\/\/truehost.com\/support\/author\/wm\/"},"doc_category_info":[],"doc_tag_info":[],"yoast_head":"\nHow to Create a Backup of MySQL Databases in your VPS -<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/truehost.com\/support\/knowledge-base\/how-to-create-a-backup-of-mysql-databases-in-your-vps\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Create a Backup of MySQL Databases in your VPS -\" \/>\n<meta property=\"og:description\" content=\"MySQL backups are mostly created as .sql dump files. These are flexible and can be imported to another MySQL instance or transferred$ mysql -N -e ‘show databases’ | while read dbname; do mysqldump –complete-insert –routines –triggers –single-transaction “$dbname” > \/var\/backups\/databases\/”$dbname”.sql; done around for safe keeping. The guide below will help you make backups of mysql […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/truehost.com\/support\/knowledge-base\/how-to-create-a-backup-of-mysql-databases-in-your-vps\/\" \/>\n<meta property=\"article:modified_time\" content=\"2024-06-07T13:02:47+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/truehost.com\\\/support\\\/knowledge-base\\\/how-to-create-a-backup-of-mysql-databases-in-your-vps\\\/\",\"url\":\"https:\\\/\\\/truehost.com\\\/support\\\/knowledge-base\\\/how-to-create-a-backup-of-mysql-databases-in-your-vps\\\/\",\"name\":\"How to Create a Backup of MySQL Databases in your VPS -\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/truehost.com\\\/support\\\/#website\"},\"datePublished\":\"2022-10-12T08:14:43+00:00\",\"dateModified\":\"2024-06-07T13:02:47+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/truehost.com\\\/support\\\/knowledge-base\\\/how-to-create-a-backup-of-mysql-databases-in-your-vps\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/truehost.com\\\/support\\\/knowledge-base\\\/how-to-create-a-backup-of-mysql-databases-in-your-vps\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/truehost.com\\\/support\\\/knowledge-base\\\/how-to-create-a-backup-of-mysql-databases-in-your-vps\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/truehost.com\\\/support\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Create a Backup of MySQL Databases in your VPS\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/truehost.com\\\/support\\\/#website\",\"url\":\"https:\\\/\\\/truehost.com\\\/support\\\/\",\"name\":\"\",\"description\":\"Help In a Click\",\"publisher\":{\"@id\":\"https:\\\/\\\/truehost.com\\\/support\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/truehost.com\\\/support\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/truehost.com\\\/support\\\/#organization\",\"name\":\"Truehost Kenya\",\"url\":\"https:\\\/\\\/truehost.com\\\/support\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/truehost.com\\\/support\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/truehost.com\\\/support\\\/wp-content\\\/uploads\\\/2026\\\/04\\\/cropped-image_2026-04-16_174808866.png\",\"contentUrl\":\"https:\\\/\\\/truehost.com\\\/support\\\/wp-content\\\/uploads\\\/2026\\\/04\\\/cropped-image_2026-04-16_174808866.png\",\"width\":240,\"height\":48,\"caption\":\"Truehost Kenya\"},\"image\":{\"@id\":\"https:\\\/\\\/truehost.com\\\/support\\\/#\\\/schema\\\/logo\\\/image\\\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Create a Backup of MySQL Databases in your VPS -","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:\/\/truehost.com\/support\/knowledge-base\/how-to-create-a-backup-of-mysql-databases-in-your-vps\/","og_locale":"en_US","og_type":"article","og_title":"How to Create a Backup of MySQL Databases in your VPS -","og_description":"MySQL backups are mostly created as .sql dump files. These are flexible and can be imported to another MySQL instance or transferred$ mysql -N -e ‘show databases’ | while read dbname; do mysqldump –complete-insert –routines –triggers –single-transaction “$dbname” > \/var\/backups\/databases\/”$dbname”.sql; done around for safe keeping. The guide below will help you make backups of mysql […]","og_url":"https:\/\/truehost.com\/support\/knowledge-base\/how-to-create-a-backup-of-mysql-databases-in-your-vps\/","article_modified_time":"2024-06-07T13:02:47+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/truehost.com\/support\/knowledge-base\/how-to-create-a-backup-of-mysql-databases-in-your-vps\/","url":"https:\/\/truehost.com\/support\/knowledge-base\/how-to-create-a-backup-of-mysql-databases-in-your-vps\/","name":"How to Create a Backup of MySQL Databases in your VPS -","isPartOf":{"@id":"https:\/\/truehost.com\/support\/#website"},"datePublished":"2022-10-12T08:14:43+00:00","dateModified":"2024-06-07T13:02:47+00:00","breadcrumb":{"@id":"https:\/\/truehost.com\/support\/knowledge-base\/how-to-create-a-backup-of-mysql-databases-in-your-vps\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/truehost.com\/support\/knowledge-base\/how-to-create-a-backup-of-mysql-databases-in-your-vps\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/truehost.com\/support\/knowledge-base\/how-to-create-a-backup-of-mysql-databases-in-your-vps\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/truehost.com\/support\/"},{"@type":"ListItem","position":2,"name":"How to Create a Backup of MySQL Databases in your VPS"}]},{"@type":"WebSite","@id":"https:\/\/truehost.com\/support\/#website","url":"https:\/\/truehost.com\/support\/","name":"","description":"Help In a Click","publisher":{"@id":"https:\/\/truehost.com\/support\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/truehost.com\/support\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/truehost.com\/support\/#organization","name":"Truehost Kenya","url":"https:\/\/truehost.com\/support\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/truehost.com\/support\/#\/schema\/logo\/image\/","url":"https:\/\/truehost.com\/support\/wp-content\/uploads\/2026\/04\/cropped-image_2026-04-16_174808866.png","contentUrl":"https:\/\/truehost.com\/support\/wp-content\/uploads\/2026\/04\/cropped-image_2026-04-16_174808866.png","width":240,"height":48,"caption":"Truehost Kenya"},"image":{"@id":"https:\/\/truehost.com\/support\/#\/schema\/logo\/image\/"}}]}},"_links":{"self":[{"href":"https:\/\/truehost.com\/support\/wp-json\/wp\/v2\/docs\/8005","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/truehost.com\/support\/wp-json\/wp\/v2\/docs"}],"about":[{"href":"https:\/\/truehost.com\/support\/wp-json\/wp\/v2\/types\/docs"}],"author":[{"embeddable":true,"href":"https:\/\/truehost.com\/support\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/truehost.com\/support\/wp-json\/wp\/v2\/comments?post=8005"}],"version-history":[{"count":1,"href":"https:\/\/truehost.com\/support\/wp-json\/wp\/v2\/docs\/8005\/revisions"}],"predecessor-version":[{"id":8006,"href":"https:\/\/truehost.com\/support\/wp-json\/wp\/v2\/docs\/8005\/revisions\/8006"}],"wp:attachment":[{"href":"https:\/\/truehost.com\/support\/wp-json\/wp\/v2\/media?parent=8005"}],"wp:term":[{"taxonomy":"doc_category","embeddable":true,"href":"https:\/\/truehost.com\/support\/wp-json\/wp\/v2\/doc_category?post=8005"},{"taxonomy":"doc_tag","embeddable":true,"href":"https:\/\/truehost.com\/support\/wp-json\/wp\/v2\/doc_tag?post=8005"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}