{"id":1613,"date":"2026-09-03T16:59:19","date_gmt":"2026-09-03T11:29:19","guid":{"rendered":"https:\/\/cpanelfree.com\/blog\/how-to-setup-automated-daily-mysql-backups-cron\/"},"modified":"2026-09-03T17:02:35","modified_gmt":"2026-09-03T11:32:35","slug":"how-to-setup-automated-daily-mysql-backups-cron","status":"publish","type":"post","link":"https:\/\/cpanelfree.com\/blog\/how-to-setup-automated-daily-mysql-backups-cron\/","title":{"rendered":"How to Set Up Automated Daily MySQL Database Backups with Cron Job"},"content":{"rendered":"
\n

\n Quick Answer:<\/strong> To automate daily MySQL database backups, create a bash script executing mysqldump -u root -p'password' --single-transaction --quick dbname | gzip > \/var\/backups\/db_$(date +%F).sql.gz<\/code> and schedule it in Linux crontab (crontab -e<\/code>) to execute every night at 2:00 AM (0 2 * * * \/path\/to\/backup.sh<\/code>).\n <\/p>\n<\/div>\n

Why Automated Database Backups Are Non-Negotiable<\/h2>\n

Database corruption, accidental table drops, failed WordPress plugin updates, and ransomware attacks can instantly wipe out years of business data. Relying on manual backups guarantees data loss. A fully automated backup workflow with local retention rules<\/strong> and offsite cloud synchronization<\/strong> ensures you can restore any database in under 3 minutes.<\/p>\n

Step 1: Creating the Production Backup Bash Script<\/h2>\n

Create a dedicated script file on your Linux VPS:<\/p>\n

sudo mkdir -p \/var\/backups\/mysql\nsudo nano \/usr\/local\/bin\/mysql_auto_backup.sh<\/pre>\n
Paste the following production-grade bash backup script:<\/p>\n
#!\/bin\/bash\n# ==========================================================\n# Automated MySQL Daily Backup & Retention Script\n# ==========================================================\nBACKUP_DIR=\"\/var\/backups\/mysql\"\nTIMESTAMP=$(date +\"%Y-%m-%d_%H%M%S\")\nRETENTION_DAYS=7\n\n# MySQL Credentials (or use ~\/.my.cnf for password security)\nDB_USER=\"root\"\nDB_PASS=\"YourSecureDatabasePassword\"\n\n# Create target backup directory\nmkdir -p \"$BACKUP_DIR\"\n\n# Loop through all non-system databases and dump\nDATABASES=$(mysql -u \"$DB_USER\" -p\"$DB_PASS\" -e \"SHOW DATABASES;\" | grep -Ev \"(Database|information_schema|performance_schema|mysql|sys)\")\n\nfor DB in $DATABASES; do\n    echo \"Backing up database: $DB...\"\n    mysqldump -u \"$DB_USER\" -p\"$DB_PASS\"         --single-transaction         --quick         --routines         --triggers         \"$DB\" | gzip > \"$BACKUP_DIR\/${DB}_${TIMESTAMP}.sql.gz\"\ndone\n\n# Purge local backups older than retention threshold\nfind \"$BACKUP_DIR\" -type f -name \"*.sql.gz\" -mtime +$RETENTION_DAYS -delete\n\necho \"[$(date)] MySQL daily backup completed successfully.\" >> \/var\/log\/mysql_backup.log<\/pre>\n
Step 2: Granting Execution Permissions and Testing<\/h2>\n
# Secure permissions (root only)\nsudo chmod 700 \/usr\/local\/bin\/mysql_auto_backup.sh\n\n# Run manual test\nsudo \/usr\/local\/bin\/mysql_auto_backup.sh\n\n# Verify generated backup files\nls -lh \/var\/backups\/mysql\/<\/pre>\n
Step 3: Scheduling the Daily Cron Job<\/h2>\n
Open the root user’s crontab:<\/p>\n
sudo crontab -e<\/pre>\n
Add the following cron schedule to execute the script daily at 2:00 AM:<\/p>\n
0 2 * * * \/usr\/local\/bin\/mysql_auto_backup.sh >\/dev\/null 2>&1<\/pre>\n
Step 4: Syncing Backups to Cloud Storage (AWS S3 \/ Backblaze B2)<\/h2>\n
Using rclone<\/code>, append this command to the backup script to mirror daily backups to an offsite S3 cloud bucket:<\/p>\n
rclone sync \/var\/backups\/mysql\/ remote_s3:cpanelfree-db-backups\/daily\/<\/pre>\n
\n
\n        \ud83d\udd17<\/span> Recommended Related Technical Guides:
\n    <\/h3>\n