如何创建每日自动备份MySQL的Docker容器？已部署后实操求助

Hey there! Let's break this down step by step to get your MySQL database set up and automated nightly backups running smoothly.

Step 1: Create Your Database in the MySQL Container #

First, let's confirm your MySQL container is up and running. Run this command on your host terminal to check:

docker ps

You should see your db_mysql container listed with an "Up" status. If it's stopped, start it with:

docker start db_mysql

There are two easy ways to create your database:

Option 1: Create the Database Directly from the Host #

You can run a MySQL command straight from your host without entering the container. Use this (swap in your actual password and desired database name):

docker exec -it db_mysql mysql -u root -p1234 -e "CREATE DATABASE IF NOT EXISTS mami;"

• -p1234 matches the root password you set for your db_mysql container
• mami is the database name you mentioned earlier — feel free to adjust it if needed

Option 2: Use the Interactive MySQL Shell #

If you prefer working interactively inside the container:

1. Launch the MySQL client inside the container:

docker exec -it db_mysql mysql -u root -p

2. Enter your root password (1234) when prompted.
3. Run the create command in the MySQL shell:

CREATE DATABASE IF NOT EXISTS mami;

4. Verify it was created with:

SHOW DATABASES;

5. Exit the shell with exit;

Step 2: Set Up Automated Nightly Backups #

The simplest approach (without modifying your existing MySQL container) is to use a host-side cron job to run backup commands. Here's how:

1. Prepare a Backup Directory #

First, create a folder on your host to store backups (so they persist even if the container stops):

mkdir -p ~/mysql_backups

You can use any path you want — just remember it for later.

2. Create a Backup Script #

Make a reusable shell script to handle backups:

1. Open a new script file:

nano ~/mysql_backups/mysql_backup.sh

2. Paste this content (adjust the values to match your setup):

#!/bin/bash

# Configuration
CONTAINER_NAME="db_mysql"
MYSQL_USER="root"
MYSQL_PASSWORD="1234"
BACKUP_DIR="$HOME/mysql_backups"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/${CONTAINER_NAME}_backup_$DATE.sql"

# Run the backup
docker exec $CONTAINER_NAME mysqldump -u$MYSQL_USER -p$MYSQL_PASSWORD --all-databases > $BACKUP_FILE

# Compress the backup to save space
gzip $BACKUP_FILE

# Optional: Delete backups older than 7 days to avoid storage bloat
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

3. Save and exit (press Ctrl+O, hit Enter, then Ctrl+X in nano).
4. Make the script executable:

chmod +x ~/mysql_backups/mysql_backup.sh

5. Test it to ensure it works:

~/mysql_backups/mysql_backup.sh

Check your ~/mysql_backups folder — you should see a compressed .sql.gz backup file.

3. Schedule the Script with Cron #

Set the script to run every night (we'll use 2 AM as an example):

1. Open your crontab editor:

crontab -e

2. Add this line at the bottom (adjust the path if you used a different backup folder):

0 2 * * * ~/mysql_backups/mysql_backup.sh >> ~/mysql_backups/backup_logs.log 2>&1

• 0 2 * * * translates to "run at 2:00 AM every day"
• The >> ... 2>&1 part logs all output (successes and errors) to a log file for troubleshooting

3. Save and exit the editor.

Pro Tip #

Always test restoring a backup to make sure it works! Uncompress the backup with gunzip backup_file.sql.gz, then restore it with:

docker exec -i db_mysql mysql -u root -p1234 < backup_file.sql

内容的提问来源于stack exchange，提问作者Liverpool