阿里云RDS物理備份的數(shù)據(jù)胖眷，下載后是.xd格式嫂侍，導(dǎo)入本地?cái)?shù)據(jù)庫需要使用數(shù)據(jù)恢復(fù)工具Percona XtraBackup栽烂。MySQL 5.6及之前的版本需要安裝 Percona XtraBackup 2.3阎曹。MySQL 5.7版本需要安裝 Percona XtraBackup 2.4
參考官方文檔安裝：[https://www.percona.com/doc/percona-xtrabackup/2.3/installation/yum_repo.html#f1]
1.安裝repo源
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
安裝結(jié)果如下：
Retrieving http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
Preparing... ########################################### [100%]
1:percona-release ########################################### [100%]
2.查看repo源是否添加成功
yum list | grep percona
結(jié)果如下：
...
percona-xtrabackup-20.x86_64 2.0.8-587.rhel5 percona-release-x86_64
percona-xtrabackup-20-debuginfo.x86_64 2.0.8-587.rhel5 percona-release-x86_64
percona-xtrabackup-20-test.x86_64 2.0.8-587.rhel5 percona-release-x86_64
percona-xtrabackup-21.x86_64 2.1.9-746.rhel5 percona-release-x86_64
percona-xtrabackup-21-debuginfo.x86_64 2.1.9-746.rhel5 percona-release-x86_64
percona-xtrabackup-22.x86_64 2.2.13-1.el5 percona-release-x86_64
percona-xtrabackup-22-debuginfo.x86_64 2.2.13-1.el5 percona-release-x86_64
percona-xtrabackup-debuginfo.x86_64 2.3.5-1.el5 percona-release-x86_64
percona-xtrabackup-test.x86_64 2.3.5-1.el5 percona-release-x86_64
percona-xtrabackup-test-21.x86_64 2.1.9-746.rhel5 percona-release-x86_64
percona-xtrabackup-test-22.x86_64 2.2.13-1.el5 percona-release-x86_64
...
3.使用rpm安裝
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.5/binary/redhat/7/x86_64/percona-xtrabackup-2.3.5-1.el7.x86_64.rpm
rpm -ivh percona-xtrabackup-2.3.5-1.el7.x86_64.rpm
4.缺少兩個依賴
"libev.so.4()(64bit) is needed"
"perl(DBD::mysql) is needed"
wget ftp://rpmfind.net/linux/atrpms/el6-x86_64/atrpms/stable/libev-4.04-2.el6.x86_64.rpm
rpm -ivh libev-4.04-2.el6.x86_64.rpm
yum install -y "perl(DBD::mysql)"
如何恢復(fù)數(shù)據(jù)：https://help.aliyun.com/knowledge_detail/41817.html
RDS恢復(fù)到本地服務(wù)器后，搭建主從
1.恢復(fù)數(shù)據(jù)
參考阿里文檔
https://help.aliyun.com/knowledge_detail/41817.html
a.數(shù)據(jù)庫的版本至少為5.6.16及以上(大于等于RDS版本)扬霜，我用的是5.6.37
b.我使用percona-xtrabackup-2.3.8
c.下載rds_backup_extract.sh
1.下載RDS備份
wget -c '<數(shù)據(jù)備份文件外網(wǎng)下載地址>' -O <自定義文件名>.tar.gz
2.解壓備份至data目錄
bash rds_backup_extract.sh -f <數(shù)據(jù)備份文件名>.tar.gz -C /home/mysql/data
3.應(yīng)用日志
innobackupex --defaults-file=/home/mysql/data/backup-my.cnf --apply-log /home/mysql/data
4.修改配置文件my.cnf
innodb_checksum_algorithm=innodb
server-id = 152053308
master-info-repository=file
relay-log-info_repository=file
gtid_mode=on
enforce-gtid-consistency=on
log_slave_updates
binlog_format = row
replicate-wild-ignore-table = mysql.%
replicate-wild-ignore-table = performance_schema.%
5.啟動MySQL
/usr/local/mysql/bin/mysqld --defaults-file=/data/apps/mysql/mysql3308/my.cnf &
小版本升級
/usr/local/mysql/bin/mysql_upgrade -uroot -S /data/apps/mysql/mysql3308/data/mysql.sock
2.搭建主從
備份文件中的 xtrabackup_slave_info 文件是我們將要執(zhí)行的腳本
[root@]# more xtrabackup_slave_info
SET GLOBAL gtid_purged='0c0e0c92-deb5-11e6-bf17-7cd30abeb86a:1-16282080, 29ed6371-deb5-11e6-bf18-7cd30abeb14e:1-21992780';
CHANGE MASTER TO MASTER_AUTO_POSITION=1
mysql> SET GLOBAL gtid_purged='0c0e0c92-deb5-11e6-bf17-7cd30abeb86a:1-16282080, 29ed6371-deb5-11e6-bf18-7cd30abeb14e:1-21992780';
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> SET GLOBAL gtid_purged='0c0e0c92-deb5-11e6-bf17-7cd30abeb86a:1-16282080, 29ed6371-deb5-11e6-bf18-7cd30abeb14e:1-21992780';
Query OK, 0 rows affected (0.01 sec)
清理殘留數(shù)據(jù)
truncate table mysql.slave_relay_log_info;
truncate table mysql.slave_master_info;
truncate table mysql.slave_worker_info;
delete from mysql.db where user<>'root' and char_length(user)>0;
delete from mysql.tables_priv where user<>'root' and char_length(user)>0;
flush privileges;
主庫創(chuàng)建備份賬戶
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'10.%' IDENTIFIED BY 'xxx';
從庫執(zhí)行slave命令
change master to master_host='aaa.mysql.rds.aliyuncs.com',master_port=3306, master_user='repl', master_password='xxx', master_auto_position=1;
驗(yàn)證
show slave status\G
