MySQL 数据备份恢复实例

树辉 -

涉及工具

  • MinIO
  • Restic
  • xtrabackup
  • zstd

实现备忘

MySQL备份

  • /etc/systemd/system/mysql-backup.service
[Unit]
Description=Backup MySQL databases

[Service]
# 必须为oneshot类型，否则StartPost不好控制
Type=oneshot
User=root
#Environmentfile=%h/.config/env/%p
Environment=AWS_ACCESS_KEY_ID='ACCESS_KEY'
Environment=AWS_SECRET_ACCESS_KEY='SECRET_KEY'
Environment=RESTIC_REPOSITORY='s3:http://s3.vqiu.local:9000/backup'
Environment=RESTIC_PASSWORD='rL1qk1kaV9cDOliA'
Environment=BACKUP_DIR='/data/backup/mysql'
Environment=ZSTD_NBTHREADS=4
Environment=ZSTD_CLEVEL=10
ExecStartPre=install -d $BACKUP_DIR
ExecStart=/bin/bash -c "/usr/local/bin/xtrabackup --defaults-file=/etc/mysql/my.cnf --backup --stream=xbstream | zstd > ${BACKUP_DIR}/full_backup.xb.zst"
ExecStartPost=/usr/bin/restic backup ${BACKUP_DIR}/full_backup.xb.zst --verbose --no-lock=true --tag prod-mysql
ExecStartPost=-/usr/bin/restic forget --keep-last 15 --prune

[Install]
WantedBy=multi-user.target

  • /etc/systemd/system/mysql-backup.timer
[Unit]
Description=Backup MySQL databases
[Timer]
OnCalendar=*-*-* 00:00:00

[Install]
WantedBy=timers.target

MySQL恢复

  1. 使用restic 查看备份数据
# restic snapshots --path /data/backup/mysql/full_backup.xb.zst --tag cqyq-mysql
ID        Time                 Host               Tags        Paths                                  Size
---------------------------------------------------------------------------------------------------------------
e2401a87  2025-06-10 00:00:16  Prod-MySQL-Server  cqyq-mysql  /data/backup/mysql/full_backup.xb.zst  27.968 MiB
baba4ba5  2025-06-11 00:00:30  Prod-MySQL-Server  cqyq-mysql  /data/backup/mysql/full_backup.xb.zst  28.022 MiB
...<省略若干行>...
aafcb6cf  2025-06-23 00:00:09  Prod-MySQL-Server  cqyq-mysql  /data/backup/mysql/full_backup.xb.zst  28.410 MiB
a4bc8007  2025-06-24 00:00:30  Prod-MySQL-Server  cqyq-mysql  /data/backup/mysql/full_backup.xb.zst  28.463 MiB
---------------------------------------------------------------------------------------------------------------
15 snapshots

2. 使用restic 下载文件到中转路径

# restic restore a4bc8007 --target /tmp/restore
repository 7d98fa14 opened (version 2, compression level auto)
found 3 old cache directories in /root/.cache/restic, run `restic cache --cleanup` to remove them
[0:00] 100.00%  3 / 3 index files loaded
restoring snapshot a4bc8007 of [/data/backup/mysql/full_backup.xb.zst] at 2025-06-24 00:00:30.980629487 +0800 CST by root@Prod-MySQL-Server to /tmp/restore
Summary: Restored 4 files/dirs (28.463 MiB) in 0:00

3. 使用zstd解压

# zstd -d full_backup.xb.zst

3. 还原xb文件至/tmp/mysql_restore目录

# install -d /tmp/mysql_restore
# xbstream -x -C /tmp/mysql_restore < full_backup.xb

4. MySQL日志恢复

# xtrabackup --prepare --target-dir=/tmp/mysql_restore
# xtrabackup --move-back --target-dir=/tmp/mysql_restore --datadir=/data/mysql/data

常规运维

查看备份

# restic snapshots

清理所有备份数据

# restic snapshots  |grep prod-mysql | awk '{print $1}' | xargs restic forget --prune