RDS MySQL 物理备份文件恢复到自建数据库

阿里云提供了定期备份数据库的功能，可以在阿里云的控制台将数据库恢复到备份点，那我们想将这部分数据导入到我们自建的数据库怎么操作呢？官方推荐使用开源软件Percona Xtrabackup对数据库进行恢复，Percona Xtrabackup目前没有Windows版本，但是我们可以使用Docker进行恢复。

准备工作

在阿里云的后台下载备份文件

生产环境的数据库使用的Mysql 8.0.16，我直接通过docker运行，并直接进入容器内部。

docker run -v /root/bak.xb:/root/bak.xb  -p 3308:3306 -it mysql:8.0.16 bash

先更换163源方便安装软件

cat >>/etc/apt/sources.list<<EOF
deb http://mirrors.163.com/debian/ stretch main non-free contrib
deb http://mirrors.163.com/debian/ stretch-updates main non-free contrib
deb http://mirrors.163.com/debian/ stretch-backports main non-free contrib
deb-src http://mirrors.163.com/debian/ stretch main non-free contrib
deb-src http://mirrors.163.com/debian/ stretch-updates main non-free contrib
deb-src http://mirrors.163.com/debian/ stretch-backports main non-free contrib
deb http://mirrors.163.com/debian-security/ stretch/updates main non-free contrib
deb-src http://mirrors.163.com/debian-security/ stretch/updates main non-free contrib
EOF

安装我们的主角percona-xtrabackup用户恢复文件，直接下载deb包进行安装

apt -y install lsb-release wget aria2 vim

wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb

dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb

apt-get update

apt-get -y install percona-xtrabackup-80

创建解压目录，官方文档中使用的这个路径，我懒得改了，就创建了一个

mkdir -p /home/mysql/data

开始解压

先安装qpress解压软件
aria2c http://www.quicklz.com/qpress-11-linux-x64.tar
tar xvf qpress-11-linux-x64.tar
chmod 775 qpress
cp qpress /usr/bin

cat bak.xb | xbstream -x -v -C /home/mysql/data

xtrabackup --decompress --remove-original --target-dir=/home/mysql/data

解压后进行数据恢复

# 删除原数据库中数据
rm -rf /var/lib/mysql/*

xtrabackup --prepare --target-dir=/home/mysql/data
xtrabackup --datadir=/var/lib/mysql --copy-back --target-dir=/home/mysql/data

自建数据库不支持如下参数，需要注释掉。

vi /home/mysql/data/backup-my.cnf

#innodb_log_checksum_algorithm
#innodb_fast_checksum
#innodb_log_block_size
#innodb_doublewrite_file
#rds_encrypt_data
#innodb_encrypt_algorithm
#redo_log_version
#master_key_id
#server_uuid

启动

mysqld_safe --defaults-file=/home/mysql/data/backup-my.cnf --user=root --datadir=/home/mysql/data &

2020-04-28T09:43:49.658819Z mysqld_safe Logging to '/home/mysql/data/14864923d3cb.err'.
2020-04-28T09:43:49.703474Z mysqld_safe Starting mysqld daemon with databases from /home/mysql/data

如果mysql服务没起来，可以查看/home/mysql/data/14864923d3cb.err这个日志文件

实际使用遇到过的问题

2020-08-18T06:18:32.304152Z 0 [ERROR] [MY-010095] [Server] Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files

--secure-file-priv 默认使用 /var/lib/mysql-files这个目录，我们更改一下

vim /home/mysql/data/backup-my.cnf
secure-file-priv=/home/mysql/data

2020-08-18T06:20:49.765666Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('0') and data dictionary ('1').

大小写配置问题

lower_case_table_names=1

再次启动后我们登录一下

root@a8a32828e279:/home/mysql/data# mysql -upro -p            
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| pro                |
| information_schema |
| mysql              |
+--------------------+
3 rows in set (0.01 sec)

mysql> 

OK，大功告成

参考资料

https://help.aliyun.com/knowledge_detail/41817.html