Docker部署MySQL5.7或者MySQL8后，开启数据库备份（全量备份、增量备份），数据库恢复到指定日期。

启用 binlog

MySQL要启用增量备份，必须开启binlog。检查是否数据库是否开启binlog

docker ps

查看mysql运行情况

进入容器内部查看

docker exec -it mysql8 mysql -uroot -p //进入mysql8查看
docker exec -it mysql57 mysql -uroot -p //进入mysql57查看

| log_bin | ON |
表示已经开启。

如果未开启binlog，如果你挂载了mysql配置文件，只需要修改它对应内容如下：

挂载my.cnf

[mysqld]
log-bin=mysql-bin
server-id=1 //数据库有几个这个id就往后便宜，不要指定一个id
binlog_format=ROW
expire_logs_days=7

未挂载配置文件，需要重新挂载，查看容器是否挂载了宿主机目录

docker inspect mysql8 --format='{{range .Mounts}}{{println .Source "->" .Destination}}{{end}}' //容器mysql8
docker inspect mysql57 --format='{{range .Mounts}}{{println .Source "->" .Destination}}{{end}}' //容器mysql57

提示如下

/var/lib/docker/volumes/18866e7c265e4d2dd61ff3b80de1d54dffd86f98e1f29f316908c5a001926e31/_data -> /var/lib/mysql
说明没有挂载文件，使用了 Docker 的匿名 volume，需要重新挂载。

创建挂载数据目录,并复制原数据

mkdir -p /home/mydata/mysql8/data
mkdir -p /home/mydata/mysql57/data

# mysql8
docker run --rm -v 5e7fa8659f50f29cf1c647ecabf1e42ff2cc96f73b3c715eaea0cfa40530d128:/from -v /home/mydata/mysql8/data:/to alpine sh -c "cp -a /from/* /to/"

# mysql57
docker run --rm -v 18866e7c265e4d2dd61ff3b80de1d54dffd86f98e1f29f316908c5a001926e31:/from -v /home/mydata/mysql57/data:/to alpine sh -c "cp -a /from/* /to/"

完成并检查对应文件夹数据是否拷贝成功
并删除原容器

docker stop mysql8 mysql57
docker rm mysql8 mysql57

重新创建容器，挂载目录

#mysql8
docker run -d --name mysql8   -e MYSQL_ROOT_PASSWORD=123456   -v /home/mydata/mysql8/data:/var/lib/mysql   -v /home/mydata/mysql8/my.cnf:/etc/my.cnf   -p 3308:3306   swr.cn-east-3.myhuaweicloud.com/library/mysql:8.0

# mysql57
docker run -d --name mysql57   -e MYSQL_ROOT_PASSWORD=123456   -v /home/mydata/mysql57/data:/var/lib/mysql   -v /home/mydata/mysql57/my.cnf:/etc/my.cnf   -p 3307:3306   swr.cn-east-3.myhuaweicloud.com/library/mysql:5.7

#确认挂载生效
docker inspect mysql8 --format='{{range .Mounts}}{{println .Source "->" .Destination}}{{end}}'
docker inspect mysql57 --format='{{range .Mounts}}{{println .Source "->" .Destination}}{{end}}'

挂载生效后的目录

数据库全量备份full_backup.sh

#!/bin/bash

TODAY=$(date +%F)

# 通用配置
BACKUP_ROOT="/home/mydata/mysql/backup/full"
MYSQL_USER="root"
MYSQL_PASS="123456"

# mysql8 备份
BACKUP_DIR8="$BACKUP_ROOT/mysql8/$TODAY"
mkdir -p $BACKUP_DIR8
docker exec mysql8 mysqldump -u$MYSQL_USER -p$MYSQL_PASS --all-databases > $BACKUP_DIR8/all.sql

# mysql57 备份
BACKUP_DIR57="$BACKUP_ROOT/mysql57/$TODAY"
mkdir -p $BACKUP_DIR57
docker exec mysql57 mysqldump -u$MYSQL_USER -p$MYSQL_PASS --all-databases > $BACKUP_DIR57/all.sql

# 清理3天前备份
find $BACKUP_ROOT/mysql8/ -type d -mtime +3 -exec rm -rf {} \;
find $BACKUP_ROOT/mysql57/ -type d -mtime +3 -exec rm -rf {} \;

数据库增量备份increment_backup.sh

#!/bin/bash

# 时间戳
NOW=$(date +"%F_%H-%M")

# 通用配置
SOURCE8="/home/mydata/mysql8/data"
SOURCE57="/home/mydata/mysql57/data"

DEST8="/home/mydata/mysql/backup/increment/mysql8"
DEST57="/home/mydata/mysql/backup/increment/mysql57"

mkdir -p $DEST8
mkdir -p $DEST57

# 增量备份：复制 binlog（避免重复用 cp -an）
cp -an $SOURCE8/mysql-bin.* $DEST8/
cp -an $SOURCE57/mysql-bin.* $DEST57/

# 删除3天前的 binlog 备份
find $DEST8 -type f -mtime +3 -name "mysql-bin.*" -exec rm -f {} \;
find $DEST57 -type f -mtime +3 -name "mysql-bin.*" -exec rm -f {} \;

数据恢复

mysql8恢复

#!/bin/bash

read -p "请输入恢复时间（格式如 2025-06-14 15:30:00，直接回车为恢复全部）： " STOP_TIME

MYSQL_CONTAINER="mysql8"
MYSQL_USER="root"
MYSQL_PASS="123456"
FULL_SQL="/home/mydata/mysql/backup/full/mysql8/2025-06-14/all.sql"
BINLOG_DIR="/home/mydata/mysql/backup/increment/mysql8"

echo "开始恢复 mysql8..."

# 还原全量备份
echo "还原全量备份：$FULL_SQL"
docker exec -i $MYSQL_CONTAINER mysql -u$MYSQL_USER -p$MYSQL_PASS < $FULL_SQL

# 执行增量恢复
for binlog in $(find $BINLOG_DIR -type f -name 'mysql-bin.[0-9]*' | sort); do
  echo "🔹 执行 binlog 文件：$binlog"

  if [ -n "$STOP_TIME" ]; then
    docker exec $MYSQL_CONTAINER mysqlbinlog --stop-datetime="$STOP_TIME" "/var/lib/mysql/$(basename $binlog)" | \
      docker exec -i $MYSQL_CONTAINER mysql -u$MYSQL_USER -p$MYSQL_PASS
    echo "已恢复至指定时间：$STOP_TIME"
    break
  else
    docker exec $MYSQL_CONTAINER mysqlbinlog "/var/lib/mysql/$(basename $binlog)" | \
      docker exec -i $MYSQL_CONTAINER mysql -u$MYSQL_USER -p$MYSQL_PASS
  fi
done

echo "mysql8 数据恢复完成！"

mysql57恢复

#!/bin/bash

read -p "请输入恢复时间（格式如 2025-06-14 15:30:00，直接回车为恢复全部）： " STOP_TIME

MYSQL_CONTAINER="mysql57"
MYSQL_USER="root"
MYSQL_PASS="123456"
FULL_SQL="/home/mydata/mysql/backup/full/mysql57/2025-06-14/all.sql"
BINLOG_DIR="/home/mydata/mysql/backup/increment/mysql57"

echo "开始恢复 mysql57..."

# 还原全量备份
echo "还原全量备份：$FULL_SQL"
docker exec -i $MYSQL_CONTAINER mysql -u$MYSQL_USER -p$MYSQL_PASS < $FULL_SQL

# 执行增量恢复
for binlog in $(find $BINLOG_DIR -type f -name 'mysql-bin.[0-9]*' | sort); do
  echo "🔹 执行 binlog 文件：$binlog"

  if [ -n "$STOP_TIME" ]; then
    docker exec $MYSQL_CONTAINER mysqlbinlog --stop-datetime="$STOP_TIME" "/var/lib/mysql/$(basename $binlog)" | \
      docker exec -i $MYSQL_CONTAINER mysql -u$MYSQL_USER -p$MYSQL_PASS
    echo "已恢复至指定时间：$STOP_TIME"
    break
  else
    docker exec $MYSQL_CONTAINER mysqlbinlog "/var/lib/mysql/$(basename $binlog)" | \
      docker exec -i $MYSQL_CONTAINER mysql -u$MYSQL_USER -p$MYSQL_PASS
  fi
done

echo "mysql57 数据恢复完成！"