mysql8.4备份与恢复实践

原创已于 2025-06-11 16:54:27 修改 · 215 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2025-06-10 15:21:27 首次发布

MySQL 同时被 2 个专栏收录

13 篇文章

订阅专栏

数据库

9 篇文章

订阅专栏

服务器版本

#cat /etc/os-release

openEuler 22.03 (LTS-SP4)

数据库与数据信息

数据库版本：

#mysql --version

mysql Ver 8.4.5 for Linux on x86_64 (MySQL Community Server - GPL)

数据库：testdb1

表t_1,t_2,t_3,t_4

表初始数据：

物理冷备份与恢复

物理冷备份：

停止mysql服务

#systemctl stop mysql

打包压缩data目录：

#cd /opt/software/mysql/mysql-8.4.5-linux-glibc2.28-x86_64/data

#tar -zcvf $(date +%Y%m%d%H%M%S).tar.gz *

这样冷备份就完成了，这种备份简单，适合非核心业务场景，能很好地保证数据库的完整性。由于备份过程简单直接，所以备份和恢复速度都比较快。但缺点也很明显，需要停机操作，这在一些对业务连续性要求较高的场景中不太适用。

物理恢复：

模拟数据丢失：

停止mysql服务

#systemctl stop mysql

备份现有data目录：

执行：

#mv data data_bak2

然后新建一个空data目录：

执行：

#mkdir data

将物理冷备份的压缩文件包复制到data目录

执行：

将备份的数据解压恢复到data目录：

#tar -xzf 20250607111223.tar.gz

启动mysql服务：

#systemctl start mysql

登录数据库查看数据是否恢复：

恢复成功！

Mysqldump逻辑热备份与恢复

mysqldump常用选项

--databases或-B：备份指定的数据库
--all-databases或-A：备份所有数据库
-d或--no-data：用于备份数据库的结构（即表的创建语句）但不包括表中的数据
--flush-logs或-F ：在备份开始前刷新 MySQL 服务器的日志文件
--single-transaction：使用单一事务来备份数据
--lock-all-tables ：备份过程中锁定所有表
--master-data=2 ：用于备份二进制日志的位置信息，用于设置主从复制
--set-gtid-purged=OFF ：用于关闭 GTID 信息的输出

举例：

mysqldump -u 用户 -p密码 --databases testdb1 testdb2 > mysqldump_20250611_testdb.sql #备份一个或多个指定库及库中的所有表

mysqldump -u 用户 -p密码 --all-databases > mysqldump_20250611_all.sql #备份所有库及库中的所有表

mysqldump -u 用户 -p密码 --all-databases | gzip > mysqldump_20250611_all.sql.gz

mysqldump -u 用户 -p密码 testdb1 t_1 t_2 > mysqldump_20250611.sql #备份指定库中的一个或多个表（不包含库对象本身）

备份testdb1数据库：

执行：

#mysqldump -u root -p -B testdb1 > $(date +%Y%m%d%H%M%S).sql

模拟数据丢失：

查看当前t_1,t_2,t_3,t_4表数据：

模拟丢失数据：

恢复testdb1数据库：

#mysql -u root -p < /opt/software/mysql/mysql-8.4.5-linux-glibc2.28-x86_64/data_backup/20250611111101.sql

验证恢复情况：

结论：数据恢复正常

增量备份与恢复

配置

增量备份与恢复需要开启二进制日志：

#vi my.cnf

配置

[mysqld]

# 启用binglog日志文件，可以指定目录，如果不指定则放在数据目录下面
log_bin = mysql-bin

# 二进制日志文件的格式
binlog_format = mixed
配置好后要重启mysql服务

先做一次全量备份

#mysqldump -u root -p -B testdb1 > $(date +%Y%m%d%H%M%S).sql

增量备份（刷新二进制日志）

#mysqladmin -u root -p flush-logs

mysql-bin开头的文件即是二进制日志文件

通过刷新二进制日志实现增量备份

后续操作日志都写在新生成的二进制日志文件中

查看二进制日志内容

方法：mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制日志(binlog)文件路径

或者：mysqlbinlog --no-defaults 二进制日志(binlog)文件路径

#mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/software/mysql/mysql-8.4.5-linux-glibc2.28-x86_64/data/mysql-bin.000012

查看当前数据

模拟基于位置增量恢复

t_1表增加1条数据

查看二进制日志

#mysqlbinlog --no-defaults mysql-bin.000012

t_2表删除一条数据

查看二进制日志

先完全恢复

#mysql -u root -p < /opt/software/mysql/mysql-8.4.5-linux-glibc2.28-x86_64/data_backup/20250611115824.sql

再基于位置增量恢复

语法格式：

mysqlbinlog --no-defaults --start-position=起始位置点 --stop-position=结束位置点二进制日志文件路径

#mysqlbinlog --start-position=25167 --stop-position=25499 /opt/software/mysql/mysql-8.4.5-linux-glibc2.28-x86_64/data/mysql-bin.000012 | mysql -u root -p -S /opt/software/mysql/mysql-8.4.5-linux-glibc2.28-x86_64/mysql.sock

验证恢复情况

数据恢复成功！

模拟基于时间点增量恢复

查看当前数据

全量备份

#mysqldump -u root -p -B testdb1 > $(date +%Y%m%d%H%M%S).sql

刷新二进制日志

#mysqladmin -u root -p flush-logs

后续操作日志都写入新生成的二进制日志文件中

操作数据库（新增数据，删除数据，删除表）

查看二进制日志文件

#cd data

#mysqlbinlog --no-defaults mysql-bin.000016

先全量恢复

#mysql -u root -p < /opt/software/mysql/mysql-8.4.5-linux-glibc2.28-x86_64/data_backup/20250611163102.sql

再基于时间点增量恢复

语法格式：

mysqlbinlog --no-defaults --start-datetime='YYYY-mm-dd HH:MM:SS' --stop-datetime='YYYY-mm-dd HH:MM:SS' 二进制日志文件路径

#mysqlbinlog --no-defaults --start-datetime='2025-06-11 16:33:25' --stop-datetime='2025-06-11 16:34:58' /opt/software/mysql/mysql-8.4.5-linux-glibc2.28-x86_64/data/mysql-bin.000016

查看数据恢复情况

没有恢复到理想状态，理想数据应该是t_1有5条数据，t_2有2条，t_3有4条，t_4有4条

不知道是哪里搞错了？

增量恢复说明

在维护数据库时,因为各种各样的原因可能会导致数据丢失,如:人为的 SQI语句破坏数据库、在进行下一次全备份之前发生系统故障导致数据库数据丢失、在数据库主从架构中主库的数据发生故障等。当出现以上场景时可以使用增量恢复来恢复数据。
常用的增量恢复的方法有三种:一般恢复、基于位置的恢复、基于时间点的恢复。

一般恢复:

将所有备份的二进制日志内容全部恢复，命令格式如下所示。
mysqlbinlog [--no-defaults] 增量备份二进制文件 | mysql -u 用户名 -p 密码

基于位置的恢复:

数据库管理员在操作数据库时可能在同一时间点既有错误的操作也有正确的操作，通过基于位置进行恢复可以更加精准，命令格式如下所示
格式 1:恢复数据到指定位置。
mysqlbinlog --stop-position=' 操作 id ' 二进制文件 | mysql -u 用户名 -p 密码

格式2：从指定的位置开始恢复数据。
mysqlbinlog --start-position=' 操作 id ' 二进制文件 | mysql -u 用户名 -p 密码

基于时间点的恢复:

跳过某个发生错误的时间点实现数据恢复，而基于时间点的恢复可以分成三种情况。
格式 1:从日志开头截止到某个时间点的恢复。
mysqlbinlog [--no-defaults] --stop-datetime=' 年-月-日小时：分：秒' 二进制日志 | mysql -u 用户名 -p 密码

格式 2:从某个时间点到日志结尾的恢复。
mysqlbinlog [--no-defaults] --start-datetime=' 年-月-日小时:分钟:秒二进制日志 | mysql -u 用户名 -p 密码

格式 3:从某个时间点到某个时间点的恢复。
mysqlbinlog [--no-defaults] --start-datetime = '年-月-日小时:分钟:秒’ -- stop-datetime = ’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

恢复原则：

从开始语句的BEGIN的位置点或时间点start，到结束语句的COMMIT之后的位置点或时间点stop

PXB（Percona XtraBackup）备份与恢复

Percona XtraBackup是世界上唯一一款开源的免费MySQL热备份软件，可以为InnoDB和XtraDB数据库执行非阻塞备份。
使用Percona XtraBackup，可以获得以下好处：

快速可靠地完成备份
备份期间不间断的事务处理
节省磁盘空间和网络带宽
自动备份验证
由于恢复时间更快，正常运行时间更长
Percona XtraBackup对所有版本的Percona Server for MySQL和MySQL进行MySQL热备份。它执行流式、压缩和增量MySQL备份。
注意：
随着Percona XtraBackup 8.0的推出，Percona XtraBackup 2.4将继续支持MySQL和Percona Server 5.6和5.7数据库。由于新的MySQL重做日志和数据字典格式，Percona XtraBackup 8.0.x版本将只与MySQL8.0.x和即将推出的用于MySQL8.0.x的Percona服务器兼容。

下载PXB：

下载地址：

https://www.percona.com/downloads/XtraBackupDownload free, open source software for MySQL including Percona Server, Percona XtraBackup, Percona Toolkit, Percona XtraDB Cluster, and more. Download now!https://www.percona.com/downloads/XtraBackup

查看服务器的glibc版本，然后选择对应的版本下载

查看glibc版本方法，执行：

#rpm -q glibc

安装PXB：

将下载PXB上传到服务器目录：/opt/software/mysql/pxb，然后解压：

#tar -zxvf percona-xtrabackup-8.4.0-3-Linux-x86_64.glibc2.34

设置环境变量：

#vi /etc/profile

增加：

export XTRABACKUP_HOME=/opt/software/mysql/pxb/percona-xtrabackup-8.4.0-3-Linux-x86_64.glibc2.34
export PATH=$PATH:$HOME/bin:$XTRABACKUP_HOME/bin

使环境变量生效，执行：

#source /etc/profile

验证PXB安装是否成功，执行：

#xtrabackup --version

报错：xtrabackup: error while loading shared libraries: libssl.so.3: cannot open shared object file: No such file or directory
即缺失：libssl.so.3 包，这个包位于openssl包中

查看服务器openssl版本，发现是1.1版本，需要升级至3.x