Last active
August 12, 2025 19:33
-
-
Save royvandam/7cc0af5d972c20179a8bafce50eb897a to your computer and use it in GitHub Desktop.
Daily mysql database backup with rotation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 0 3 * * * /usr/local/bin/mysql-backup |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/bash | |
| set -euo pipefail | |
| umask 077 | |
| # --- Default config --- | |
| CRED_FILE="/etc/default/mysql-backup.auth" # must exist; INI with [client] user/password/host | |
| BACKUP_DIR="/var/lib/mysql-backups" | |
| KEEP_DAYS=7 | |
| KEEP_WEEKS=4 | |
| KEEP_MONTHS=6 | |
| # If you want specific DBs: set DBS="db1 db2"; leave empty to auto-detect (excluding system DBs) | |
| DBS="" | |
| # --- Load external config if available --- | |
| CONF_FILE="/etc/default/mysql-backup.conf" | |
| if [ -f "$CONF_FILE" ]; then | |
| # shellcheck source=/etc/default/mysql-backup.conf | |
| source "$CONF_FILE" | |
| fi | |
| # --- Checks --- | |
| if [ ! -f "$CRED_FILE" ]; then | |
| echo "ERROR: Credentials file $CRED_FILE not found" >&2 | |
| exit 1 | |
| fi | |
| # Check file ownership and permissions | |
| OWNER_GROUP="$(stat -c '%U:%G' "$CRED_FILE")" | |
| PERMS="$(stat -c '%a' "$CRED_FILE")" | |
| if [ "$OWNER_GROUP" != "root:root" ] || [ "$PERMS" != "600" ]; then | |
| echo "ERROR: $CRED_FILE must be owned by root:root and have permissions 600." >&2 | |
| echo "Current: owner=$OWNER_GROUP perms=$PERMS" >&2 | |
| exit 1 | |
| fi | |
| # --- Commands with creds --- | |
| MYSQL="mysql --defaults-extra-file=$CRED_FILE --batch --skip-column-names" | |
| MYSQLDUMP="mysqldump --defaults-extra-file=$CRED_FILE" | |
| # --- Determine DB list --- | |
| if [ -z "${DBS:-}" ]; then | |
| mapfile -t DB_LIST < <($MYSQL -e "SHOW DATABASES" | grep -Ev '^(information_schema|performance_schema)$') | |
| else | |
| read -r -a DB_LIST <<<"$DBS" | |
| fi | |
| if [ "${#DB_LIST[@]}" -eq 0 ]; then | |
| echo "ERROR: No databases to back up" >&2 | |
| exit 1 | |
| fi | |
| # --- Prepare folders --- | |
| DATE="$(date +%Y-%m-%d)" | |
| RUN_DIR="$BACKUP_DIR/daily/$DATE" | |
| mkdir -p "$RUN_DIR" "$BACKUP_DIR/weekly" "$BACKUP_DIR/monthly" | |
| # If today's directory exists, warn and recreate | |
| if [ -d "$RUN_DIR" ]; then | |
| echo "WARNING: $RUN_DIR already exists; removing and recreating to ensure a fresh backup" >&2 | |
| rm -rf "$RUN_DIR" | |
| fi | |
| mkdir -p "$RUN_DIR" | |
| # --- Build common dump flags --- | |
| DUMP_FLAGS=(--single-transaction --quick --routines --events --triggers --hex-blob --no-tablespaces) | |
| # --- Per-DB dumps --- | |
| for db in "${DB_LIST[@]}"; do | |
| out="$RUN_DIR/$db.sql.gz" | |
| $MYSQLDUMP "${DUMP_FLAGS[@]}" --databases "$db" | gzip > "$out" | |
| done | |
| # --- Rotation (remove old dated folders) --- | |
| find "$BACKUP_DIR/daily" -mindepth 1 -maxdepth 1 -type d -mtime +$((KEEP_DAYS-1)) -exec rm -rf {} + | |
| find "$BACKUP_DIR/weekly" -mindepth 1 -maxdepth 1 -type d -mtime +$((KEEP_WEEKS*7-1)) -exec rm -rf {} + | |
| find "$BACKUP_DIR/monthly" -mindepth 1 -maxdepth 1 -type d -mtime +$((KEEP_MONTHS*30-1)) -exec rm -rf {} + | |
| # --- Promote weekly (Sunday) --- | |
| if [ "$(date +%u)" -eq 7 ]; then | |
| cp -a "$RUN_DIR" "$BACKUP_DIR/weekly/$DATE" | |
| fi | |
| # --- Promote monthly (1st) --- | |
| if [ "$(date +%d)" = "01" ]; then | |
| cp -a "$RUN_DIR" "$BACKUP_DIR/monthly/$DATE" | |
| fi |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment