kamidox.com

MySQL 数据库备份恢复

任务目标¶

使用腾讯云自动备份的 .xb 文件，在自建的主机上恢复出数据库。参考文档 https://cloud.tencent.com/document/product/236/33363。

安装 xtrabackup¶

安装 xtrabackup 和 qpress。其中 qpress 是 xtrabackup 用来压缩和解压的工具。

$ wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
$ sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
$ percona-release enable-only tools release
$ sudo apt install percona-xtrabackup-24
$ sudo apt install qpress

上述脚本先安装 percona 工具，以便修改 ubuntu 的软件源，然后再通过 apt 安装 percona-xtrabackup。需要注意，因为我们用的是 MySQL 5.6 的系统，所以安装 xtrabackup 2.4。如果用的是 MySQL 8.0，则使用配套的 xtrabackup 8.0 版本。

直接从腾讯云上下载安装 qpress 速度会更快。

$ wget -d --user-agent="Mozilla/5.0 (Windows NT x.y; rv:10.0) Gecko/20100101 Firefox/10.0" https://docs-tencentdb-1256569818.cos.ap-guangzhou.myqcloud.com/qpress-11-linux-x64.tar
$ sudo tar -xf qpress-11-linux-x64.tar -C /usr/local/bin

恢复数据¶

假设下载下来的数据库备份文件为 ~/mysql/weapp.xb，恢复出来的数据文件放在 ~/msyql/data 目录下：

# 修改用户
$ rm -rf ~/mysql/data
$ mkdir -p ~/mysql/data
$ xbstream -x -C ~/mysql/data < ~/mysql/weapp.xb
$ xtrabackup --decompress --remove-original --target-dir=~/mysql/data
$ xtrabackup --prepare --target-dir=~/mysql/data

运行 MySQL Docker 验证¶

$ sudo docker pull mysql:5.6
$ sudo docker run -p 3306:3306 --name mysql -v ~/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.6
$ sudo docker container stop mysql
$ sudo docker container start mysql
$ mysql -h 172.17.0.2 -uroot -p123456
mysql> USE information_schema;
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='weapp';
+-----------+
| data      |
+-----------+
| 2974.41MB |
+-----------+
1 row in set (0.00 sec)

其中 172.17.0.2 是 MySQL docker 容器的地址，也可以用 127.0.0.1 来替代。

使用 mysql 访问数据库时，可能出现如下错误：

ERROR 1045 (28000): Access denied for user 'root'@'172.17.0.1' (using password: YES)

解决方法是，进入 mysql docker image，修改用户权限。

$ sudo docker exec -it mysql /bin/bash
root@6df8e760c575:/# mysql -uroot
mysql> use mysql;
mhsql> select user,host from user where user='root';

+------+----------------+
| user | host           |
+------+----------------+
| root | %              |
| root | 127.0.0.1      |
| root | ::1            |
| root | localhost      |
| root | tencent64.site |
+------+----------------+
5 rows in set (0.00 sec)

修改权限，把 tencent64.site 改为 docker 网关 172.17.0.1。把 ::1 改为小程序后端地址 172.17.0.3。

mysql> update user set host='172.17.0.1' where User='root' and host='tencent64.site';
mysql> grant all privileges on *.* to root@'172.17.0.1' identified by '123456' with grant option;
mysql> flush privileges;

mysql> update user set host='172.17.0.3' where User='root' and host='::1';
mysql> grant all privileges on *.* to root@'172.17.0.3' identified by '123456' with grant option;
mysql> flush privileges;

备份数据库¶

自己使用 docker 运行 mysql 数据库时，就需要考虑灾备。我们可以使用 xtrabackup 来自己进行数据库热备份。

首次备份之前，需要创建链接，把 /var/lib/mysql 链接到真实的 mysql 数据库文件位置。

# 此处假设 mysql 数据库放在 /home/ubuntu/mysql/data 目录下需要创建一个到 /var/lib/mysql 的链接，以便 xtrabackup 备份时访问，否则会报错
$ ln -s /home/ubuntu/mysql/data /var/lib/mysql

把保存备份的主机的登录密钥拷贝到应用主机上。

$ scp -i ~/.ssh/kamidox.qcloud.1.pem sfox.studio.9.aws.pem ubuntu@weapp.kamidox.com:/home/ubuntu/.ssh

然后执行备份，并使用 rsync 把备份文件拷贝到备份主机。

#!/bin/bash
# 调用示例 backup-database-full.sh base
if [ ! $1 ]; then
        BASE=base
else
        BASE=$1
fi

BACKUP_DIR=/home/ubuntu/backup
BASE_DIR=$BACKUP_DIR/$BASE
BASE_XB_FILE=${BASE_DIR}.xb

sudo rm -rf $BACKUP_DIR
mkdir -p $BASE_DIR
sudo xtrabackup --host=172.17.0.2 --user=root --password=123456 --backup --compress --stream=xbstream --target-dir=$BASE_DIR --extra-lsndir=$BASE_DIR > $BASE_XB_FILE
sudo chown -R ubuntu:ubuntu $BASE_XB_FILE
scp -i /home/ubuntu/.ssh/sfox.studio.9.aws.pem $BASE_XB_FILE ubuntu@kamidox.com:/home/ubuntu/backup/

按照当前 6GB 左右的数据库容量，大概在 2m 内可以完成备份。备份出来的文件压缩后在 2.4GB 左右。

备份完成后，我们可以上传到另外一台服务器上，以确保备份文件在物理上，处于不同的区域，提高灾备有效性和安全性。

上传备份时间比较长，且会占用应用服务器的带宽，最好在半夜执行。

还原数据库¶

当应用服务器的数据损坏或丢失，可以从灾备还原。还原前，需要先把备份数据库拷贝到应用服务器上，然后执行以下脚本来还原。

xtrabackup --decompress --remove-original --target-dir=/home/ubuntu/mysql/backup/
xtrabackup --prepare --target-dir=/home/ubuntu/mysql/backup/
sudo docker container stop mysql
rm -rf /home/ubuntu/mysql/data
mv /home/ubuntu/mysql/backup/ /home/ubuntu/mysql/data
sudo docker container start mysql

此处假设 /home/ubuntu/mysql/data 是 mysql 数据库所在目录。还原后，可以把 /home/ubuntu/mysql/backup/ 删除以便节省空间。

增量备份¶

增量备份，可以大幅节省空间以及传输的带宽。

#!/bin/bash
# 脚本调用示例 1: backup-database-inc.sh base inc1
# 脚本调用示例 2: backup-database-inc.sh inc1 inc2
if [ ! $1 ]; then
        BASE=base
        INC=inc1
else
        BASE=$1
        INC=$2
fi

BASE_DIR=/home/ubuntu/backup/$BASE
INC_DIR=/home/ubuntu/backup/$INC
INC_XB_FILE=${INC_DIR}.xb

mkdir -p $INC_DIR
sudo xtrabackup --host=172.17.0.2 --user=root --password=123456 --backup --compress --stream=xbstream --incremental-basedir=$BASE_DIR --target-dir=$INC_DIR --extra-lsndir=$INC_DIR > $INC_XB_FILE
sudo chown -R ubuntu:ubuntu $INC_XB_FILE
scp -i /home/ubuntu/.ssh/sfox.studio.9.aws.pem $INC_XB_FILE ubuntu@kamidox.com:/home/ubuntu/backup/

还原增量备份¶

BASE_DIR=/home/ubuntu/mysql/backup/base
INC_DIR1=/home/ubuntu/mysql/backup/inc1
INC_DIR2=/home/ubuntu/mysql/backup/inc2

xtrabackup --prepare --apply-log-only --target-dir=$BASE_DIR
xtrabackup --prepare --apply-log-only --target-dir=$BASE_DIR --incremental-dir=$INC_DIR1
xtrabackup --prepare --target-dir=$BASE_DIR --incremental-dir=$INC_DIR2

除了最后一个增量备份外，其他的备份都需要带 --apply-log-only 参数。

准备好了之后，就可以用 rsync, cp, mv 等命令来直接恢复数据库。

自动增量备份¶

自动从 /home/ubuntu/backup 目录查找已经备份的文件，并在最新的增量备份基础上，再进行增量备份。

#!/bin/bash
# hint: 必须使用 bash，否则 crontab 会默认使用 /bin/sh，导致无法识别 ${BASE:3} 表达式
# hint: 在 BACKUP_DIR 目录下查找最新的增量备份，在这个基础上再次进行增量备份，如 inc1 -> inc2; inc324 -> inc325;
BACKUP_DIR=/home/ubuntu/backup
BASE=$(ls -lt $BACKUP_DIR | grep '^d' | grep inc | head -n 1 | awk '{print $9}')
if [ ! $BASE ]; then
        BASE=base
        INC=inc1
else
        NUM=${BASE:3}
        NUM=$((NUM+1))
        INC=${BASE:0:3}${NUM}
fi

echo "backup database incremental, BASE=$BASE INC=$INC"
/home/ubuntu/bin/backup-database-inc.sh $BASE $INC

（完）