华为云.qp文件恢复到数据库

华为云MySQL 5.6.51全量备份.qp文件恢复到自建数据库测试实验

mysql下载安装

https://downloads.mysql.com/archives/community/
MySQL-5.6.51-1.el7.x86_64.rpm-bundle.tar
1
2

yum -y remove mariadb-libs
rpm -e --nodeps mariadb-server mariadb  mariadb-libs
rm -rf /etc/my.cnf
rm -rf /var/lib/mysql/*
tar xf MySQL-5.6.51-1.el7.x86_64.rpm-bundle.tar
rm -rf MySQL-5.6.51-1.el7.x86_64.rpm-bundle.tar
yum -y install MySQL-*
rm -rf MySQL-*
1
2
3
4
5
6
7
8

service mysql start
ls /var/lib/mysql
ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  RPM_UPGRADE_HISTORY  RPM_UPGRADE_MARKER-LAST  test
1
2
3

cat /root/.mysql_secret   #默认的密码存放文件
The random password ...(local time): deQn1aEPea_HyaIv

mysql_secure_installation		#初始化数据库
mysql -uroot -p123456 -e "show databases;"
1
2
3
4
5

迁移准备

qpress下载

https://cloud.tencent.com/document/product/236/33364  #在这个帖子上找到的下载
[root@vm opt]# tar xf qpress-11-linux-x64.tar
[root@vm opt]# chmod +x qpress
[root@vm opt]# cp qpress /usr/local/bin/
1
2
3
4

XtraBackup 2.4下载，

wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum localinstall percona-release-latest.noarch.rpm
yum clean all && yum makecache fast
yum provides xtrabackup
1
2
3
4

#只下载到了这一个版本
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
yum localinstall  percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
1
2
3

[root@vm opt]# service mysql stop
[root@vm opt]# mv /var/lib/mysql /opt/
[root@vm opt]# mkdir /var/lib/mysql
[root@vm opt]# cat /usr/my.cnf   #5.6默认配置文件
[mysqld]
datadir=/var/lib/mysql  #默认没有指定目录，恢复时会报错
1
2
3
4
5
6

参考文档https://blog.csdn.net/weixin_45533230/article/details/120649097

[root@vm opt]# cd /opt;mkdir backupdir
[root@vm opt]# xbstream -x  4 < /opt/test.qp -C ./backupdir/
[root@vm opt]# ls backupdir/
backup-my.cnf.qp  mysql          performance_schema         xtrabackup_checkpoints  xtrabackup_logfile.qp
ibdata1.qp        obs_heartbeat  xtrabackup_binlog_info.qp  xtrabackup_info.qp      xtrabackup_slave_info.qp

[root@vm opt]# innobackupex --parallel 4 --decompress ./backupdir
16:43:50 completed OK!   #342M，几秒钟

[root@vm opt]# innobackupex --apply-log ./backupdir
17:05:59 innobackupex: Starting
17:06:18 completed OK!
1
2
3
4
5
6
7
8
9
10
11
12

[root@vm opt]# innobackupex --defaults-file=/usr/my.cnf --copy-back ./backupdir    #确保my.cat里的datadir存在且为空
17:06:35 innobackupex: Starting 
17:06:51 completed OK!

[root@vm opt]# ls /var/lib/mysql
ibdata1      ib_logfile1  mysql          performance_schema            xtrabackup_info
ib_logfile0  ibtmp1       obs_heartbeat  xtrabackup_binlog_pos_innodb  xtrabackup_slave_info

[root@vm opt]# chown -R mysql.mysql /var/lib/mysql
[root@vm opt]# service mysql start
Starting MySQL.Logging to '/var/lib/mysql/vm.err'.
 SUCCESS!
1
2
3
4
5
6
7
8
9
10
11
12

[root@vm opt]# mysql -uroot -p登录
#5.6改密码，比较粗暴
use mysql;
update user set password=password("123456") where user="root";
flush privileges;
1
2
3
4
5