Aug 31, 2024 4 min read 系统运维

MySQL + S3 备份姿势

环境

对象存储： MinIO
备份工具： Percona XtraBackup/mysqldump/restic

方案1：使用自带xbcloud

实现脚本如下：

/data/backup/mysql_backup.sh

#!/usr/bin/env bash

s3_ep=http://s3.vqiu.local:9000
s3_ak=xx
s3_sk=xx
s3_bk=mysql-backup

xtrabackup --backup --stream=xbstream --extra-lsndir=/tmp --target-dir=/tmp | \
           xbcloud put --storage=s3 \
           --s3-endpoint="${s3_ep}" \
           --s3-access-key="${s3_ak}" \
           --s3-secret-key="${s3_sk}" \
           --s3-bucket="${s3_bk}" \
           --parallel=10 \
           $(date -I)-full_backup

将本机的数据库实例全量备份至对象存储

使用以下方式将数据还原

export AWS_ACCESS_KEY_ID=XX
export AWS_SECRET_ACCESS_KEY=xx
export AWS_DEFAULT_REGION=xx
export AWS_ENDPOINT=http://s3.vqiu.local:9000/

mkdir restore

#!/usr/bin/env bash

#xbcloud get --storage=s3 --s3-bucket=mysql-backup  2024-08-31-full_backup
xbcloud get s3://mysql-backup/2024-08-31-full_backup | xbstream -x -C restore --parallel=8

系统任务定时计划

# cat >~/.config/env/backup-mysql<<EOF
s3_ep=http://s3.vqiu.local:9000
s3_ak=xx
s3_sk=xx
s3_bk=mysql-backup
EOF

# cat >/etc/systemd/system/backup-mysql.service<<EOF
[Unit]
Description=Backup MySQL databases

[Service]
Type=oneshot
EnvironmentFile=%h/.config/env/%p
User=root
ExecStart=/bin/bash -c "xtrabackup --defaults-file=/etc/mysql/my.cnf \
                                    --backup --stream=xbstream  \
                                    --extra-lsndir=/tmp \
                                    --target-dir=/tmp | xbcloud put \
                                    --storage=s3 \
                                    --s3-endpoint=\${s3_ep} \
                                    --s3-access-key=\${s3_ak} \
                                    --s3-secret-key=\${s3_sk} \
                                    --s3-bucket=\${s3_bk} \
                                    --parallel=10 \
                                    \$(date -I)-full_backup"

[Install]
WantedBy=multi-user.target
EOF

# cat > /etc/systemd/system/backup-mysql.timer<<EOF
[Unit]
Description=Backup MySQL databases
[Timer]
OnCalendar=*-*-* 00:00:00
OnCalendar=*-*-* 12:00:00
Persistent=true

[Install]
WantedBy=timers.target
EOF

// OnCalendar=*-*-* 00:00:00 表示每天的 00:00 触发定时器.
// OnCalendar=*-*-* 12:00:00 表示每天的 12:00 触发定时器.
// Persistent=true 确保如果系统在指定时间未运行，下次启动时会补上这次运行.
// WantedBy=timers.target 表示该定时器应该在 timers.target 下运行.

缺点：

文件比较散，以目录的形式存储，个人还是喜欢用流式
没有原生的周期清理策略（只能通过Bucket策略实现）

方案2： mysqldump + restic

实现脚本

#!/bin/bash

cd `dirname $0`

export MYSQL_ADDR=127.0.0.1
export MYSQL_USER=root
export MYSQL_PASS=xxx

export TAG=mysql

for DB in $(mysql -u$MYSQL_ADDR -BNe 'show databases' | grep -Ev --line-regexp 'mysql|information_schema|performance_schema|sys')
do
	mysqldump -u$MYSQL_ADDR --skip-dump-date --force $DB | \
		gzip --rsyncable | \
		restic backup \
			--stdin --stdin-filename mysql/$DB.sql.gz \
			--tag "$TAG" \
			--tag "$DB"
done

或者只备份单库

mysqldump --databases DB_NAME -u database_user -p | restic backup --stdin --stdin-filename database_dump.sql

备份全库

database_user -p --all-databases | restic backup --stdin --stdin-filename all_databases.sql

方案3： XtraBackup + restic[推荐]

备份脚本

#!/usr/bin/env bash

source  /root/.restic.env

xtrabackup --backup --stream=xbstream --extra-lsndir=/tmp --target-dir=/tmp 2>/data/backup/mysql-backup.log | restic backup --stdin --stdin-filename $(date -I)-full_backup.xb --tag mysql

我更喜欢使用zstd压缩一次再传递给restic

# xtrabackup --backup --stream=xbstream --extra-lsndir=/tmp --target-dir=/tmp 2>/data/backup/mysql-backup.log | zstd -9 -T10 | restic backup --stdin --stdin-filename $(date -I)-full_backup.xb.zst --tag mysql

整合systemd

cat > /etc/systemd/system/backup-mysql.service<<EOF
[Unit]
Description=Backup MySQL databases

[Service]
# 必须为oneshot类型，否则StartPost不好控制
Type=oneshot
User=root
#Environmentfile=%h/.config/env/%p
Environment=AWS_ACCESS_KEY_ID='<AK>'
Environment=AWS_SECRET_ACCESS_KEY='<SK>'
Environment=RESTIC_REPOSITORY='s3:http://172.20.6.245:9000/backup'
Environment=RESTIC_PASSWORD='<xx>'
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 cqyq-mysql
ExecStartPost=-/usr/bin/restic forget --keep-last 15 --prune

[Install]
WantedBy=multi-user.target
EOF

定义备份计划

# cat > /etc/systemd/system/backup-mysql.timer<<EOF
[Unit]
Description=Backup MySQL databases
[Timer]
OnCalendar=*-*-* 00:00:00

[Install]
WantedBy=timers.target
EOF

查看状态

# restic snapshots 
repository 278bf6bd opened (version 2, compression level auto)
ID        Time                 Host        Tags        Paths                                 Size
--------------------------------------------------------------------------------------------------------
487fe997  2024-08-31 14:13:43  prod-mysql  mysql    /full_backup.xb  162.020 MiB
--------------------------------------------------------------------------------------------------------
3 snapshots

恢复步骤

# restic restore 487fe997 --target restore_dir

// 格式还原
# xbstream -x < full_backup.xb -C /data/backup/xbstream
# xtrabackup --parallel=4 --decompress --target-dir=/data/backup/xbstream

// 恢复
# xtrabackup --prepare --target-dir=/data/backup/xbstream
# xtrabackup --copy-back --target-dir=/data/backup/xbstream --datadir=/data/mysql/data