一、概述

1.1 背景介绍

在数据库运维中，数据安全是系统稳定运行的基石。随着业务规模的增长和数据价值的提升，如何制定一套高效、可靠的MySQL备份策略成为每个DBA或开发人员必须面对的问题。

MySQL备份主要有两大工具：

• mysqldump：官方提供的逻辑备份工具，简单可靠，适合小型数据库；
• Percona XtraBackup：开源的免费的物理热备份工具，高效快速，适合大型数据库；

本文将详细介绍这两种工具的使用方法、适用场景以及生产环境的最佳实践。

1.2 工具特点

• mysqldump特点
  
• Percona XtraBackup特点
  

1.3 使用场景

二、备份与恢复准备工作

2.1 安装备份工具xtrabackup

# Rocky Linux 9 / CentOS Stream 9 
--- 使用yum安装Percona仓库
sudo dnf install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release setup pxb-80

# 安装XtraBackup
sudo dnf install -y percona-xtrabackup-80

# 安装压缩工具
sudo dnf install -y qpress lz4

--- 二进制安装Percona
sudo dnf install -y libev libaio-devel libaio openssl-devel libev-devel perl perl-devel perl-CPAN perl-DBD-MySQL perl-Time-HiRes 

wget -c https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.35-35/binary/tarball/percona-xtrabackup-8.0.35-35-Linux-x86_64.glibc2.34-minimal.tar.gz

tar -zxvf percona-xtrabackup-8.0.35-35-Linux-x86_64.glibc2.34-minimal.tar.gz

mv percona-xtrabackup-8.0.35-35-Linux-x86_64.glibc2.34-minimal   /usr/local/xtrabackup

echo "export PATH=$PATH:/usr/local/xtrabackup/bin" >> /etc/profile

source /etc/profile

# 验证安装
xtrabackup --version
# xtrabackup version 8.0.35-30 based on MySQL server 8.0.35

# Ubuntu 24.04
sudo apt-get update
sudo apt-get install -y wget gnupg2 lsb-release
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo percona-release setup pxb-80
sudo apt-get install -y percona-xtrabackup-80 qpress

2.2 创建备份用户

-- 创建备份专用用户
create user 'backup'@'localhost' identified by 'BackupPass@2024';

-- mysqldump所需权限
grant select,show view,trigger,lock tables,event on *.* to 'backup'@'localhost';

-- XtraBackup 2.4 所需权限
grant reload,process,lock tables,replication client on *.* to 'backup'@'localhost';

-- XtraBackup 8.0+所需权限
grant backup_admin,reload,process,lock tables,replication client on *.* to 'backup'@'localhost';
grant select on performance_schema.log_status to 'backup'@'localhost';
grant select on performance_schema.keyring_component_status to 'backup'@'localhost';
grant select on performance_schema.replication_group_members to 'backup'@'localhost';

-- 刷新权限
flush privileges;

2.3 创建测试数据

create database school;
use school;
create table user(id int(3), name char(20), address char(32));

insert into user values(001,'zhangsan', 'beijing');
insert into user values(002,'lisi', 'zhejiang');

select * from user;
+------+----------+----------+
| id   | name     | address  |
+------+----------+----------+
|    1 | zhangsan | beijing  |
|    2 | lisi     | zhejiang |

2.4 创建备份目录

# 创建备份目录结构
mkdir -p /backup/mysql/{full,incremental,binlog,scripts,logs}
chown -R mysql:mysql /backup/mysql
chmod 750 /backup/mysql

# 目录说明：
# /backup/mysql/full        - 全量备份
# /backup/mysql/incremental - 增量备份
# /backup/mysql/binlog      - binlog归档
# /backup/mysql/scripts     - 备份脚本
# /backup/mysql/logs        - 备份日志

三、mysqldump备份与恢复实战

3.1 mysqldump介绍

• mysqldump适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备。

3.2 mysqldump常用选项

3.3 mysqldump全量备份与恢复实战

3.3.1 全量备份

# 备份单个数据库
mysqldump -u backup -p'BackupPass@2024' \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --databases \
    school > /backup/mysql/full/school_$(date +%Y%m%d).sql

# 备份多个数据库
mysqldump -u backup -p'BackupPass@2024' \
	--single-transaction \
	--routines \
	--triggers \
	--events \
  --databases db1 db2 db3 > /backup/mysql/full/multi_db_$(date +%Y%m%d).sql

# 备份所有数据库
mysqldump -u backup -p'BackupPass@2024' \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --all-databases > /backup/mysql/full/all_db_$(date +%Y%m%d).sql

# 备份单个表
mysqldump -u backup -p'BackupPass@2024' \
    --single-transaction \
    school user > /backup/mysql/full/school_user_$(date +%Y%m%d).sql

# 备份多个表
mysqldump -u backup -p'BackupPass@2024' \
    --single-transaction \
    school users1 users2 > /backup/mysql/full/school_users1_users2_$(date +%Y%m%d).sql

# 只备份表结构
mysqldump -u backup -p'BackupPass@2024' \
    --no-data \
    school > /backup/mysql/full/school_schema_$(date +%Y%m%d).sql

# mysqldump生产环境推荐参数组合
mysqldump -u backup -p'BackupPass@2024' \
    --single-transaction \          # InnoDB一致性读，不锁表
    --master-data=2 \               # 记录binlog位置（注释形式）
    --routines \                    # 包含存储过程和函数
    --triggers \                    # 包含触发器
    --events \                      # 包含事件调度器
    --set-gtid-purged=AUTO \        # GTID处理（自动判断）
    --hex-blob \                    # 二进制数据使用十六进制
    --quick \                       # 逐行读取，减少内存使用
    --max-allowed-packet=512M \     # 大数据包支持
    --default-character-set=utf8mb4 \  # 字符集
    --all-databases \
    | gzip > /backup/mysql/full/all_db_$(date +%Y%m%d).sql.gz

# MySQL 8.0.26+ 使用新参数名
# --master-data 改为 --source-data
mysqldump -u backup -p'BackupPass@2024' \
    --single-transaction \
    --source-data=2 \               # 新参数名
    --all-databases \
    | gzip > /backup/mysql/full/all_db_$(date +%Y%m%d).sql.gz

3.3.2 模拟MySQL数据丢失

mysql> drop database school;
mysql> select * from school.user;

3.3.3 恢复数据库

mysql -u backup -p'BackupPass@2024' < /backup/mysql/full/school_$(date +%Y%m%d).sql

3.3.4 恢复数据表

mysql -u backup -p'BackupPass@2024' school < /backup/mysql/full/school_user_$(date +%Y%m%d).sql

四、xtrabackup备份与恢复实战

4.1 XtraBackup介绍

xtrabakackup有2个工具，分别是xtrabakup、innobakupex。

• xtrabackup主要备份innoDb和xtraDb两种表；
• innobackupex则只能备份innoDb和myisam

在 xtrabakackup 2.4版本后，innobackupex功能已经全部集成到xtrabackup，innobackupex作为xtrabackup的软链接。

4.2 XtraBackup常用选项

--host                  指定主机
--user                  指定用户名
--password              指定密码
--port                  指定端口
--databases             指定数据库
--incremental           执行增量备份，需要指定–incremental-basedir
--backup                执行完全备份
--incremental-basedir   指定完全备份的目录
--incremental-dir       指定增量备份的目录   
--apply-log             对备份进行预处理操作，在备份完成后，通过回滚未提交的事务和同步已经提交的事务至数据文件，保证数据完整
--redo-only             通常与 --apply-log 一起使用，用于应用备份中的重做日志（redo log），但不回滚未提交的事务
--copy-back             执行恢复操作
--target-dir            指定备份存储目录（目录不存在会自动创建）
--datadir               指定MySQL数据目录
--databases             指定仅备份特定数据库 / 表（多个用空格分隔）
--compress              压缩备份文件（依赖 qpress 工具）
--compress-threads      指定压缩线程数量
--defaults-file         指定MySQL配置文件路径（必须放在命令行第一个选项位置）
--no-timestamp          备份目录不自动生成时间戳（默认会生成）
--prepare               对备份文件做预处理（应用 redo log、回滚未提交事务）
--apply-log-only        仅应用重做日志（redo log），不回滚事务	
--parallel              指定并行线程数

4.3 XtraBackup全量备份与恢复实战

4.3.1 全量备份

# 创建全量备份
xtrabackup --backup \
    --user=backup \
    --password='BackupPass@2024' \
    --socket=/var/lib/mysql/mysql.sock \
    --parallel=4 \
    --target-dir=/backup/mysql/full/$(date +%Y%m%d)

# 创建全量备份并压缩
xtrabackup --backup \
    --user=backup \
    --password='BackupPass@2024' \
    --socket=/var/lib/mysql/mysql.sock \
    --target-dir=/backup/mysql/full/$(date +%Y%m%d) \
    --compress \
    --compress-threads=4

# 解压全量备份
xtrabackup --decompress \
    --parallel=4 \
    --target-dir=/backup/mysql/full/$(date +%Y%m%d)

4.3.2 查看备份检查点文件

cat /backup/mysql/full/$(date +%Y%m%d)/xtrabackup_checkpoints

backup_type = full-prepared
from_lsn = 0            #全量备份的起始LSN（始终从 0 开始）
to_lsn = 19706024       #全量备份的结束LSN（增量备份的 from_lsn 必须等于这个值）
last_lsn = 19706024     #备份时数据库的最新LSN（与 to_lsn 一致，说明备份一致性正常）
flushed_lsn = 19706024
redo_memory = 0
redo_frames = 0

4.3.3 模拟MySQL数据丢失

mysql> drop database school;

mysql> select * from school.user;

4.3.4 恢复全量数据

1）停止MySQL服务

由于 xtrabackup 执行的是物理备份，所以想要进行恢复MySQL，必须先要停止MySQL服务。
systemctl stop mysqld

2）删除数据目录
当恢复数据时，必须保证MySQL目录 /var/lib/mysql 目录必须是空的，否则MySQL 数据目录中残留旧文件（如 ibdata1、表空间文件、日志文件），会与恢复的备份文件产生冲突，导致 InnoDB 数据字典不一致、日志校验失败，最终恢复失败；

rm -rf /var/lib/mysql

3）执行预处理备份

#由于备份是将所有物理库表等文件复制到备份目录，而整个过程需要持续一段时间，此时备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务，最终导致备份结果处于不一致状态。此时需要进行 prepare 操作来回滚未提交的事务及同步已经提交的事务至数据文件，从而保证数据一致性。
xtrabackup --prepare \
    --target-dir=/backup/mysql/full/$(date +%Y%m%d)

4）恢复全量数据

xtrabackup --copy-back \
    --target-dir=/backup/mysql/full/$(date +%Y%m%d)

5）修改数据目录权限

chown -R  mysql:mysql /var/lib/mysql

6）启动MySQL服务

systemctl start mysqld

4.4 XtraBackup增量备份与恢复实战

• 使用 Xtrabackup 进行增量备份时，每一次增量备份都需要以上一次的备份为基础，之后再将增量备份运用到第一次全备之上，从而完成备份。

4.4.1 增量备份

# 创建全量备份（上面备份过，可省略）
xtrabackup --backup \
    --user=backup \
    --password='BackupPass@2024' \
    --socket=/var/lib/mysql/mysql.sock \
    --parallel=4 \
    --target-dir=/backup/mysql/full/$(date +%Y%m%d)

# 数据库插入第一次新数据
insert into user values(003,'wangwu', 'shanghai');

# 创建第一次增量备份
xtrabackup --backup \
    --user=backup \
    --password='BackupPass@2024' \
    --socket=/var/lib/mysql/mysql.sock \
    --parallel=4 \
    --target-dir=/backup/mysql/incremental/incr1 \
    --incremental-basedir=/backup/mysql/full/$(date +%Y%m%d)

注意：正常情况下，增量备份数据中 xtrabackup_checkpoints文件 的 from_lsn 必须等于全量备份的 to_lsn，如果数值不相等，说明增量备份建错了（基于错误的全量/增量创建），需重新创建增量备份；如果数值相等，继续下一步。

# 数据库插入第二次新数据
insert into user values(004,'zhangxiaoming', 'henan');

# 创建第二次增量备份（基于第一次增量）
xtrabackup --backup \
    --user=backup \
    --password='BackupPass@2024' \
    --socket=/var/lib/mysql/mysql.sock \
    --parallel=4 \
    --target-dir=/backup/mysql/incremental/incr2 \
    --incremental-basedir=/backup/mysql/incremental/incr1

4.4.2 模拟MySQL数据丢失

mysql> drop database school;

mysql> select * from school.user;

4.4.3 增量恢复

• 由于此时 school 数据库中有全量 + 增量的数据，因此恢复数据时，也要恢复全量 + 增量的数据。不可以只恢复某一次的增量备份(不包含全量备份)。

1）停止MySQL服务

由于 xtrabackup 执行的是物理备份，所以想要进行恢复MySQL，必须先要停止MySQL服务。
systemctl stop mysqld

2）删除数据目录
当恢复数据时，必须保证MySQL目录 /var/lib/mysql 目录必须是空的，否则MySQL 数据目录中残留旧文件（如 ibdata1、表空间文件、日志文件），会与恢复的备份文件产生冲突，导致 InnoDB 数据字典不一致、日志校验失败，最终恢复失败；

rm -rf /var/lib/mysql

3）恢复全量 + 第一次增量

# 准备预处理全量备份（仅应用redo log日志，不回滚事务）
⚠️ 注意：全量备份的 --apply-log-only 只能执行 1 次，重复执行会导致 LSN 状态异常。
xtrabackup --prepare \
    --apply-log-only \
    --target-dir=/backup/mysql/full/$(date +%Y%m%d)

# 合并第一次增量合并到全量备份
xtrabackup --prepare \
    --apply-log-only \
    --target-dir=/backup/mysql/full/$(date +%Y%m%d) \
    --incremental-dir=/backup/mysql/incremental/incr1

# 最后预处理备份（回滚未提交事务）
xtrabackup --prepare \
    --target-dir=/backup/mysql/full/$(date +%Y%m%d) 

注意：最后预处理备份可省略 --apply-log，因为默认就是完整预处理

# 恢复所有备份数据（全量+第一次增量）
xtrabackup --copy-back \
    --target-dir=/backup/mysql/full/$(date +%Y%m%d) 

4）恢复全量 + 第一次增量 + 第二次增量

# 准备预处理全量备份（仅应用redo log日志，不回滚事务）
xtrabackup --prepare \
    --apply-log-only \
    --target-dir=/backup/mysql/full/$(date +%Y%m%d)

# 合并第一次增量合并到全量备份
xtrabackup --prepare \
    --apply-log-only \
    --target-dir=/backup/mysql/full/$(date +%Y%m%d) \
    --incremental-dir=/backup/mysql/incremental/incr1

# 合并第二次增量合并到全量备份
xtrabackup --prepare \
		--apply-log-only \
    --target-dir=/backup/mysql/full/$(date +%Y%m%d) \
    --incremental-dir=/backup/mysql/incremental/incr2

# 最后预处理备份（回滚未提交事务）
xtrabackup --prepare \
    --target-dir=/backup/mysql/full/$(date +%Y%m%d) 

注意：最后预处理备份可省略 --apply-log，因为默认就是完整预处理

# 恢复所有备份数据（全量+增量）
xtrabackup --copy-back \
    --target-dir=/backup/mysql/full/$(date +%Y%m%d) 

5）修改数据目录权限

chown -R mysql:mysql /var/lib/mysql

6）启动MySQL服务

systemctl start mysqld

五、总结

1）mysqldump 和 xtrabackup 都是 MySQL 备份的重要工具，它们各有优缺点。
2）mysqldump 简单易用，适用于小型数据库和开发测试环境；而 xtrabackup 备份速度快，支持热备份和增量备份，适用于生产环境中的大型数据库。
3）在实际应用中，可以根据具体需求和场景选择合适的备份工具，并制定合理的备份与恢复策略，以确保数据库的安全性和高可用性。