Comprehensive Guide to Automated MySQL Backup and Restore with Docker

As a DBA, an unexpected backup failure can cause a business outage in seconds; this guide shows how to ensure backup scripts run automatically, detect issues promptly, and allow recovery drills.

1. Environment Overview and Component Relationships

Before operating, review the servers, Docker container names, MySQL roles, responsibilities, and where commands should be executed. The master container mysql-master handles writes and binlog generation, while mysql-node1 and mysql-node2 are read‑only replicas.

2. Backup Strategy & Directory Planning

Store all scripts and backup files in a unified directory to avoid accidental deletion. Example layout:

<code>/home/dba/backup-scripts   # scripts and logs
/home/dba/mysql-backups      # actual backup data</code>

Create sub‑directories for daily, weekly, and monthly backups, e.g., /home/dba/mysql-backups/daily/2025-06-10 .

2.1 Execution Scenarios and Commands

On the master host, create the directories and verify permissions:

<code>mkdir -p /home/dba/backup-scripts
mkdir -p /home/dba/mysql-backups/{daily,weekly,monthly}
ls -R /home/dba</code>

If mkdir is missing, install coreutils with sudo yum install -y coreutils . Ensure the directories are owned by dba:dba and writable.

3. Writing the Automated Backup Script

Create backup_mysql_automation.sh under /home/dba/backup-scripts/ and make it executable.

<code>#!/bin/bash
# ===========================================================================
# backup_mysql_automation.sh – MySQL automated backup script
# Environment: AlmaLinux 8.x + Docker + MySQL (master container: mysql-master)
# Goal: Daily logical backup at 02:00, retain 7 days, store by date
# ===========================================================================

# ---- Variables ----
CONTAINER_NAME="mysql-master"
BACKUP_ROOT="/home/dba/mysql-backups"
LOG_DIR="/home/dba/backup-scripts/logs"
RETENTION_DAYS=7
DATE=$(date +"%F")
TIME=$(date +"%H%M")
TODAY_DIR="${BACKUP_ROOT}/daily/${DATE}"
BACKUP_FILE="${TODAY_DIR}/mysql_master_${DATE}_${TIME}.sql.gz"
LOG_FILE="${LOG_DIR}/backup_${DATE}.log"

# ---- Command Checks ----
for cmd in docker gzip tar; do
  command -v $cmd >/dev/null 2>&1 || {
    echo "[ERROR] Command not found: $cmd" | tee -a "$LOG_FILE"
    echo "[INFO] Install with: sudo yum install -y $cmd" | tee -a "$LOG_FILE"
    exit 1
  }
 done

# ---- Create Directories ----
mkdir -p "$TODAY_DIR" || { echo "[ERROR] Failed to create $TODAY_DIR" | tee -a "$LOG_FILE"; exit 1; }
mkdir -p "$LOG_DIR"   || { echo "[ERROR] Failed to create $LOG_DIR"   | tee -a "$LOG_FILE"; exit 1; }

# ---- Start Backup ----
echo "[$(date +"%F %T")] Starting backup of MySQL master ($CONTAINER_NAME)" | tee -a "$LOG_FILE"

docker exec $CONTAINER_NAME sh -c "mysqldump -uroot -p'YOUR_PASSWORD' --all-databases --single-transaction --quick" \
  | gzip > "$BACKUP_FILE"

if [ $? -eq 0 ]; then
  echo "[$(date +"%F %T")] Backup succeeded: $BACKUP_FILE" | tee -a "$LOG_FILE"
else
  echo "[$(date +"%F %T")] Backup failed" | tee -a "$LOG_FILE"
  exit 1
fi

# ---- Cleanup Old Backups ----
echo "[$(date +"%F %T")] Cleaning backups older than $RETENTION_DAYS days" | tee -a "$LOG_FILE"
find "$BACKUP_ROOT/daily" -maxdepth 1 -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \; -print | tee -a "$LOG_FILE"

echo "[$(date +"%F %T")] Cleanup completed" | tee -a "$LOG_FILE"

echo "[$(date +"%F %T")] MySQL backup script finished" | tee -a "$LOG_FILE"
exit 0</code>

All commands are annotated for beginners. The script checks required tools, creates date‑based directories, runs mysqldump inside the Docker container, compresses the output, logs progress, and removes backups older than the retention period.

4. Scheduling with Cron

Ensure crond (or cronie ) is installed and running:

<code>systemctl status crond
sudo yum install -y cronie
sudo systemctl start crond
sudo systemctl enable crond</code>

Edit the crontab for the DBA user and add the following line to run the script daily at 02:00:

<code>0 2 * * * /home/dba/backup-scripts/backup_mysql_automation.sh >> /home/dba/backup-scripts/logs/cron_backup.log 2>&1</code>

5. Backup Recovery Drill

Test restoration by creating a temporary MySQL container, copying a recent backup into it, decompressing, and importing:

<code># Start test container
docker run -d --name mysql-test -e MYSQL_ROOT_PASSWORD=testpwd -p 3307:3306 mysql:8.0
sleep 10
# Copy backup to host directory
mkdir -p /home/dba/restore-test
cp /home/dba/mysql-backups/daily/2025-06-10/mysql_master_2025-06-10_0200.sql.gz /home/dba/restore-test/
# Copy into container
docker cp /home/dba/restore-test/mysql_master_2025-06-10_0200.sql.gz mysql-test:/tmp/restore/
# Inside container
docker exec -it mysql-test bash
cd /tmp/restore
gzip -d mysql_master_2025-06-10_0200.sql.gz
mysql -uroot -p'testpwd' < mysql_master_2025-06-10_0200.sql
# Verify data
mysql -uroot -p'testpwd' -e "SHOW DATABASES;"
exit</code>

If the data appears as expected, the backup and restore process is verified.

6. Common Issues & Troubleshooting

"bash: docker: command not found" – Install Docker using the steps in section 4.

"bash: mysqldump: command not found" – Run the dump via docker exec mysql-master mysqldump … or install the MySQL client on the host.

Missing gzip or tar – Install with sudo yum install -y gzip tar .

Container lacks yum – Prefer performing operations on the host; if needed, build a custom Docker image that includes required tools.

Access denied (Error 1045) – Verify the root password in the script and ensure the user has sufficient privileges.

For any other questions, feel free to leave a private message to the author.