{"id":517,"date":"2026-06-26T03:14:57","date_gmt":"2026-06-26T03:14:57","guid":{"rendered":"https:\/\/virtualserversvps.com\/blog\/?p=517"},"modified":"2026-06-26T03:14:57","modified_gmt":"2026-06-26T03:14:57","slug":"how-to-set-up-automated-database-backups-on-your-vps-mysql-and-postgresql","status":"publish","type":"post","link":"https:\/\/virtualserversvps.com\/blog\/how-to-set-up-automated-database-backups-on-your-vps-mysql-and-postgresql\/","title":{"rendered":"How to Set Up Automated Database Backups on Your VPS: MySQL and PostgreSQL"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Database backups are your safety net. Whether you run MySQL or PostgreSQL on your VPS, an automated offsite backup strategy ensures you can recover from corruption, accidental deletion, or server failure in minutes instead of days. This guide walks through setting up automated backups using cron, mysqldump\/pg_dump, and offsite storage \u2014 with scripts you can deploy in under 15 minutes.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Prerequisites<\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li>A Linux VPS (Ubuntu 22.04\/24.04 recommended) with root or sudo access<\/li><li>MySQL 8.0+ or PostgreSQL 15+ installed and running<\/li><li>An offsite storage destination (we&#8217;ll cover S3-compatible, rsync, and cloud storage options)<\/li><li>Basic familiarity with the command line<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Step 1: Creating the Backup Script for MySQL<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Create a dedicated backup user with minimal privileges \u2014 never use root for automated backups:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE USER 'backup'@'localhost' IDENTIFIED BY 'strong-password-here';\nGRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';\nFLUSH PRIVILEGES;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Now create the backup script at <code>\/usr\/local\/bin\/backup-mysql.sh<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>#!\/bin\/bash\nset -e\n\nDB_USER=\"backup\"\nDB_PASS=\"strong-password-here\"\nBACKUP_DIR=\"\/var\/backups\/mysql\"\nDATE=$(date +%Y-%m-%d_%H-%M-%S)\nRETENTION_DAYS=7\n\nmkdir -p \"$BACKUP_DIR\"\n\n# Dump each database separately\ndatabases=$(mysql -u\"$DB_USER\" -p\"$DB_PASS\" -e \"SHOW DATABASES;\" | grep -Ev \"(Database|information_schema|performance_schema|sys)\")\n\nfor db in $databases; do\n    mysqldump --single-transaction --routines --triggers         -u\"$DB_USER\" -p\"$DB_PASS\" \"$db\"         | gzip &gt; \"$BACKUP_DIR\/${db}_${DATE}.sql.gz\"\n    echo \"Backed up: $db\"\ndone\n\n# Remove backups older than retention period\nfind \"$BACKUP_DIR\" -name \"*.sql.gz\" -mtime +$RETENTION_DAYS -delete\n\necho \"MySQL backup completed: $DATE\"<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Make it executable: <code>chmod +x \/usr\/local\/bin\/backup-mysql.sh<\/code><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 2: Creating the Backup Script for PostgreSQL<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">PostgreSQL uses <code>pg_dump<\/code> per database or <code>pg_dumpall<\/code> for a cluster-level backup. Create <code>\/usr\/local\/bin\/backup-pgsql.sh<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>#!\/bin\/bash\nset -e\n\nBACKUP_DIR=\"\/var\/backups\/postgresql\"\nDATE=$(date +%Y-%m-%d_%H-%M-%S)\nRETENTION_DAYS=7\n\nmkdir -p \"$BACKUP_DIR\"\n\n# Get list of databases (exclude template databases)\ndatabases=$(sudo -u postgres psql -t -c \"SELECT datname FROM pg_database WHERE datistemplate = false;\")\n\nfor db in $databases; do\n    sudo -u postgres pg_dump --no-owner --compress=9 \"$db\"         &gt; \"$BACKUP_DIR\/${db}_${DATE}.sql.gz\"\n    echo \"Backed up: $db\"\ndone\n\n# Remove old backups\nfind \"$BACKUP_DIR\" -name \"*.sql.gz\" -mtime +$RETENTION_DAYS -delete\n\necho \"PostgreSQL backup completed: $DATE\"<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Make it executable: <code>chmod +x \/usr\/local\/bin\/backup-pgsql.sh<\/code><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 3: Automating with Cron<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Add a cron entry to run daily at 3 AM:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># Edit crontab\nsudo crontab -e\n\n# Add one of these lines depending on your database:\n# MySQL\n0 3 * * * \/usr\/local\/bin\/backup-mysql.sh &gt;&gt; \/var\/log\/db-backup.log 2&gt;&amp;1\n\n# PostgreSQL\n0 3 * * * \/usr\/local\/bin\/backup-pgsql.sh &gt;&gt; \/var\/log\/db-backup.log 2&gt;&amp;1<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">For databases that can&#8217;t tolerate losing up to 24 hours of data, add an hourly differential backup:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># Hourly incremental (MySQL) \u2014 requires binary logging\n0 * * * * mysql -u\"$DB_USER\" -p\"$DB_PASS\" -e \"FLUSH BINARY LOGS;\" &amp;&amp;   cp \/var\/lib\/mysql\/binlog.* \/var\/backups\/mysql\/binlogs\/<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 4: Offsite Storage Options<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">A backup on the same server is not a real backup \u2014 if the disk fails, you lose both the database and its backup. Here are offsite options sorted by ease of setup:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>Method<\/th><th>Setup Time<\/th><th>Monthly Cost<\/th><th>Best For<\/th><\/tr><\/thead><tbody><tr><td>rsync to another VPS<\/td><td>10 minutes<\/td><td>$5\u201310 additional VPS<\/td><td>Users with a second server<\/td><\/tr><tr><td>S3-compatible object storage<\/td><td>15 minutes<\/td><td>$2\u20135 per 100 GB<\/td><td>Production workloads<\/td><\/tr><tr><td>rsync.net \/ BorgBase<\/td><td>20 minutes<\/td><td>$12\u201315 per 100 GB<\/td><td>Offsite dedicated backup service<\/td><\/tr><tr><td>Google Drive \/ Dropbox (rclone)<\/td><td>10 minutes<\/td><td>Free (up to 15 GB)<\/td><td>Personal\/low-volume backups<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n<p class=\"wp-block-paragraph\">Using rclone for S3-compatible storage:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># Install rclone\nsudo apt install rclone -y\nrclone config  # Follow prompts for your S3 provider\n\n# Add to the backup script\nrclone sync \"$BACKUP_DIR\" \"remote:bucket-name\/db-backups\/\"<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 5: Testing Your Backups<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Untested backups are no backups at all. Add a monthly restore test using a separate staging VPS:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># Test restore for MySQL\ngunzip &lt; \/var\/backups\/mysql\/mydb_2026-01-01_03-00-00.sql.gz | mysql -u root -p\n\n# Test restore for PostgreSQL\ngunzip &lt; \/var\/backups\/postgresql\/mydb_2026-01-01_03-00-00.sql.gz | sudo -u postgres psql<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Monitoring Backup Health<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Add basic health checks to detect backup failures before you need them:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Check backup file size \u2014 a zero-byte file means the backup failed silently<\/li><li>Monitor backup logs (<code>\/var\/log\/db-backup.log<\/code>) for error strings<\/li><li>Use Uptime Kuma or Healthchecks.io to ping a URL after each successful backup<\/li><li>Set up email or Slack alerts when backup size changes by more than 20%<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Putting It All Together<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Automated database backups on a VPS are straightforward: create a backup script, schedule it with cron, and sync the results offsite. The entire setup takes under 30 minutes and will save you days of downtime if something goes wrong. Compare <a href=\"https:\/\/virtualserversvps.com\/#providers\">VPS plans on our comparison table<\/a> to find a provider with sufficient storage for your backup retention needs.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Database backups are your safety net. Whether you run MySQL or PostgreSQL on your VPS, an automated offsite backup strategy ensures you can recover from corruption, accidental deletion, or server&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":1,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-517","post","type-post","status-publish","format-standard","hentry","category-vps-guides-tutorials"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v26.1 (Yoast SEO v26.1) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to Set Up Automated Database Backups on Your VPS: MySQL and PostgreSQL - Virtual Servers VPS Blog<\/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:\/\/virtualserversvps.com\/blog\/how-to-set-up-automated-database-backups-on-your-vps-mysql-and-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Set Up Automated Database Backups on Your VPS: MySQL and PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"How to Set Up Automated Database Backups on Your VPS: MySQL and PostgreSQL\" \/>\n<meta property=\"og:url\" content=\"https:\/\/virtualserversvps.com\/blog\/how-to-set-up-automated-database-backups-on-your-vps-mysql-and-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"Virtual Servers VPS Blog\" \/>\n<meta property=\"article:published_time\" content=\"2026-06-26T03:14:57+00:00\" \/>\n<meta name=\"author\" content=\"Virtual-Servers-Vps-Editor\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Virtual-Servers-Vps-Editor\" \/>\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\":\"WebPage\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/how-to-set-up-automated-database-backups-on-your-vps-mysql-and-postgresql\/\",\"url\":\"https:\/\/virtualserversvps.com\/blog\/how-to-set-up-automated-database-backups-on-your-vps-mysql-and-postgresql\/\",\"name\":\"How to Set Up Automated Database Backups on Your VPS: MySQL and PostgreSQL - Virtual Servers VPS Blog\",\"isPartOf\":{\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#website\"},\"datePublished\":\"2026-06-26T03:14:57+00:00\",\"author\":{\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0\"},\"breadcrumb\":{\"@id\":\"https:\/\/virtualserversvps.com\/blog\/how-to-set-up-automated-database-backups-on-your-vps-mysql-and-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/virtualserversvps.com\/blog\/how-to-set-up-automated-database-backups-on-your-vps-mysql-and-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/how-to-set-up-automated-database-backups-on-your-vps-mysql-and-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/virtualserversvps.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Set Up Automated Database Backups on Your VPS: MySQL and PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#website\",\"url\":\"https:\/\/virtualserversvps.com\/blog\/\",\"name\":\"Virtual Servers VPS Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/virtualserversvps.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0\",\"name\":\"Virtual-Servers-Vps-Editor\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d820b15f1cd028e97610d9adf536df7be5cb6423869967037d468d5355fa003f?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d820b15f1cd028e97610d9adf536df7be5cb6423869967037d468d5355fa003f?s=96&d=mm&r=g\",\"caption\":\"Virtual-Servers-Vps-Editor\"},\"sameAs\":[\"https:\/\/virtualserversvps.com\/blog\"],\"url\":\"https:\/\/virtualserversvps.com\/blog\/author\/virtualserversvps\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to Set Up Automated Database Backups on Your VPS: MySQL and PostgreSQL - Virtual Servers VPS Blog","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:\/\/virtualserversvps.com\/blog\/how-to-set-up-automated-database-backups-on-your-vps-mysql-and-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"How to Set Up Automated Database Backups on Your VPS: MySQL and PostgreSQL","og_description":"How to Set Up Automated Database Backups on Your VPS: MySQL and PostgreSQL","og_url":"https:\/\/virtualserversvps.com\/blog\/how-to-set-up-automated-database-backups-on-your-vps-mysql-and-postgresql\/","og_site_name":"Virtual Servers VPS Blog","article_published_time":"2026-06-26T03:14:57+00:00","author":"Virtual-Servers-Vps-Editor","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Virtual-Servers-Vps-Editor","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/virtualserversvps.com\/blog\/how-to-set-up-automated-database-backups-on-your-vps-mysql-and-postgresql\/","url":"https:\/\/virtualserversvps.com\/blog\/how-to-set-up-automated-database-backups-on-your-vps-mysql-and-postgresql\/","name":"How to Set Up Automated Database Backups on Your VPS: MySQL and PostgreSQL - Virtual Servers VPS Blog","isPartOf":{"@id":"https:\/\/virtualserversvps.com\/blog\/#website"},"datePublished":"2026-06-26T03:14:57+00:00","author":{"@id":"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0"},"breadcrumb":{"@id":"https:\/\/virtualserversvps.com\/blog\/how-to-set-up-automated-database-backups-on-your-vps-mysql-and-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/virtualserversvps.com\/blog\/how-to-set-up-automated-database-backups-on-your-vps-mysql-and-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/virtualserversvps.com\/blog\/how-to-set-up-automated-database-backups-on-your-vps-mysql-and-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/virtualserversvps.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to Set Up Automated Database Backups on Your VPS: MySQL and PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/virtualserversvps.com\/blog\/#website","url":"https:\/\/virtualserversvps.com\/blog\/","name":"Virtual Servers VPS Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/virtualserversvps.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0","name":"Virtual-Servers-Vps-Editor","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d820b15f1cd028e97610d9adf536df7be5cb6423869967037d468d5355fa003f?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d820b15f1cd028e97610d9adf536df7be5cb6423869967037d468d5355fa003f?s=96&d=mm&r=g","caption":"Virtual-Servers-Vps-Editor"},"sameAs":["https:\/\/virtualserversvps.com\/blog"],"url":"https:\/\/virtualserversvps.com\/blog\/author\/virtualserversvps\/"}]}},"_links":{"self":[{"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/posts\/517","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/comments?post=517"}],"version-history":[{"count":1,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/posts\/517\/revisions"}],"predecessor-version":[{"id":520,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/posts\/517\/revisions\/520"}],"wp:attachment":[{"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/media?parent=517"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/categories?post=517"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/tags?post=517"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}