0%

阿里云Mysql备份恢复

阿里云Mysql备份恢复

安装Percona数据库

使用官网给出的安装方式

1
2
3
4
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm
http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm

yum install Percona-Server-server-56

恢复软件

1
2
3
4
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.12-r170eb8c-el7-x86_64-bundle.tar
tar xvf Percona-XtraBackup-2.4.12-r170eb8c-el7-x86_64-bundle.tar

yum install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

数据文件

从阿里上下载解压脚本和数据文件

1
2
3
4
5
6
7
wget -c 'http://oss.aliyuncs.com/aliyunecs/rds_backup_extract.sh?spm=a2c4g.11186623.2.6.JS88k1&file=rds_backup_extract.sh' -O rds_backup_extract.sh

mkdir -p /home/mysql/data

bash rds_backup_extract.sh -f copy.tar.gz -C /home/mysql/data

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

编辑backup-my.cnf文件

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

注释掉下面的参数

1
2
3
4
5
#innodb_fast_checksum
#innodb_page_size
#innodb_log_block_size
#rds_encrypt_data=false
#innodb_encrypt_algorithm=aes_128_ecb

执行

1
chown -R mysql:mysql /home/mysql/data

启动

1
2
3
4
5
6
7
8
9
10
11
12
 mysqld_safe --defaults-file=/home/mysql/data/backup-my.cnf --user=mysql --datadir=/home/mysql/data
~~~
如果报这个错
Can't find file: './mysql/plugin.frm'

执行
setenforce 0

然后执行mysql_upgrade --force -uroot -p

使用mysql -uroot 登录到数据库
执行命令

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;
~~~