从Aliyun RDS还原备份数据备忘

从后台下载备份, 可以下载到本地,也可以在远程机器上操作。

安装工具:
https://www.percona.com/doc/percona-xtrabackup/2.2/installation/yum_repo.html

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum list | grep percona
yum install percona-xtrabackup-22

rpm -qa | grep percona
percona-release-0.1-3.noarch
percona-xtrabackup-22-2.2.13-1.el7.x86_64

/usr/bin/innobackupex
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-22-2.2.13
/usr/share/doc/percona-xtrabackup-22-2.2.13/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz

需要使用的是/usr/bin/innobackupex工具。

下载解压工具:http://oss.aliyuncs.com/aliyunecs/rds_backup_extract.sh?spm=5176.7741817.2.6.Aw8bl9&file=rds_backup_extract.sh

chmod +x rds_backup_extract.sh

解压实例备份数据(看数据大小,可能时间较长, 另外确保磁盘空间足够):

rds_backup_extract.sh -f hins3426165_data_xxx.tar.gz -C /root/mysql/data

还原数据:

innobackupex —defaults-file=/root/mysql/data/backup-my.cnf --apply-log /root/mysql/data

启动实例:

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

启动实例可能会遇到各种问题,主要是配置文件导致的,注释backup-my.cnf中对应的配置即可:
1 [ERROR] /alidata/server/mysql/bin/mysqld: unknown variable ‘innodb_log_checksum_algorithm=innodb’
把innodb_log_checksum_algorithm=innodb注释掉

2 [ERROR] InnoDB: ./ibdata1 can’t be opened in read-write mode
rm -fr ibdata1 ib*

3 [ERROR] –gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires –log-bin and –log-slave-updates
添加gtid_mode = on,enforce_gtid_consistency = 1,log_slave_updates = 1

4 [ERROR] Fatal error: Can’t open and lock privilege tables: Can’t find file: ‘./mysql/user.frm’
新建用户:
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;

5 其它问题
一般都是配置相关,对应注释掉相对应的指令即可。