基础环境：
阿里云ECS
RockyLinux 9.4 – 5.14.0-427.24.1.el9_4.x86_64
mysql Ver 8.4.2 for Linux on x86_64 (MySQL Community Server - GPL)
xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7)
qpress 1.1
MySQL数据恢复、Binlog解析需要版本对应，跨大版本肯定是不行了，即8.0的备份、Binlog只能在8.0上还原、解析；
数据库环境搭建
操作系统环境准备，基础包准备
yum install -y vim wget tar1.关闭selinux
查看状态
getenforce
Enforcing：强制模式，代表SELinux运行中，且已经正确开始限制domain/type
Permissive：宽容模式，代表SELinux运行中，不过仅会有警告信息并不会实际限制domain/type的读写（这种模式可以用来作为SELinux的debuug之用）
Disabled：关闭模式，SELinux并没有实际运行
临时关闭
setenforce 0
永久关闭
vim /etc/selinux/config
SELINUX=disabled
reboot2.关闭swap
swapon --show
临时关闭
swapoff /dev/dm-1
永久关闭
vim /etc/fstab
注释掉
UUID=xxxx-xxxx none swap sw 0 0 或者 /dev/sdXN none swap sw 0 0
更新 grub
grub2-mkconfig -o /boot/grub2/grub.cfg
重启系统
reboot3.关闭防火墙
systemctl status firewalld
systemctl stop firewalld
systemctl disable firewalld
4.修改内核参数
vim /etc/sysctl.conffs.aio-max-nr= 1048576
fs.file-max= 6815744
kernel.shmall= 2097152
kernel.shmmax= 4294967295
kernel.shmmni= 4096
kernel.sem= 250 32000 100 128
net.ipv4.ip_local_port_range= 9000 65500
net.core.rmem_default= 262144
net.core.rmem_max= 4194304
net.core.wmem_default= 262144
net.core.wmem_max= 1048576
vm.swappiness = 0生效
/sbin/sysctl -p
/sbin/sysctl -a 5.修改资源使用参数
vim /etc/security/limits.conf# *表示所有用户，可只设置root和kingbase用户
* soft nofile 65536
# 注意：设置nofile的hard limit不能大于/proc/sys/fs/nr_open，否则注销后将无法正常登陆
* hard nofile 65535
* soft nproc 65536
* hard nproc 65535
# unlimited表示无限制
* soft core unlimited
* hard core unlimited6.RemoveIPC参数
关闭RemoveIPC。CentOS操作系统默认为关闭，可以跳过该步骤。
vim /etc/systemd/logind.conf
RemoveIPC=no
vim /usr/lib/systemd/system/systemd-logind.service
RemoveIPC=no
# 重新加载
systemctl daemon-reload
systemctl restart systemd-logind.service
# 验证
loginctl show-session | grep RemoveIPC
systemctl show systemd-logind | grep RemoveIPC7.时区设置
export TZ=CST-8
yum install ntpdate -y
ntpdate cn.ntp.org.cn8.关闭透明大页
echo never > /sys/kernel/mm/transparent_hugepage/enabled888.设置LOCAL
查看
locale -a
有些国产库安装的时候需要中文显示
需要设置
export LANG=zh_CN.utf8
dnf install glibc-langpack-zh.x86_64
echo LANG=zh_CN.UTF-8 > /etc/locale.conf
source /etc/locale.conf
locale -a
9.数据盘挂载
lsblk
fdisk /dev/sdb
n
p
#1
t
#1
8e
w
fdisk -l
pvcreate /dev/sdb1
pvs
vgcreate vgmysql /dev/sdb1
vgs
lvcreate -L 99.9G -n lvmysql vgmysql
lvs
mkfs.ext4 /dev/vgmysql/lvmysql
mkdir /mysqldata
mount /dev/mapper/vgmysql-lvmysql /mysqldata
vim /etc/fstab
/dev/mapper/vgmysql-lvmysql /mysqldata ext4 defaults 0 0
重启操作系统
reboot10.数据库安装
wget https://dev.mysql.com/get/mysql84-community-release-el9-1.noarch.rpm
yum localinstall -y mysql84-community-release-el9-1.noarch.rpm
yum repolist enabled | grep mysql.*-community
yum repolist all | grep mysql
dnf config-manager --enable mysql-8.4-lts-community
yum install -y mysql-community-server
mkdir -p /mysqldata/{data,logs}
touch /mysqldata/logs/slow.log
touch /mysqldata/logs/mysqld.log
touch /mysqldata/logs/error.log
chmod 640 /mysqldata/logs/error.log
chmod 640 /mysqldata/logs/slow.log
chown -R mysql:mysql /mysqldata
chmod 750 -R /mysqldata/编译配置文件
# chmod 644 /etc/my.cnf
vim /etc/my.cnf
datadir=/mysqldata/data
port=3306
socket=/var/lib/mysql/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid
server_id=1
autocommit=1
log_error=/mysqldata/logs/error.log
lower_case_table_names=1
slow_query_log=1
slow_query_log_file=/mysqldata/logs/slow.log
long_query_time=1
innodb_buffer_pool_size=6G
max_connections=512
wait_timeout=1800
performance_schema=1
local_infile=1
default-time_zone = '+8:00'
# event_scheduler=ON
# sql_mode=''
# binlogs
binlog_row_image=full
binlog_row_metadata=full
[mysqldump]
user=user_backup
password="Lunz@2024"
启动数据库
systemctl start mysqld
# 获取临时密码
cat /mysqldata/logs/error.log|grep pass|awk '{print $NF}'#更新root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Lunz@2017';
# 允许root用户远程连接
use mysql;
update user set Host = '%' where User='root';
FLUSH PRIVILEGES;验证所需工具
mysqldump --help
mysqlbinlog --help数据库还原工具安装
这里使用 阿里云 XtraBackup 8.0
由于RDS MySQL 8.0有新增Redo类型，开源版本的Percona XtraBackup可能存在兼容性问题。因此请下载阿里云RDS提供的XtraBackup工具。
sudo yum localinstall -y t-rds-xtrabackup-80-8.0.31-20230817110455.alios6.x86_64sudo yum localinstall -y t-rds-xtrabackup-80-8.0.31-20230817110455.alios7.x86_64.rpmsudo yum localinstall -y t-rds-xtrabackup-80-8.0.31-20230817110455.alios7.aarch64.rpmsudo yum localinstall -y t-rds-xtrabackup-80-8.0.31-20230817110455.al8.aarch64.rpm说明：安装完成后，Percona XtraBackup的可执行文件将位于/u01/xtrabackup80/bin目录下，该目录路径默认不会被添加到Linux系统的PATH环境变量中。
[root@iZbp1ctgni579msvzghxk4Z ~]# wget https://help-static-aliyun-doc.aliyuncs.com/file-manage-files/zh-CN/20230825/kpof/t-rds-xtrabackup-80-8.0.31-20230817110455.alios7.x86_64.rpm
--2024-08-16 09:44:00-- https://help-static-aliyun-doc.aliyuncs.com/file-manage-files/zh-CN/20230825/kpof/t-rds-xtrabackup-80-8.0.31-20230817110455.alios7.x86_64.rpm
Resolving help-static-aliyun-doc.aliyuncs.com (help-static-aliyun-doc.aliyuncs.com)... 121.89.58.187, 121.89.58.189, 121.89.58.190, ...
Connecting to help-static-aliyun-doc.aliyuncs.com (help-static-aliyun-doc.aliyuncs.com)|121.89.58.187|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 300957476 (287M) [application/x-rpm]
Saving to: ‘t-rds-xtrabackup-80-8.0.31-20230817110455.alios7.x86_64.rpm’
t-rds-xtrabackup-80-8.0.31-20230817110455.alios7.x8 100%[===================================================================================================================>] 287.01M 10.8MB/s in 24s
2024-08-16 09:44:24 (12.0 MB/s) - ‘t-rds-xtrabackup-80-8.0.31-20230817110455.alios7.x86_64.rpm’ saved [300957476/300957476]
[root@iZbp1ctgni579msvzghxk4Z ~]# yum localinstall t-rds-xtrabackup-80-8.0.31-20230817110455.alios7.x86_64.rpm
Last metadata expiration check: 2:18:12 ago on Fri 16 Aug 2024 07:26:29 AM CST.
Dependencies resolved.
================================================================================================================================================================================================================
Package Architecture Version Repository Size
================================================================================================================================================================================================================
Installing:
t-rds-xtrabackup-80 x86_64 8.0.31-20230817110455.alios7 @commandline 287 M
Transaction Summary
================================================================================================================================================================================================================
Install 1 Package
Total size: 287 M
Installed size: 1.2 G
Is this ok [y/N]:find / -name xtrackbackup
vim ~/.bash_profile
# 添加
export XTB="/u01/xtrabackup80/bin"
export PATH="$XTB:$PATH"
# 生效
source ~/.bash_profile如果阿里云系统为CentOS 7 以上，建议装Percona官方版本
wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.35-31/binary/redhat/9/x86_64/percona-xtrabackup-80-8.0.35-31.1.el9.x86_64.rpm?_gl=1*1pig9ys*_gcl_au*MTIwNjc4NDg3NS4xNzIzNzczMDU1 -O percona-xtrabackup-80-8.0.35-31.1.el9.x86_64.rpm
[root@iZbp1ctgni579msvzghxk4Z ~]# yum localinstall percona-xtrabackup-80-8.0.35-31.1.el9.x86_64.rpm
Last metadata expiration check: 3:33:31 ago on Mon 19 Aug 2024 05:12:08 AM CST.
Dependencies resolved.
================================================================================================================================================================================================================================
Package Architecture Version Repository Size
================================================================================================================================================================================================================================
Installing:
percona-xtrabackup-80 x86_64 8.0.35-31.1.el9 @commandline 37 M
Installing dependencies:
libev x86_64 4.33-5.el9 baseos 52 k
mariadb-connector-c x86_64 3.2.6-1.el9_0 appstream 195 k
perl-DBD-MySQL x86_64 4.050-13.el9 appstream 145 k
perl-DBI x86_64 1.643-9.el9 appstream 700 k
perl-DynaLoader x86_64 1.47-481.el9 appstream 24 k
perl-Math-BigInt noarch 1:1.9998.18-460.el9 appstream 188 k
perl-Math-Complex noarch 1.59-481.el9 appstream 45 k
zstd x86_64 1.5.1-2.el9 baseos 546 k
Transaction Summary
================================================================================================================================================================================================================================
Install 9 Packages
Total size: 39 M
Total download size: 1.9 M
Installed size: 197 M
Is this ok [y/N]: y[root@iZbp1ctgni579msvzghxk4Z ~]# xtrabackup --version
2024-08-19T08:53:37.348999+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/mysqldata/data --server-id=1 --innodb_buffer_pool_size=6G
xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7)
[root@iZbp1ctgni579msvzghxk4Z ~]# mysql --version
mysql Ver 8.4.2 for Linux on x86_64 (MySQL Community Server - GPL)安装解压工具 qpress
wget "https://static-aliyun-doc.oss-cn-hangzhou.aliyuncs.com/file-manage-files/zh-CN/20230406/flxd/qpress-11-linux-x64.tar"
tar -xvf qpress-11-linux-x64.tar
mv qpress /usr/local/bin/
sudo chmod 775 /usr/local/bin/qpress下载备份文件
注意复制的网址需要用单引号，再加 -O 指定保存文件名称
解压备份文件包
_xb.qp后缀
qpress -do hins23171014_data_20231204031428_xb.qp | xbstream -x -v -C /var/mysql_bkdata/_qp.xb后缀
cat hins24302919_data_20231117033429_xb.qp | xbstream -x -v -C /var/mysql_bkdata/
xtrabackup --decompress --remove-original --target-dir=/var/mysql_bkdata/恢复前准备
/u01/xtrabackup80/bin/xtrabackup --defaults-file=/var/mysql_bkdata/backup-my.cnf --prepare --target-dir=/var/mysql_bkdata/恢复数据
sudo xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/var/mysql_bkdata/