Databases Intermediate 15 min

How to Set Up Automatic MySQL & MariaDB Database Backups on Linux

Automate daily compressed SQL dumps with automated timestamping, 30-day retention cleanup, and cron scheduling

IndSoft Cloud Engineering
Reviewed October 2026
Production Verified Commands

Guide Overview & Architecture Outcome

By following this guide, your MySQL or MariaDB databases will back up automatically every night into compressed .sql.gz archives. A retention script will automatically delete archives older than 30 days to conserve disk space.

Table of Contents
Step 1: Create Secure Backup Directory and D... Step 2: Create the Automated Backup Shell Sc... Step 3: Schedule Daily Execution via Cron... Check That It Worked Troubleshooting
Practice on Live Cloud

Spin up an Ubuntu 24.04 instance with instant activation to test this tutorial:

Deploy IndSoft VPS

Prerequisites & System Requirements

A Linux VPS running MySQL or MariaDB with at least 5 GB free disk space.

1

Create Secure Backup Directory and Dedicated Backup User

Create a restricted directory and generate a dedicated database user with SELECT and LOCK TABLES privileges.

bash · step-1-command.sh
sudo mkdir -p /var/backups/mysql sudo chmod 700 /var/backups/mysql sudo mysql -e "CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'STRONG_BACKUP_PASSWORD'; GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES ON *.* TO 'backupuser'@'localhost'; FLUSH PRIVILEGES;"
Expected System Output:
Query OK, 0 rows affected
2

Create the Automated Backup Shell Script

Create a script at /usr/local/bin/mysql-backup.sh to dump all databases with gzip compression.

bash · step-2-command.sh
sudo nano /usr/local/bin/mysql-backup.sh # Script sets: BACKUP_DIR, DATE=$(date +%Y-%m-%d_%H%M%S) # mysqldump --all-databases | gzip > $BACKUP_DIR/db-$DATE.sql.gz # find $BACKUP_DIR -name "*.sql.gz" -mtime +30 -delete sudo chmod +x /usr/local/bin/mysql-backup.sh
Expected System Output:
Script created and executable permissions granted.
3

Schedule Daily Execution via Cron

Add a crontab entry to execute the backup script automatically every night at 2:30 AM.

bash · step-3-command.sh
sudo crontab -e # Add: 30 2 * * * /usr/local/bin/mysql-backup.sh > /var/log/mysql-backup.log 2>&1
Expected System Output:
crontab: installing new crontab

Check That It Worked (Verification)

Run this quick verification check to confirm your installation is responding properly:

verify-service.sh
sudo /usr/local/bin/mysql-backup.sh && ls -lh /var/backups/mysql
Expected Response:
-rw-r--r-- 1 root root 14.8M Oct 14 02:30 all_databases_2026-10-14.sql.gz

Troubleshooting Common Errors

Quick resolutions for frequent setup pitfalls and configuration issues.

Diagnostic Tips
mysqldump: Got error: 1045: Access denied
Store credentials in a secure /root/.my.cnf configuration file with permissions chmod 600 so scripts don't expose passwords in process lists.
Disk full error during dump
For massive multi-gigabyte databases, pipe dumps table by table or add an offsite mounted block volume on IndSoft Cloud.
Corrupted restore test
Always test restoring backups to a staging database: gunzip < backup.sql.gz | mysql staging_db.