Directory Layout

Start by building a dedicated structure for storing scripts, dump files, and logs:

mkdir -p /mysql/backup/{scripts,files,logs}

Full Backup Script

Create the executable that handles database dumps:

vi /mysql/backup/scripts/backup_full.sh

Paste the logic below. It connects to MySQL, discovers databases, and writes comrpessed SQL exports while skipping system schemas:

#!/bin/bash

STORAGE_ROOT="/mysql/backup"
ARCHIVE_DIR="$STORAGE_ROOT/files"
CURRENT_TS=$(date +%Y%m%d)

DB_LIST=$(mysql -uroot -p'123456' -e "show databases" | grep -Ev "Database|sys|information_schema|performance_schema|mysql")

for DB_NAME in $DB_LIST; do
    echo "[$(date)] Starting dump for $DB_NAME"
    mysqldump -uroot -p'123456' \
        --default-character-set=utf8 \
        --quick \
        --lock-all-tables \
        --flush-logs \
        --events \
        --routines \
        --triggers \
        --databases "$DB_NAME" | gzip > "$ARCHIVE_DIR/${DB_NAME}_${CURRENT_TS}.sql.gz"
    echo "[$(date)] Finished $DB_NAME"
done

Make it executable and run a manual test:

chmod +x /mysql/backup/scripts/backup_full.sh
sh /mysql/backup/scripts/backup_full.sh
ls -lh /mysql/backup/files

To inspect a compressed archive, decompress it first:

gunzip /mysql/backup/files/example_today.sql.gz

Scheduling Periodic Runs

Install the cron daemon if missing:

yum install -y crontab

Register the backup job to execute at 3:00 AM daily:

crontab -e
# Inside the editor add:
0 3 * * * /bin/bash /mysql/backup/scripts/backup_full.sh

Retiring Outdated Backups

Older archives can accumulate quickly. A cleanup script removes files older than seven days:

vi /mysql/backup/scripts/backup_full_clean.sh

#!/bin/bash
find /mysql/backup/files -type f -name "*.gz" -mtime +7 -delete

Schedule the cleaner at 1:00 AM:

crontab -e
# Add the line:
0 1 * * * /bin/bash /mysql/backup/scripts/backup_full_clean.sh

Verify both crontab entries are active with crontab -l.