Xtrabackup实现MySQL备份与恢复

1 打开binlog配置，binlog文件生成规则，单个文件大小，时间规则等，binlog是否加密
2 定期全量备份：定时任务+脚本
3 短时间的增量备份：binlog
恢复：
在基础全量备份A的基础上追加合并多个增量备份文件，生成全量备份文件B，再使用命令行从备份文件B恢复数据。
自动备份工具：待完成

================= binlog_format ====================
查看binlog日志格式 SHOW GLOBAL VARIABLES LIKE '%binlog_format%';
binlog日志格式有三种：ROW、STATEMENT、MIXED
配置模式（无需重启）：SET GLOBAL binlog_format = 'STATEMENT'; -- 或 'ROW' 或 'MIXED'
在配置文件中永久配置 /etc/my.cnf [mysqld] binlog_format = ROW
MySQL 5.7.7 之前，默认格式为 Statement。MySQL 5.7.7 及之后，默认格式改为 Row，以提高数据一致性
================= row格式的binlog处理 ====================
mysql命令行环境：
show binary logs;
查看数据库的创建时间：找到数据库文件目录，stat db_name查看文件夹的创建时间，或ls -ld db_name
SHOW VARIABLES LIKE 'datadir';

================= 演练进度日志 ====================
20250926：新建了数据库，表，shell脚本，下载了percona的工具包
20250928：通过shell脚本持续插入数据
使用xtrbackup工具全量备份，只能备份innodb和xtradb两种数据表，只有ibd文件，frm文件它不管，恢复时需要DBA提供frm,这个工具不能备份表结构、触发器等，
----------------- 计划
使用工具查看数据库连接状态
使用工具做全量备份+增量备份+binlog恢复 完成全量备份与恢复演练(已完成)
binlog默认是系统级别的，也可以指定数据库名称只记录它们的binlog

================= 将二进制binlog转为可读的.sql文件
shell命令行环境：
https://www.cnblogs.com/dbasql/p/19010127
/usr/local/mysql/bin/mysqlbinlog -d test_dbname -vv --base64-output=decode-rows /var/lib/mysql/mysql-bin.000022 > /root/test_row_binlog.sql
/usr/local/mysql/bin/mysqlbinlog --start-position=773 --stop-position=927 -vv --base64-output=decode-rows binlog.000006 > binlog_pos2.sql
mysqlbinlog --start-datetime="2025-10-11 09:00:00" binlog.000019 binlog.000020 binlog.000021 > 11binlog.sql

row策略的binlog，mysqlbinlog binlog-000005 > binlog.sql后，看到的sql如下
# at 1573
#250905 15:21:29 server id 1 end_log_pos 1765 CRC32 0xab36ef7d Update_rows: table id 96 flags: STMT_END_F
### UPDATE `zabbix`.`ha_node`
### WHERE
### @1='cmecksaiy0001kxg7yl1iu8u9' /* VARSTRING(100) meta=100 nullable=0 is_null=0 */
### @2='' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
### @3='localhost' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
### @4=10051 /* INT meta=0 nullable=0 is_null=0 */
### @5=1757056884 /* INT meta=0 nullable=0 is_null=0 */
### @6=3 /* INT meta=0 nullable=0 is_null=0 */
### @7='cmf6iatfr0000jls51dbt80en' /* VARSTRING(100) meta=100 nullable=0 is_null=0 */
### SET
### @1='cmecksaiy0001kxg7yl1iu8u9' /* VARSTRING(100) meta=100 nullable=0 is_null=0 */
### @2='' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
### @3='localhost' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
### @4=10051 /* INT meta=0 nullable=0 is_null=0 */
### @5=1757056889 /* INT meta=0 nullable=0 is_null=0 */
### @6=3 /* INT meta=0 nullable=0 is_null=0 */
### @7='cmf6iatfr0000jls51dbt80en' /* VARSTRING(100) meta=100 nullable=0 is_null=0 */
# at 1765
#250905 15:21:29 server id 1 end_log_pos 1796 CRC32 0xb5c6309e Xid = 281
COMMIT/*!*/;
# at 1796

？xtrabackup适合大型数据库吗
当然适合！xtrabackup 是处理大型数据库备份和恢复场景的标杆工具之一，尤其适合大型数据库。
事实上，当数据库体积达到几百GB甚至TB级别时，传统的逻辑备份工具（如 mysqldump）会因为执行时间过长、锁表时间长、恢复速度极慢而变得不切实际，
而 xtrabackup 的优势在这些场景下会体现得淋漓尽致。
为什么 xtrabackup 非常适合大型数据库？
1 物理备份，速度快
xtrabackup 直接拷贝数据文件（.ibd, .frm 等），而不是像 mysqldump 那样通过 SELECT 语句生成 SQL 文件。文件拷贝的 I/O 效率远高于逻辑导出，备份速度主要取决于磁盘 I/O 性能，这对于大型数据库至关重要。
2 热备份，几乎不停机
它在备份过程中大多数时间不锁表，只在最后阶段为了获取一致的二进制日志位置信息会短暂锁定（通常使用 FLUSH TABLES WITH READ LOCK）。这意味着在生产环境中，数据库在备份期间仍然可以正常提供读写服务，保证了业务的高可用性。
3 增量备份，节省空间和时间
这是 xtrabackup 的核心优势。对于大型数据库，每次进行全量备份会消耗大量磁盘空间和带宽。xtrabackup 可以只备份自上次全量或增量备份以来发生变化的数据页。
效果：假设你有一个 1TB 的数据库，每天可能只变化 50GB。你可以设置每周日进行一次全量备份，周一到周六每天进行增量备份。这样，周一的增量备份基于周日，周二基于周一，以此类推。这极大地减少了日常备份的存储空间占用和备份时间，对网络和存储的压力也小得多。
4 高效的压缩和流备份
xtrabackup 支持在备份时直接进行压缩（使用 --compress 选项），可以减少备份文件体积，节省存储空间。
支持流式备份（使用 --stream=xbstream 或 --stream=tar），可以将备份数据直接通过网络传输到另一台备份服务器，避免在数据库服务器本地占用大量临时空间。
5 可靠的恢复机制
恢复过程也是物理级别的，直接将数据文件拷贝回数据目录，然后应用重做日志（--apply-log）来保证数据一致性。恢复一个 TB 级别的数据库，虽然也需要一定时间，但比导入一个 TB 级别的 SQL 文件要快几个数量级。
注意：实践时要参考“在大型数据库场景下的最佳实践和注意事项”
如果RTO(恢复时间目标)要求非常苛刻，可能需要考虑从库延迟复制、或者使用像 MySQL Shell 的 Clone Plugin 等更现代的技术作为补充。

================= 0 添加mysql安装目录到环境变量 =================
配置mysql安装目录到环境变量，就可以在任何位置使用mysql/bin目录下的命令，而不用写全路径
vim ~/.bashrc文件，添加两行
MYSQL_HOME=/usr/local/mysql/
export PATH="$MYSQL_HOME/bin:$PATH"
source ~/.bashrc #使文件生效
================= 1 mysql停服状态下所有数据库的全量备份与恢复，文件系统级 =================
--备份
service mysql stop/systemctl stop mysql #停服务
sudo tar -czvf mysql_full_backup_$(date +%Y%m%d).tar.gz /data/mysql/ #备份mysql的数据目录
service mysql start/systemctl start mysql #启动服务
--恢复
tar -xzvf mysql_full_backup_$(date +%Y%m%d).tar.gz -C [mysql数据库的数据目录]
================= 2 mysqldump数据库停服状态下单个数据库的全量备份与恢复，文件系统级 start=================
--备份
mysql暂时锁库(让数据库进入只读状态，禁止写入操作)
数据库生成sql文件：mysqldump -uroot -p"Admin123..." source_db_name --single-transaction --routines --events --triggers > sdbname_full_backup_20250929-0946.sql
--恢复
新建同名/不同名数据库：create database target_dbname default character set utf8mb4;
应用sql文件恢复数据：mysql -uroot -p"Admin123..." target_dbname < sdbname_full_backup_20250929-0946.sql
优点：不需要安装额外的工具，操作简单
缺点：备份时需要停止服务(否则数据不全)，数据量大时恢复速度慢
适用场景：小型数据库停服后，执行数据迁移。
================= 2 mysqldump数据库停服状态下单个数据库的全量备份与恢复，文件系统级 end=================

？不停服务备份数据库使用xtrabackup工具，为什么，这个工具解决了什么关键问题
xtrabackup支持热备份，对生产环境影响小，关键优点就是：热备份+增量备份+备份过程对生产影响小
1 配置binlog只记录指定数据库(可以)
2 不停服务单个数据库全量备份(可以)
3 不停服务单个数据库增量备份(可以)

================= xtrabackup简介与使用场景 =================
Xtrabackup专门用于MySQL数据库的开源热备份工具，主要用于InnoDB/XtraDB存储引擎的在线备份，(innodb引擎&在线备份&恢复快&对生产影响小&支持压缩和加密&还原到任意时间点)
Xtrabackup支持全量备份和增量备份，能在生产环境中最小化对数据库服务的影响。
备份过程无需中断事务执行，数据复制效率高；支持压缩和加密功能，可节省磁盘空间和传输流量。备份文件还原速度快，支持直接恢复到原服务器或迁移至其他服务器。 ‌
Xtrabackup支持快速还原至任意时间点，适用于数据库故障恢复或数据回滚场景。
--mysql5.x和8.x都会备份表机构(在其他地方看到xtrabackup不会备份低版本mysql的.frm文件，这是错误的)
在 MySQL 8.0 之前的版本（5.6、5.7 等），xtrabackup 会完全备份 .frm 文件，这是备份过程中不可或缺的一部分，确保了表结构的完整备份和恢复。
从 MySQL 8.0 开始，由于架构变化，xtrabackup 转而备份包含表结构信息的数据字典表，不再处理 .frm 文件。
================= 0 安装xtrabackup =================
从官网下载对应mysql服务版本的.rpm包，上传服务器
安装依赖 yum install lz4 perl-DBD-MySQL zstd
rpm -ivh percona-xtrabackup-80-8.0.35-34.1.el8.x86_64.rpm
xtrabackup --version #查看版本信息
(可选)安装pmm工具 rpm -ivh pmm-client-3.4.0-7.el8.x86_64.rpm
================= 1 验证xtrabackup的特性 =================
在线全量备份：
须知：备份目录空间要足够，不能小于原数据文件大小，备份目录需mysql用户可读写（chown -R mysql:mysql /backup_dir），xtrabackup与mysql版本匹配。
1、创建备份专用的mysql账号,赋予备份需要的权限，这里在root用户上加一个权限就行，GRANT BACKUP_ADMIN ON *.* TO `root`@`%`; show grants;
2、执行备份命令，参数看根据需求增加,--defaults-file指定配置文件,--socket指定sock文件位置，--target-dir指定备份文件存放位置
xtrabackup --defaults-file=/etc/my.cnf --user=root --password="Admin123..." --socket=/tmp/mysql.sock --backup --target-dir=/lyc/mysqlback
xtrabackup --defaults-file=/etc/my.cnf --user=root --password="Admin123..." --socket=/tmp/mysql.sock --backup \
--lock-ddl=false --lock-ddl-per-table --parallel=2 --compress=zstd --compress-threads=2 \
--target-dir=/lyc/mysqlfullback_$(date +%Y%m%d%H%M%S) > xtraback_log 2>&1
3、验证备份：检查备份目录下是否生成xtrabackup_checkpoints文件，cat命令查看文件内容，确认backup_type = full-backuped
4、准备恢复数据,使用--prepare或--apply-log合并事务日志，确保数据一致性：xtrabackup --prepare --target-dir=/lyc/mysqlback
5、停mysql服务，service mysql stop, ps -ef|grep mysql, pkill -9 mysql
# 数据目录一定能让mysql用户读写，不然会报错
chown -R mysql:mysql /data/mysql
# --data-dir指定mysql的数据目录，--copy-back后面接备份文件位置
6、恢复数据(需清空原数据目录)：xtrabackup --copy-back /lyc/mysqlback --data-dir=/data/mysql
7、service mysql start启动成功后查看数据，没有问题，恢复到了备份时候的样子。
压缩备份：可以
加密备份：还没测试
全量还原：可以
还原到指定时间点：可以
遇到的问题：无

==============*** 重点1：全量备份+增量备份 保证数据完整性 start***===============
全量备份是增量备份的基础
补充说明：--compress=zstd --compress-threads=2 如果要使用压缩，压缩得到的备份文件体积小，有利于存储和传输，恢复之前需要解压，不能从压缩文件直接恢复。
全量备份命令：xtrabackup --defaults-file=/etc/my.cnf --user=root --password="Admin123..." --socket=/tmp/mysql.sock --backup --lock-ddl=false --lock-ddl-per-table --parallel=2 --target-dir=/lyc/mysqlfullback_$(date +%Y%m%d%H%M%S) > xtraback_log 2>&1
解压命令：xtrabackup --decompress --target-dir=/lyc/10mysqlfullback
备份完成后，应用日志使其处于一致状态（但不要使用 --apply-log-only）)：xtrabackup --prepare --target-dir=/lyc/10mysqlfullback

第一次增量备份：
xtrabackup --defaults-file=/etc/my.cnf --user=root --password="Admin123..." --socket=/tmp/mysql.sock --backup --lock-ddl=false --lock-ddl-per-table --parallel=2 --target-dir=/lyc/mysql_1_increback_$(date +%Y%m%d%H%M%S) --incremental-basedir=/lyc/10mysqlfull > xtraback_incre_log 2>&1
第二次增量备份：
xtrabackup --defaults-file=/etc/my.cnf --user=root --password="Admin123..." --socket=/tmp/mysql.sock --backup --lock-ddl=false --lock-ddl-per-table --parallel=2 --target-dir=/lyc/mysql_2_increback_$(date +%Y%m%d%H%M%S) --incremental-basedir=/lyc/mysql_1_increback_$(date +%Y%m%d%H%M%S) > xtraback_2incre_log 2>&1

准备恢复：
xtrabackup --prepare是幂等的，多次执行效果一样，不会有影响。
这是最需要小心的一步。你不能直接使用增量备份文件，必须按顺序将它们“应用”到全量备份上。
原则：全量备份必须用 --apply-log-only 准备；除最后一个增量备份外，其他增量备份在应用时都必须加上 --apply-log-only。
xtrabackup --prepare --apply-log-only --target-dir=/lyc/10mysqlfull
xtrabackup --prepare --apply-log-only --target-dir=/lyc/10mysqlfull --incremental-dir=/lyc/incre_1_mysqlback
xtrabackup --prepare --target-dir=/lyc/10mysqlfull --incremental-dir=/lyc/incre_2_mysqlback

恢复数据：
停止MySQL服务，清空数据目录(mv备份)，数据目录要求mysql用户有读写执行权限(chown -R mysql:mysql /data/mysql)
service mysql stop
rm -rf /data/mysql/
xtrabackup --copy-back --target-dir=/lyc/10mysqlfull --data-dir=/data/mysql
service mysql start

启动失败问题记录：
因为我恢复备份文件到数据目录使用的是root账号(非mysql账号)，数据恢复后，数据目录的文件所有者和所有组是root,所以启动mysql服务时，mysql账户无法在数据目录执行某些操作导致启动失败。
解决：chown -R mysql:mysql /data/mysql
再次启动，成功

结论：数据全量恢复成功
==============*** 重点1：全量备份+增量备份 保证数据完整性 end***===============
==============*** 重点2：最后一次增量备份到mysql出故障停服的这段时间的数据恢复 start***===============
思路：通过binlog日志指定时间范围生成.sql文件，全量+增量恢复绝大部分数据，启动MySQL服务后,执行这部分sql语句。
1、查看binlog文件的信息，找到符合时间范围的binlog文件，stat filename
[root@Rockytest mysql]# stat binlog.000019
File: binlog.000019
Size: 157 Blocks: 8 IO Block: 4096 regular file
Device: fd00h/64768d Inode: 2937275 Links: 1
Access: (0640/-rw-r-----) Uid: ( 1000/ mysql) Gid: ( 1000/ mysql)
Access: 2025-10-10 20:07:53.782234856 +0800 # 最后访问时间
Modify: 2025-10-10 20:07:53.713234859 +0800 # 最后修改时间（内容）
Change: 2025-10-10 20:07:53.713234859 +0800 # 最后改变时间（元数据）
Birth: 2025-10-10 19:59:29.649255091 +0800 # 创建时间（部分系统支持）

2、通过binlog生成.sql文件
----测试的时候，这种方式生成的.sql文件不可用，/usr/local/mysql/bin/mysqlbinlog --start-position=157 -vv --base64-output=decode-rows binlog.000020 > 11binlog.sql
2.1 时间范围只涉及一个binlog文件，我看了插入数据前的pos为157(show master status;),没写--stop-position表示恢复到文件最后。
mysqlbinlog --start-datetime="2025-10-11 09:00:00" binlog.000020 > 11binlog.sql
如果涉及多个binlog文件，用空格隔开即可，如下
mysqlbinlog --start-datetime="2025-10-11 09:00:00" binlog.000019 binlog.000020 binlog.000021 > 11binlog.sql
mysql -uroot -p"Admin123..." < 11binlog.sql #恢复数据
service mysql start #启动服务

查看数据，已经全量恢复
==============*** 重点2：最后一次增量备份到mysql出故障停服的这段时间的数据恢复 end***===============