How to Backup MySQL Cronjob for Daily Dumps + Cleanup

MySQL Backup at Fixed Time: How to Create a Cronjob for Daily Dumps (02:30) + Cleanup at 7 Days (03:00). Learn how to configure two secure cronjobs on your server: a daily MySQL/MariaDB database backup at 02:30 using mysqldump, followed by a cleanup at 03:00 that automatically deletes backup files older than 7 days. Step-by-step guide included.

Why do you need cronjobs for database backups?

Even if your hosting provider promises backups, having your own control lowers the risk of data loss, shortens restore times, and ensures compliance with regulations (GDPR, audits, SLA). The recommended strategy is “3-2-1”:

• 3 copies of your data,
• 2 different storage media,
• 1 off-site backup.

In this article, you’ll learn how to set up two cronjobs:

1. Daily backup at 02:30 (using mysqldump + compression).
2. Cleanup at 03:00 that deletes files older than 7 days.

We’ll also cover password security, file permissions, logging, verification, options for busy servers, plus variations for cPanel/Plesk, Docker, WP-CLI, GPG encryption, and S3 upload.

Prerequisites

• SSH access (root or user with crontab permissions).
• mysqldump installed (comes with MySQL/MariaDB client tools).
• A dedicated backup directory, e.g. /var/backups/mysql.
• Free runtime window between 02:30–03:30 (low traffic).
• Optional: an email address for cron notifications (MAILTO).

Secure password storage: .my.cnf with strict permissions

Never put your password directly in the command line. Instead, create a config file for root (or the backup user):

sudo -i
nano /root/.my.cnf

Content:

[client]
user=backupuser
password=VERY_STRONG_PASSWORD
host=localhost
# socket=/var/run/mysqld/mysqld.sock   # optional, if using socket

Protect the file:

chmod 600 /root/.my.cnf

This way, mysqldump can run without exposing the password in logs or process lists.

Backup directory structure

mkdir -p /var/backups/mysql/$(hostname -f)
chmod -R 700 /var/backups/mysql

Using $(hostname -f) keeps backups separate per server.

Robust backup script (logging + compression + checksum)

File: /usr/local/bin/backup_mysql.sh

#!/usr/bin/env bash
set -euo pipefail

# Config
BACKUP_ROOT="/var/backups/mysql"
HOSTDIR="$(hostname -f)"
DEST_DIR="${BACKUP_ROOT}/${HOSTDIR}"
LOGFILE="/var/log/mysql_backup.log"
DATE="$(date +'%Y-%m-%d_%H-%M-%S')"
DUMP_FILE="${DEST_DIR}/mysql-${DATE}.sql"
ARCHIVE="${DUMP_FILE}.gz"

mkdir -p "${DEST_DIR}"
chmod 700 "${DEST_DIR}"
touch "${LOGFILE}"
chmod 600 "${LOGFILE}"

echo "[$(date -Is)] START backup" >> "${LOGFILE}"

mysqldump \
  --defaults-file=/root/.my.cnf \
  --single-transaction \
  --quick \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  --set-gtid-purged=OFF \
  --all-databases \
  > "${DUMP_FILE}"

gzip -9 "${DUMP_FILE}"
sha256sum "${ARCHIVE}" > "${ARCHIVE}.sha256"
chmod 600 "${ARCHIVE}" "${ARCHIVE}.sha256"

echo "[$(date -Is)] DONE backup -> ${ARCHIVE}" >> "${LOGFILE}"

Make it executable:

chmod +x /usr/local/bin/backup_mysql.sh

Cronjob #1 Daily backup at 02:30 ✅

Edit crontab:

crontab -e

Add:

MAILTO=admin@example.com
30 2 * * * nice -n 10 ionice -c2 -n7 /usr/local/bin/backup_mysql.sh >> /var/log/mysql_backup.log 2>&1

• nice and ionice lower CPU/IO priority.
• Logs go to /var/log/mysql_backup.log.
• Cron sends email if MAILTO is defined.

Cronjob #2 Cleanup at 03:00 (files older than 7 days) ✅

0 3 * * * find /var/backups/mysql -type f -name '*.gz' -mtime +7 -print -delete >> /var/log/mysql_backup.log 2>&1

Or include checksum files too:

0 3 * * * find /var/backups/mysql -type f \( -name '*.gz' -o -name '*.sha256' \) -mtime +7 -print -delete >> /var/log/mysql_backup.log 2>&1

Verification

1. Run the script manually: /usr/local/bin/backup_mysql.sh
2. Check backup directory.
3. Inspect logs: tail -n 100 /var/log/mysql_backup.log

Restore test

cd /var/backups/mysql/$(hostname -f)
sha256sum -c mysql-YYYY-MM-DD_HH-MM-SS.sql.gz.sha256
gunzip mysql-YYYY-MM-DD_HH-MM-SS.sql.gz
mysql --defaults-file=/root/.my.cnf < mysql-YYYY-MM-DD_HH-MM-SS.sql

Always test restores regularly.

Variations

• Exclude large tables: use --ignore-table=db.table.
• Per-database dumps: loop through SHOW DATABASES.
• WP-CLI export: wp db export.
• Docker: run docker exec mysql_container mysqldump ….
• Encryption: use GPG for off-site backups.
• Off-site storage: upload with aws s3 cp or rclone.

Security best practices

• Permissions: 700 on dirs, 600 on backup files.
• Only root can access .my.cnf.
• Monitor logs and cron mail.
• Run at low-traffic hours.
• Verify checksums before restores.
• Test restore monthly.

Conclusion

With these two cronjobs a daily backup at 02:30 and an automatic cleanup at 03:00 your MySQL databases are safe, lean, and easy to restore. Add checksum validation, off-site replication, and encryption, and you have a truly production-grade backup strategy. Remember: a backup is only as good as your last successful restore test.

🔔 For more tutorials like this, consider subscribing to our blog.
📩 Do you have questions or suggestions? Leave a comment or contact us!

🏷️ Tags: mysql backup, cronjob linux, mysqldump, cleanup cron, database restore, wordpress backup, mariadb security, database automation, server admin, aws s3 backup

📢 Hashtags: #MySQL, #Backup, #Cron, #Database, #Linux, #ServerAdmin, #WordPress, #MariaDB, #SysAdmin, #DevOps