阿里云 RDS 恢复到自建数据库

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

安装XtraBackup和qpress

#################################安装qpress#####################################
wget "http://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/183466/cn_zh/1608011575185/qpress-11-linux-x64.tar"
tar xvf qpress-11-linux-x64.tar
chmod 775 qpress
cp qpress /usr/bin
​
#################################安装XtraBackup#################################
#MySQL8.0
https://www.percona.com/downloads/Percona-XtraBackup-LATEST/#
​
#MySQL5.6、5.7选择2.4的版本
https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.20/binary/
#使用二进制安装Xtrabackup 2.4.*报错：libgcrypt.so.20
yum list installed|grep libgcrypt
下载对应的libgcrypt的版本的包就可以了
​

下载备份文件

wget  -c "download.url"  -O   backup_qp.xb

解压压缩包

## 先解包
cat backup_qp.xb | xbstream -x -v -C /home/mysql/data
### 对于MySQL 5.6/5.7
innobackupex --decompress --remove-original /home/mysql/data
### 对于MySQL 8.0
xtrabackup --decompress --remove-original --target-dir=/home/mysql/data

回滚未提交的事务

## MySQL 5.6/5.7
innobackupex --defaults-file=/home/mysql/data/backup-my.cnf --apply-log /home/mysql/data
​
## MySQL 8.0
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_checksum_algorithm=innodb
innodb_log_checksum_algorithm=innodb
innodb_fast_checksum=false
innodb_page_size=16384
innodb_log_block_size=512
rds_encrypt_data=false
innodb_encrypt_algorithm=aes_128_ecb
新增一下参数
server_id = 330621
innodb_buffer_pool_size =20G
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
log-error=/data/mysql/error.log
transaction-isolation=READ-COMMITTED
character-set-server = utf8mb4
default_time_zone = '+8:00'
sync_binlog=1
log-bin=mysql-bin
expire_logs_days=7
gtid-mode=on
enforce-gtid-consistency=true
################################ 修改目录属性################################ 
mkdir  /data/mysql
cp  -rp  /home/mysql/data/*    /data/mysql
useradd  -s /sbin/nologin  mysql
chown -R mysql.mysql  /data/mysql
​

启动MySQL服务

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