Automate Nightly MySQL/MariaDB Backup with mysqldump

Schedule

Setup

1. 1

   Create a dedicated backup directory

   Choose a location outside the web root, for example /var/backups/mysql, and make sure it is owned by the user that will run the script. Run: mkdir -p /var/backups/mysql && chmod 700 /var/backups/mysql

2. 2

   Store credentials securely in a MySQL options file

   Create /root/.my.cnf (or ~/.my.cnf for the cron user) with contents: [mysqldump] user=backup_user password=yourpassword Then restrict permissions: chmod 600 ~/.my.cnf. This keeps the password out of the script and out of process listings.

3. 3

   Create the backup script

   Save the script below to /usr/local/bin/mysql-nightly-backup.sh and make it executable: chmod +x /usr/local/bin/mysql-nightly-backup.sh. Edit the variables at the top to match your database name, backup directory, retention policy, and CronJobPro heartbeat token.

4. 4

   Add the cron entry

   Run crontab -e (as root or the designated backup user) and add the line that matches the schedule: 0 2 * * * /usr/local/bin/mysql-nightly-backup.sh >> /var/log/mysql-backup.log 2>&1

5. 5

   Create a CronJobPro heartbeat monitor and test

   In CronJobPro, create a new Heartbeat monitor, set the period to 24 hours, and set a grace period of 30 minutes. Copy the generated ping URL into your script (replacing the placeholder token). Do a manual test run with bash /usr/local/bin/mysql-nightly-backup.sh and confirm the heartbeat registers as received in the CronJobPro dashboard.

The script

bash

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

# --- Configuration ---
DB_NAME="your_database"          # or "--all-databases" for all DBs
BACKUP_DIR="/var/backups/mysql"
RETAIN_DAYS=7                    # delete dumps older than this
HEARTBEAT_TOKEN="your_token_here" # from CronJobPro heartbeat monitor

# --- Derived values ---
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.sql.gz"
PING_URL="https://cronjobpro.com/ping/${HEARTBEAT_TOKEN}"

# Signal failure to CronJobPro if the script exits with an error
trap 'curl -fsS --retry 3 "${PING_URL}/fail" > /dev/null 2>&1 || true' ERR

echo "[$(date)] Starting backup of '${DB_NAME}'"

# --- Run backup ---
# Credentials are read from ~/.my.cnf — no password in the command line
mysqldump \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  "${DB_NAME}" \
  | gzip -9 > "${FILE}"

echo "[$(date)] Backup written to ${FILE} ($(du -sh "${FILE}" | cut -f1))"

# --- Prune old backups ---
find "${BACKUP_DIR}" -name "${DB_NAME}_*.sql.gz" -mtime +"${RETAIN_DAYS}" -delete
echo "[$(date)] Old backups pruned (retention: ${RETAIN_DAYS} days)"

# --- Heartbeat ping on success ---
curl -fsS --retry 3 "${PING_URL}" > /dev/null 2>&1
echo "[$(date)] Heartbeat ping sent"

Frequently asked questions

More recipes