下载rds数据库冷备份文件，还原到本地mysql数据库操作流程！2022-11-17

阿里云安装-centos7.9-略

备份文件.xb的生成过程：先经过 qpress 压缩，后经过 xbstream 打包（xbstream 为 Percona 的一种打包/解包工具）

安装mysql5.7并启动服务

wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

rpm -ivh mysql57-community-release-el7-9.noarch.rpm

yum install mysql mysql-server mysql-devel

会报GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql错误
执行下列命令、解决安装报错

gpg --export -a 3a79bd29 > 3a79bd29.asc
rpm --import 3a79bd29.asc
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

yum install mysql mysql-server mysql-devel

systemctl start mysqld

这些不操作也行；后续也用不上！
grep 'temporary password' /var/log/mysqld.log
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root@123';
grant all on *.* to root@'%';
flush privileges;
show databases;
exit;

下载xtrabackup

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

yum install -y percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm

rpm -qa |grep xtrabackup

mkdir -p /root/mysql

cat hins7873945_data_20190710111001_qp.xb | xbstream -x -v -c /root/mysql
PS：这里把备份压缩包解压到指定目录下，解压后的文件为qp格式

chown -R mysql.mysql /root/mysql

xtrabackup --decompress --remove-original --target-dir=/root/mysql
PS：再次执行解压报错 提示没有qpress

下载qpress

wget https://repo.percona.com/yum/release/7/RPMS/x86_64/qpress-11-2.el7.x86_64.rpm

rpm -ivh qpress-11-2.el7.x86_64.rpm

innobackupex --decompress --remove-original /root/mysql
PS：解压缩qp包、并删除qp文件。

completed OK!

停止mysql并替换本地文件，再安全模式启动mysql服务

systemctl stop mysqld

netstat -antlp |grep 3306

cd /var/lib/

mv mysql mysqlback

mv /root/mysql ./

chown -R mysql.mysql /root/mysql

systemctl start mysqld

mysql -uroot -p
登录报错--因为mysql数据库的账号和密码也还原了 ；所以之前的无效了；需要安全模式登录

systemctl stop mysqld

cat /etc/my.cnf

vi /etc/my.cnf

[mysqld]
skip-grant-tables

systemctl start mysqld

配置远程ssh模式、加localhost登录数据库

dbeaver配置mysql链接、登录即可查看之前的备份数据库信息。

微信截图_20221117162949

引用文档：
Mysql5.7忘记root密码及mysql5.7修改root密码的方法
https://www.cnblogs.com/sharpest/p/10868651.html
Mysql 5.7 忘记root密码或重置密码的详细方法
https://www.cnblogs.com/hailun1987/p/16368823.html
使用Xtrabackup恢复以.xb结尾的数据备份
https://www.cnblogs.com/song1024/p/13297328.html
记录一次阿里云Mysql 数据库恢复 qp.xb文件恢复数据
https://blog.csdn.net/weixin_41979605/article/details/118496138
将阿里云RDS备份的qe.xb后缀文件部署到本地服务器-mysql5.6
https://blog.csdn.net/weixin_42917630/article/details/94985802

目录CONTENT