本地mysql备份至rds_阿里云RDS备份在本地mysql快速还原

本地准备：

##安装和RDS相同的mysql版本，拿mysql5.6为例

##安装Xtrabackup 包

cd /data/source

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.8/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm

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

##去RDS控制台下载备份包放到 /data/source 目录下

##下载解压脚本

wget http://oss.aliyuncs.com/aliyunecs/rds_backup_extract.sh?spm=5176.7741817.2.4.dzPsOP&file=rds_backup_extract.sh -O rds_backup_extract.sh

##准备一个backup-my.cnf

vim backup-my.cnf

# This MySQL optionsfilewas generated by innobackupex.

# The MySQL server

[mysqld]

innodb_checksum_algorithm=innodb

#innodb_log_checksum_algorithm=innodb

innodb_data_file_path=ibdata1:200M:autoextend

innodb_log_files_in_group=2innodb_log_file_size=524288000#innodb_fast_checksum=falseinnodb_page_size=16384#innodb_log_block_size=512innodb_undo_directory=.

innodb_undo_tablespaces=0#rds_encrypt_data=false#innodb_encrypt_algorithm=aes_128_ecb

##使用脚本还原

#!/bin/bash

db_dir=/data/source/mysql/data

systemctl stop mysqldps -ef | grep mysqld | grep -v grep | awk '{ print "kill -9 " $2}' |bash

#创建文件夹

cd/data/sourceif [ -d $db_dir ];then

rm -rf /data/source/mysqlecho "Delete directory '$db_dir'"

fi

mkdir -p $db_direcho "Create directory '$db_dir'"#解压数据if [ $1 ];then

echo "Backup file is: '$1'"bash rds_backup_extract.sh -f $1 -C $db_direlseread-p "Please enter a backup file name:"bak_fileif [ $bak_file ];then

echo "Backup file is: '$bak_file'"bash rds_backup_extract.sh -f $bak_file -C $db_direlse

echo "Input error"exit0

fi

fi

echo "`ls -l $db_dir`"#还原数据库

innobackupex--defaults-file=$db_dir/backup-my.cnf --apply-log $db_dirchown -R mysql:mysql $db_dircp -f backup-my.cnf $db_dir/backup-my.cnf

#启动实例

mysqld_safe--defaults-file=$db_dir/backup-my.cnf --user=mysql --datadir=$db_dir > /tmp/mysql_demo.lo

g2>&1 &

sleep 10mysql_upgrade-uroot

#设置密码while:doread-p "Please enter the root@% password:"passwordif [ $password ];then

echo "The root@% password is ‘$password’"breakelse

echo "Input error"

fi

donemysql-uroot <

GRANT ALL PRIVILEGES ON*.* TO 'root'@'%' IDENTIFIED BY '$password'WITH GRANT OPTION;

EOF

exit0