RDS_MYSQL_物理备份文件恢复到自建数据库

Posted by Elli0t on 2020-08-05

这次恢复的文件是 <数据备份文件名>_qp.xb

官方文档

Ubuntu 16.04 恢复(最后一步失败)

前期环境搭建

1
2
3
4
Distributor ID:	Ubuntu
Description: Ubuntu 16.04.6 LTS
Release: 16.04
Codename: xenial
  • version
    • mysql:8.0.21
    • percona-xtrabackup-80
    • qpress 默认版本

percona-xtrabackup-80(apt 安装)

1
2
3
4
5
6
7
8
sudo apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
sudo vim /etc/apt/sources.list
然后在最后加上两行
deb http://repo.percona.com/apt saucy main
deb-src http://repo.percona.com/apt saucy main

sudo apt-get update
sudo apt-get install percona-xtrabackup-80

qpress

1
2
3
4
wget http://www.quicklz.com/qpress-11-linux-x64.tar
tar xvf qpress-11-linux-x64.tar
sudo chmod 775 qpress
sudo cp qpress /usr/bin

mysql

和 percon-xtrabackup-80 方式一样,二进制文件将版本信息加入到 source.list 文件中,apt-get update 然后 apt-get install mysql-server 就安装了 8.0 版本了

1
2
3
4
官网下载二进制包
https://dev.mysql.com/downloads/repo/apt

sudo dpkg -i mysql-apt-config_0.8.15-1_all.deb

选择好版本和安装内容,点击 OK。后面有一个让你选择使不使用新的加密方式:随便你

asdfaewf2348

1
2
sudo apt-get update
sudo apt-get install mysql-server

恢复

说明 请确保您的Percona XtraBackup版本正确:

  • MySQL 5.6及之前的版本需要安装 Percona XtraBackup 2.3,安装指导请参见官方文档Percona XtraBackup 2.3
  • MySQL 5.7版本需要安装 Percona XtraBackup 2.4,安装指导请参见官方文档Percona XtraBackup 2.4
  • MySQL 8.0版本需要安装 Percona XtraBackup 8.0,安装指导请参见官方文档Percona XtraBackup 8.0
1
2
3
4
5
6
7
8
9
10
11
12
13
## 解包
cat <数据备份文件名>_qp.xb | xbstream -x -v -C /home/mysql/data
## MySQL 8.0解压
xtrabackup --decompress --remove-original --target-dir=/home/mysql/data
## MySQL 准备文件和复制文件到数据库目录
xtrabackup --prepare --target-dir=/home/mysql/data
xtrabackup --datadir=/var/lib/mysql --copy-back --target-dir=/home/mysql/data
## 修改文件属主,并确定文件所属为MySQL用户
chown -R mysql:mysql /home/mysql/data
## 启动MySQL进程
mysqld_safe --defaults-file=/home/mysql/data/backup-my.cnf --user=mysql --datadir=/home/mysql/data &
## 登录MySQL数据库以验证进程启动成功(我这一步失败了,一直报错,说我配置文件出错)
mysql -uroot -p<数据库密码>

/var/lib/mysql 是数据库储存目录

/home/mysql/data 是你恢复备份的解压目录,但是 mysqld_safe 那一步还是配置了从 /home/mysql/data 启动

为避免版本问题,需修改backup-my.cnf参数,具体操作步骤查看文章头部的官方文档

登录MySQL数据库以验证进程启动我这一步失败了,一直报错,说我配置文件出错,这是报错信息

1
2
3
4
5
6
7
8
9
10
11
root@ubuntu:/var/lib/mysql# tail /var/log/mysql/error.log 
2020-08-05T03:08:32.236408Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.21' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-08-05T03:11:07.514641Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.21).
2020-08-05T03:11:08.651728Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21) MySQL Community Server - GPL.
2020-08-05T03:11:15.772931Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21) starting as process 35648
2020-08-05T03:11:15.794569Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-08-05T03:11:16.171519Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-08-05T03:11:16.176632Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('0') and data dictionary ('1').
2020-08-05T03:11:16.176874Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-08-05T03:11:16.177658Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-08-05T03:11:16.694328Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21) MySQL Community Server - GPL.
巩固的知识
1
2
3
4
5
6
7
8
9
10
## 查找已经安装的 xxxx 的程序
dpkg -l | grep xxxx
## 卸载 xxxx 程序
apt-get --purge autoremove xxxx
## 查看 ubuntu 版本
lsb_release -a
## ubuntu mysql 储存数据目录
/var/lib/mysql/
## mysql 启动
/etc/init.d/mysql start
参考链接

ubuntu16.0下安装xtrabackup

https://blog.csdn.net/qq_36652619/article/details/79679362

Centos 7.8 (2003) 恢复(成功)

一定要严格按照版本来安装,否则会失败的。因为其他版本的我都试过了,数据库最后都启动失败…

前期环境搭建

1
2
3
4
5
6
7
8
[root@localhost mysql]# cat /etc/redhat-release
CentOS Linux release 7.8.2003 (Core)
[root@localhost mysql]# mysql --version
mysql Ver 8.0.21 for Linux on x86_64 (MySQL Community Server - GPL)
[root@localhost mysql]# xtrabackup --version
xtrabackup: [Warning] option 'innodb_undo_tablespaces': unsigned value 0 adjusted to 2.
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_data_file_path=ibdata1:200M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=524288000 --innodb_undo_directory=/var/lib/mysql/ --datadir=/var/lib/mysql --innodb_undo_tablespaces=0 --server-id=999098802
xtrabackup version 8.0.9 based on MySQL server 8.0.18 Linux (x86_64) (revision id: c5cbbe4)

一下两个程序,我放在 github 上了:https://github.com/ybm911/mysql_resume_blog_program

percona-xtrabackup-80-8.0.9-1.el7.x86_64.rpm

1
yum install -y percona-xtrabackup-80-8.0.9-1.el7.x86_64.rpm

qpress-11-1.el7.x86_64.rpm

1
yum install -y qpress-11-1.el7.x86_64.rpm

mysql

1
2
3
4
5
wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm
yum localinstall mysql80-community-release-el7-1.noarch.rpm
yum -y install yum-utils
yum install mysql-community-server
## 安装好了不要启动数据库

恢复

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
cat xxxx_qp.xb |xbstream -x -v -C /var/lib/mysql
xtrabackup --decompress --remove-original --target-dir=/var/lib/mysql
xtrabackup --prepare --apply-log-only --target-dir=/var/lib/mysql

## 对文件夹重新授权
chown -R mysql.mysql /var/lib/mysql

## 修改/etc/my.cnf配置文件
[mysqld]
lower_case_table_names=1
innodb_checksum_algorithm=crc32
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=524288000
innodb_undo_directory=/var/lib/mysql/
basedir=/usr
datadir=/var/lib/mysql
innodb_undo_tablespaces=0
server_id=999098802
skip-grant-tables=1
sql_mode=''

## 此时启动数据库,修改aliyun_root用户密码
systemctl start mysqld.service

## 进入数据库
mysql -u –p
mysql> flush privileges;
mysql> alter user 'aliyun_root'@'127.0.0.1' identified by '123456';
mysql> flush privileges;

## 去掉skip-grant-tables参数,重启数据库
将/etc/my.cnf skip-grant-tables=1 这个参数删掉或注释掉,再重启数据库

systemctl restart mysqld.service

mysql -ualiyun_root –p123456 -h127.0.0.1 登录数据库,可自由操作

至此结束🔚,centos 的整个操作笔者是一路被版本问题坑过来的,现在总结出来的大概率能恢复成功。

巩固的知识
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
## 清除系统默认配置的 yum 源
yum clean all
## 建立 yum 仓库缓存
yum makecache
## 升级所有的软件包
yum update
## 升级一个软件包
yum update xxxx
## 安装一个本地 rpm 包
yum install xxxx.rpm
## 显示已经安装的软件包
yum list installed
## 查找可以安装的软件包
yum list xxxx
## 卸载软件包,-y 自动应答yes
yum remove xxxx
参考链接

RDS for MySQL8.0物理备份恢复到本地自建数据库

https://yq.aliyun.com/articles/711361?scm=20140722.184.2.173&do=login&accounttraceid=402ca4bd82c34bac9296cdcf93515dffielo