docker备份MySQL文件到容器外

定时任务定时备份MySQL数据库

1
2
3
crontab -e
0 1 * * * /app/km_mysql_backup/km_mysql_backup.sh

脚本名称km_mysql_backup.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
#!/bin/bash

# 容器名称
CONTAINER_NAME="mysql-km-5.7.43"

# MySQL数据库连接信息
DB_USER=""
DB_PASSWORD=""
DB_NAME="km3_db"
CHARACTER_SET="utf8mb4"  # 设置字符集

# 外部备份目录
EXTERNAL_BACKUP_DIR="/app/km_mysql_backup"

# 检查外部备份目录是否存在，不存在则创建
if [ ! -d "$EXTERNAL_BACKUP_DIR" ]; then
    mkdir -p $EXTERNAL_BACKUP_DIR
fi

# 获取当前日期
CURRENT_DATE=$(date +"%Y-%m-%d")
CURRENT_DATE_FILE="km3_db_$CURRENT_DATE.sql"
CURRENT_DATE_ZIP="$EXTERNAL_BACKUP_DIR/km3_db_$CURRENT_DATE.zip"

# 容器内备份目录
CONTAINER_BACKUP_DIR="/tmp"
CONTAINER_BACKUP_PATH="$CONTAINER_BACKUP_DIR/$CURRENT_DATE_FILE"

# 压缩前清理一天之前的所有容器外文件的SQL文件
find $EXTERNAL_BACKUP_DIR -name "*.sql" -type f -mtime +1 -delete

# 获取昨天的日期
YESTERDAY=$(date -d "yesterday" "+%Y-%m-%d")

# 压缩昨天的SQL文件
YESTERDAY_FILE="km3_db_$YESTERDAY.sql"
YESTERDAY_ZIP="$EXTERNAL_BACKUP_DIR/km3_db_$YESTERDAY.zip"
if [ -f "$EXTERNAL_BACKUP_DIR/$YESTERDAY_FILE" ]; then
    zip -j $YESTERDAY_ZIP $EXTERNAL_BACKUP_DIR/$YESTERDAY_FILE
    echo "昨天的备份文件已成功压缩到 $YESTERDAY_ZIP"
else
    echo "昨天的备份文件未找到."
fi

# 清理容器内三天以上的SQL文件
docker exec -i $CONTAINER_NAME sh -c "find $CONTAINER_BACKUP_DIR -name 'km3_db_*.sql' -type f -mtime +3 -delete"
echo "删除容器内三天前的备份文件."

# 删除昨天以及昨天之前的.sql文件
find $EXTERNAL_BACKUP_DIR -name "km3_db_*.sql" -type f -mtime +1 -delete
echo "昨天以及昨天之前的.sql文件已删除."

# 删除外部目录中15天前的备份文件
find $EXTERNAL_BACKUP_DIR -name "km3_db_*.zip" -type f -mtime +15 -delete
echo "删除外部目录中15天前的备份文件."

# 备份数据库
docker exec -i $CONTAINER_NAME sh -c "mysqldump -u$DB_USER -p'$DB_PASSWORD' --default-character-set=$CHARACTER_SET --databases $DB_NAME" > $CONTAINER_BACKUP_PATH

# 将备份文件复制到外部备份目录
if [ -f $CONTAINER_BACKUP_PATH ]; then
    cp $CONTAINER_BACKUP_PATH $EXTERNAL_BACKUP_DIR/$CURRENT_DATE_FILE
    echo "数据库备份成功，并已存储到 $EXTERNAL_BACKUP_DIR/$CURRENT_DATE_FILE"
else
    echo "数据库备份失败"
    exit 1
fi