阿里云Mysql5.7 数据库恢复 qp.xb文件恢复数据_数据库备份qp文件如何还原

1、宕下来的文件，一定是qp.xb的文件，如果不是qp.xb文件，该恢复文档不适合

2、准备linux系统，centos最好

3、centos 基础环境准备完之后安装必须要的恢复软件【基本按照官方文档即可】

        官网：RDS MySQL的物理备份如何恢复到自建数据库_云数据库 RDS-阿里云帮助中心

        安装Qpress

#下载qoress 包，解压给予权限
wget "http://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/183466/cn_zh/1608011575185/qpress-11-linux-x64.tar"
tar xvf qpress-11-linux-x64.tar
chmod 775 qpress
cp qpress /usr/bin

       获取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

        网盘地址：

        链接：https://pan.baidu.com/s/1t57i7YtaTQg3LRVdPd0W6A 
        提取码：5ju4

       安装xtrabackup、mysql【mysql安装之后无需启动】

#安装xtrabackup
yum install -y percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
#安装mysql5.7系列
wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql-community-server
# rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022  如果报错，需要重新导入秘钥后再安装mysql-community-server

      Mysql5.7网盘地址：

                链接：https://pan.baidu.com/s/1j5W5qklQjxdLe1dXNXHJZQ 
                提取码：xa2a

        数据恢复：

#将8p47w1251_qp.xb文件放置/opt目录下
cd /opt
#创建/home/mysql/data/文件夹
mkdir -p /home/mysql/data
#使用xbstream处理qp.xb文件
cat 8p47w1251_qp.xb | xbstream -x -v -C /home/mysql/data
#进入/home/mysql/data进行解压【跟着官网教程走】 显示 completed OK! 即正常
cd /home/mysql/data/
innobackupex --decompress --remove-original /home/mysql/data
innobackupex --defaults-file=/home/mysql/data/backup-my.cnf --apply-log /home/mysql/data
chown -R mysql:mysql /home/mysql/data

        修改mysql配置文件

vim /home/mysql/data/backup-my.cnf
#添加如下参数
lower_case_table_names=1
#注释如下不支持的参数：
innodb_checksum_algorithm=crc32
innodb_log_checksum_algorithm=strict_crc32
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1572864000
innodb_fast_checksum=false
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_directory=./
innodb_undo_tablespaces=0
server_id=1291154917
redo_log_version=1
server_uuid=dbd99726-2585-11eb-9ae1-78aa82d27dc0
master_key_id=0
innodb_encrypt_algorithm=AES_256_CBC

 启动mysql【知道数据库账号密码】

#知道数据库账号、密码时
mysqld  --user=mysql --datadir=/home/mysql/data  &
 
#登陆
mysql -u xxx  -p
 

启动mysql【不知道数据库账号密码】

#不知道数据库账号、密码时
mysqld  --user=mysql --datadir=/home/mysql/data --skip-grant-tables &
#登陆,直接敲msyql 回车即可进入
msyql 

启动参数中加入 --skip-grant-tables启动数据库即可，使用root用户，可不需要密码直接登陆数据，但此时修改密码会由于触发器没有删除造成无法修改，“Unknown trigger has an error in its body: 'Unknown system variable 'maintain_user”；

mysql> select trigger_schema,trigger_name from information_schema.triggers;
+----------------+----------------------------+
| trigger_schema | trigger_name               |
+----------------+----------------------------+
| sys            | sys_config_insert_set_user |
| sys            | sys_config_update_set_user |
+----------------+----------------------------+
2 rows in set (0.03 sec)
mysql> drop trigger sys.sys_config_insert_set_user;
Query OK, 0 rows affected (0.06 sec)
 
mysql> drop trigger sys.sys_config_update_set_user;
Query OK, 0 rows affected (0.02 sec)

所以在启动前处理触发器

#查找触发器文件，并删除/移动
find /home/mysql/data/ -iname *.trg
    /home/mysql/data/mysql/proxies_priv.TRG
    /home/mysql/data/mysql/user.TRG
 
mv /home/mysql/data/mysql/user.TRG /home/mysql/data/mysql/user.TRG.back
mv /home/mysql/data/mysql/proxies_priv.TRG /home/mysql/data/mysql/proxies_priv.TRG.back

退出mysql，重启mysql后【启动参数加--skip-grant-tables】，进入

[root@localhost ~]# mysql 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.30 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> update mysql.user set authentication_string = password('新的密码') where user='用户名' and host='%';
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

退出，重启mysql【启动参数去掉--skip-grant-tables】，使用用户密码登录

mysql -u xxx -p