使用xtrabackup进行备份恢复
概述
Percona XtraBackup 具有以下优点：
- 快速可靠地完成备份
- 备份期间不间断地处理事务
- 节省磁盘空间和网络带宽
- 自动验证备份
Percona XtraBackup 为 Percona Server for MySQL 和兼容 MySQL 的服务器进行热备份。XtraBackup 可进行流式、压缩和增量服务器备份，并支持加密。
功能的简短列表：
- 无需暂停数据库即可创建热 InnoDB 备份
- 对 MySQL 进行增量备份
- 将压缩的 MySQL 备份传输到另一台服务器
- 在线在 MySQL 服务器之间移动表
- 轻松创建新的 MySQL 复制副本
- 备份 MySQL 而不增加服务器负载
- 根据每秒的 IO 操作数进行限制
- 从完整的 InnoDB 备份中导出单个表
注意事项
- 5.7 的对应使用 XtraBackup 2.4
- 8.0 的对应使用 XtraBackup 8.0.xx（注意 8.0.34 前的需要下载对应版本的XtraBackup）
- 8.4 的对应使用 XtraBackup 8.4
安装
apt 方式：
https://docs.percona.com/percona-xtrabackup/8.4/apt-repo.htmlyum方式：
https://docs.percona.com/percona-xtrabackup/8.4/yum-repo.html二进制方式：
https://docs.percona.com/percona-xtrabackup/8.4/binary-tarball.htmlRpm/dbkg 方式下载的路径：
https://www.percona.com/downloads
安装方式有很多种，本例以安装下载的软件包方式安装
下载：
https://www.percona.com/downloads
# 下载
wget -c 'https://downloads.percona.com/downloads/Percona-XtraBackup-8.4/Percona-XtraBackup-8.4.0-1/binary/redhat/8/x86_64/percona-xtrabackup-84-8.4.0-1.1.el8.x86_64.rpm?_gl=1*4hxeka*_gcl_au*ODAwMzA0MTcuMTcyNzM1NTk4NQ..' -O percona-xtrabackup-84-8.4.0-1.1.el8.x86_64.rpm
# 安装,包括一些依赖包
yum localinstall -y percona-xtrabackup-84-8.4.0-1.1.el8.x86_64.rpm备份用户权限
5.7 最小权限
CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';8.x+ 最小权限
CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cr%T';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'localhost';
GRANT SELECT ON performance_schema.keyring_component_status TO bkpuser@'localhost';
GRANT SELECT ON performance_schema.replication_group_members TO bkpuser@'localhost';备份/还原
用到的参数介绍：
- --defaults-file 指定my.cnf路径，仅从给定文件中读取默认选项
- --ftwrl-wait-timeout 指定 xtrabackup 在运行之前应等待[FLUSH TABLES WITH READ LOCK]阻塞查询的时间(秒)
- --backup-lock-timeout 获取元数据锁的超时时间（秒）
- --slave-info 记录源服务器的二进制日志位置（在从库进行备份时）
- --parallel 备份时复制并发数
全量备份还原
备份
xtrabackup --defaults-file=/data/mysql3309/my.cnf --host=localhost --user=bkpuser --password='s3cr%T' --port=3309 --backup --ftwrl-wait-timeout=300 --backup-lock-timeout=300 --slave-info --parallel=2 --target-dir=/data/backup/full20241122还原
# 准备备份
xtrabackup --prepare --target-dir=/data/backup/full20241122/
# 恢复备份
## 1.停止MySQL
systemctl stop mysqld
## 2.删除或移走MySQL数据目录并创建目录
rm -rf /data/mysql3309/data && mkdir /data/mysql3309/data
## 3.将准备好的备份文件放入 data目录
xtrabackup --defaults-file=/data/mysql3309/my.cnf --move-back --target-dir=/data/backup/full20241122
## 4.修改权限
chown -R mysql:mysql /data/mysql3309/data
## 5.启动MySQL
systemctl start mysqld压缩备份还原
备份
增加了 --compress --compress-threads=2
xtrabackup --defaults-file=/data/mysql3309/my.cnf --host=localhost --user=bkpuser --password='s3cr%T' --port=3309 --backup --ftwrl-wait-timeout=300 --backup-lock-timeout=300 --slave-info --compress --compress-threads=2 --parallel=2 --target-dir=/data/backup/full20241122 恢复
# 解压缩
xtrabackup --decompress --decompress-threads=2 --target-dir=/data/backup/full20241122/
# 准备备份
xtrabackup --prepare --target-dir=/data/backup/full20241122/
# 恢复备份
## 1.停止MySQL
systemctl stop mysqld
## 2.删除或移走MySQL数据目录并创建目录
rm -rf /data/mysql3309/data && mkdir /data/mysql3309/data
## 3.将准备好的备份文件放入 data目录
xtrabackup --defaults-file=/data/mysql3309/my.cnf --move-back --target-dir=/data/backup/full20241122
## 4.修改权限
chown -R mysql:mysql /data/mysql3309/data
## 5.启动MySQL
systemctl start mysqld流式备份
备份
备份到本地
需要指定 --target-dir 用于中间状态文件
xtrabackup --defaults-file=/data/mysql3309/my.cnf --host=localhost --user=bkpuser --password='s3cr%T' --port=3309 --backup --ftwrl-wait-timeout=300 --backup-lock-timeout=300 --slave-info --parallel=2 --compress --compress-threads=2 --stream --target-dir=/data/backup > /data/backup/xtra_full_`date +%Y%m%d%H%M%S`.xbstream备份到远程机器
# 备份到远程服务器，需要先做下免密，否则需要输密码
xtrabackup --defaults-file=/data/mysql3309/my.cnf --host=localhost --user=bkpuser --password='s3cr%T' --port=3309 --backup --ftwrl-wait-timeout=300 --backup-lock-timeout=300 --slave-info --parallel=2 --compress --compress-threads=2 --stream=xbstream | ssh root@initnode "cat - > /data/backup/xtra_full_`date +%Y%m%d%H%M%S`.xbstream"恢复
--remove-original 在解密和解压缩后删除.qp、.xbcrypt和.qp.xbcrypt文件
# 解包
mkdir -p /data/backup/data
cat /data/backup/xtra_full_20210127173747.xbstream | xbstream -x -v -C /data/backup/data
# 由于开启了压缩，需要进行解压缩
xtrabackup --decompress --remove-original --target-dir=/data/backup/data
# 继续恢复备份的操作（停止MySQL,处理原数据，拷贝备份数据到数据目录等操作）增量备份
建议： 增量备份总是基于上一次全量备份的基础，不要基于上一次增量备份进行增备，这样在还原时会更方便。
备份
要进行增量备份,首先需要有一个全量备份
xtrabackup --defaults-file=/data/mysql3309/my.cnf --host=localhost --user=bkpuser --password='s3cr%T' --port=3309 --backup --ftwrl-wait-timeout=300 --backup-lock-timeout=300 --slave-info --compress --compress-threads=2 --parallel=2 --target-dir=/data/backup/full20241122 基于全备进行增量备份，以下示例是基于已存在的全备full20241122进行的增量备份
xtrabackup --defaults-file=/data/mysql3309/my.cnf --host=localhost --user=bkpuser --password='s3cr%T' --port=3309 --backup --ftwrl-wait-timeout=300 --backup-lock-timeout=300 --slave-info --parallel=2 --target-dir=/data/backup/inc20241123 --incremental-basedir=/data/backup/full20241122当然，也可以基于增量备份继续进行增量备份
xtrabackup --defaults-file=/data/mysql3309/my.cnf --host=localhost --user=bkpuser --password='s3cr%T' --port=3309 --backup --ftwrl-wait-timeout=300 --backup-lock-timeout=300 --slave-info --parallel=2 --target-dir=/data/backup/inc20241124 --incremental-basedir=/data/backup/inc20241123恢复
增量备份的步骤--prepare与完整备份不同。在完整备份中，将执行两种类型的操作来使数据库保持一致：已提交的事务从日志文件中针对数据文件重播，未提交的事务将回滚。在准备增量备份时，必须跳过未提交事务的回滚，因为备份时未提交的事务可能正在进行中，并且很可能会在下一次增量备份中提交。您应该使用该 --apply-log-only选项来阻止回滚阶段。
存在全量+增量的备份：
full20241122 全备
inc20241123 基于全备的第一次增备
inc20241124 基于上一次增备的增量备份
# 先应用完整备份，但要阻止回滚阶段
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full20241122/
# 将第一个增量备份应用于完整备份，如果不是最后一个增量备份，需要要阻止回滚阶段
# 最终数据在 中 /data/backups/full20241122，而不是在增量目录中
xtrabackup --prepare --target-dir=/data/backup/full20241122/ --incremental-dir=/data/backup/inc20241123
# 将第二个增量备份应用于完整备份，由于这边是指定恢复到这个增备，因此到这截止，不需要加上 --apply-log-only
xtrabackup --prepare --target-dir=/data/backup/full20241122/ --incremental-dir=/data/backup/inc20241124
# 继续进行上述恢复备份的操作（停止MySQL,处理原数据，拷贝备份数据到数据目录等操作）部分备份
当启用innodb_file_per_table该选项时，可以使用 xtrabackup 进行指定数据的部分备份。用于快速备份恢复。
警告
* 恢复部分备份应通过导入表来完成。我们不使用该`–copy-back`选项。此操作可能会导致数据库不一致。
* 不建议在进行部分备份后运行增量备份。
* 对于 tables 选项导出的需要使用导入表来完成
备份
有多种方法可以指定备份整个数据的哪一部分：
- 使用--tables选项列出表名称
# 仅备份test数据库中的表
xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/data/backups/ --tables="^test[.].*"
# 仅备份test.t1表
xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/data/backups/ --tables="^test[.]t1"- 使用--tables-file选项列出文件中的表
# 备份 db.tb01，可以包含多个表名的文件，文件中每行一个表名
echo "db.tb01" > /tmp/tables.txt
xtrabackup --backup --tables-file=/tmp/tables.txt- 使用该--databases选项列出数据库
- 请确保指定mysql、sys和 performance_schema，使用 xtrabackup –copy-back 恢复数据库时需要这些数据库
- 单库的恢复时候会显示其他库表信息，单无法查看其他库表的结构与数据。
# ` –databases` 选项接受要备份的数据库和表的空格分隔列表，格式如下databasename[.tablename]。除了此列表之外，请确保指定mysql、sys和 performance_schema
xtrabackup --backup --databases='mysql sys performance_schema test ...'- 使用该--databases-file选项列出数据库
- 可以包含多个库表名的文件，文件中每行一个库表名
xtrabackup --backup --databases=/tmp/tables.txt恢复
当您在部分备份上使用该--prepare选项时，您将看到有关不存在的表的警告。这是因为这些表存在于 InnoDB 内部的数据字典中，但相应的 .ibd 文件不存在。它们未被复制到备份目录中。这些表将从数据字典中删除，当您恢复备份并启动 InnoDB 时，它们将不再存在，并且不会导致任何错误或警告打印到日志文件中。
# 应用日志并使用以下 --export选项：
xtrabackup --prepare --export --target-dir=/path/to/partial/backup通过执行以下操作来导入表：
- 创建表的副本
- 丢弃表的原始表空间
- 将.ibd和.cfg文件复制到以下位置
- 导入表空间
# 1. 目标库创建具有相同结构的表。
# 2.运行ALTER TABLE ... DISCARD TABLESPACE命令以丢弃新表的表空间：
ALTER TABLE test.export_test DISCARD TABLESPACE;
# 3.将导出的文件复制到test/目标服务器的数据目录的子目录中。
# 4.执行导入 ALTER TABLE ... IMPORT TABLESPACE
ALTER TABLE test.export_test IMPORT TABLESPACE;基于时间点的恢复
1.正常恢复全备
2.查看增量的binlog
mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004 \
--start-position=57 > mybinlog.sql3.恢复增量的数据
# 使用查询检查文件以确定哪个位置或日期对应于所需的时间点。确定后，将其传送到服务器。例如，如果该点是11-12-25 01:00:00，则命令将数据库向前移动到该时间点。
mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004 \
--start-position=57 --stop-datetime="11-12-25 01:00:00" | mysql -u root -p加密备份
使用 xtrabackup 设置副本
1.进行全备
xtrabackup --defaults-file=/data/mysql3309/my.cnf --host=localhost --user=bkpuser --password='s3cr%T' --port=3309 --backup --ftwrl-wait-timeout=300 --backup-lock-timeout=300 --slave-info --parallel=2 --target-dir=/data/backup/full202411222.将备份数据复制到副本
scp -r /data/backup/full20241122 root@10.10.2.17:/data3.执行恢复
# 准备备份
xtrabackup --prepare --target-dir=/data/backup/full20241122/
# 恢复备份
## 1.停止MySQL
systemctl stop mysqld
## 2.删除或移走MySQL数据目录并创建目录
rm -rf /data/mysql3309/data && mkdir /data/mysql3309/data
## 3.将准备好的备份文件放入 data目录
xtrabackup --defaults-file=/data/mysql3309/my.cnf --move-back --target-dir=/data/backup/full20241122
## 4.修改权限
chown -R mysql:mysql /data/mysql3309/data
## 5.启动MySQL
systemctl start mysqld4.查看二进制日志坐标和GTID信息
cat xtrabackup_binlog_info5.配置并启动复制
RESET BINARY LOGS AND GTIDS; 或 RESET MASTER; (< 8.4)
SET GLOBAL gtid_purged='<gtid_string_found_in_xtrabackup_binlog_info>';
CHANGE REPLICATION SOURCE TO
SOURCE_HOST="$sourceip",
SOURCE_USER="repl",
SOURCE_PASSWORD="$replicapass",
SOURCE_AUTO_POSITION = 1;
START REPLICA;
SHOW REPLICA STATUS\G原理解析
xtraback 2.4备份原理
可观察 general 日志与 xtrabackup的备份日志
- 连接mysql进行版本检查。
- 通过读取配置文件，获取数据和日志文件位置。
- 启动redo后台备份线程，从备份实例的最近一次checkpoint LSN的位置开始备份所有增量的redo log，有新的redo log就拷贝到xtrabackup的logfile中，持续进行监控拷贝。
- 执行FLUSH TABLES WITH READ LOCK (FTWRL)，取得一致性位点
- 开始备份非 InnoDB 文件（包括 frm、MYD、MYI、CSV、opt、par等）。拷贝非 InnoDB 文件过程中，数据库处于全局只读状态
- 释放全局读锁。
- 记录binlog信息等，备份结束。
mysql> select event_time,thread_id,left(argument, 64) from mysql.general_log;
+----------------------------+-----------+------------------------------------------------------------------+
| event_time | thread_id | left(argument, 64) |
+----------------------------+-----------+------------------------------------------------------------------+
| 2019-08-26 18:06:04.788686 | 86 | databak@localhost on using Socket |
| 2019-08-26 18:06:04.792355 | 86 | SET SESSION wait_timeout=2147483 |
| 2019-08-26 18:06:04.792670 | 86 | SELECT CONCAT(@@hostname, @@port) |
| 2019-08-26 18:06:04.795392 | 86 | |
| 2019-08-26 18:06:04.795887 | 87 | databak@localhost on using Socket |
| 2019-08-26 18:06:04.796144 | 87 | SET SESSION wait_timeout=2147483 |
| 2019-08-26 18:06:04.796225 | 87 | SET SESSION autocommit=1 |
| 2019-08-26 18:06:04.796299 | 87 | SET NAMES utf8 |
| 2019-08-26 18:06:04.796428 | 87 | SHOW VARIABLES |
| 2019-08-26 18:06:04.799345 | 87 | SHOW ENGINE INNODB STATUS |
| 2019-08-26 18:06:04.800803 | 87 | SELECT PLUGIN_NAME, PLUGIN_LIBRARY FROM information_schema.plugi |
| 2019-08-26 18:06:04.801509 | 87 | SELECT
CONCAT(table_schema, '/', table_name), engine
FROM info |
| 2019-08-26 18:06:11.690723 | 87 | SET SESSION lock_wait_timeout=31536000 | # 设置锁等待时间是为了防止Xtrabackup执行获取全局锁超时而导致备份失败退出。
| 2019-08-26 18:06:11.690956 | 87 | FLUSH NO_WRITE_TO_BINLOG TABLES | # 关闭打开的表
| 2019-08-26 18:06:11.695217 | 87 | FLUSH TABLES WITH READ LOCK | # 锁住所有的表阻塞写操作
| 2019-08-26 18:06:14.022408 | 87 | SHOW MASTER STATUS |
| 2019-08-26 18:06:14.027833 | 87 | SHOW VARIABLES |
| 2019-08-26 18:06:14.037243 | 87 | FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS | # 将innodb层的重做日志持久化到磁盘，xtraback不备份binlog,若不刷盘，可能导致最后一组事务恢复时回滚丢失
| 2019-08-26 18:06:14.249876 | 87 | UNLOCK TABLES |
| 2019-08-26 18:06:14.265571 | 87 | SELECT UUID() |
| 2019-08-26 18:06:14.265808 | 87 | SELECT VERSION() |
| 2019-08-26 18:06:14.479985 | 87 | |
| 2019-08-26 18:06:18.226737 | 82 | select event_time,thread_id,left(argument, 64) from general_log |
+----------------------------+-----------+------------------------------------------------------------------+
23 rows in set (0.00 sec)原理说白了就是在所有的事务表和非事务表备份完成，获取全局读锁，且使用了show master status语句获取了binlog的pos之后，执行刷新redo log buffer中的日志到磁盘中，然后redo log copy线程拷贝这最后的redo log日志数据。为啥这样数据就是完整的？因为获取了全局读锁到unlock tables释放之前，不会再有请求进来。
xtraback 8.0备份过程
mysql> set global log_output='table';
mysql> set global general_log=1;
mysql> truncate table mysql.general_log;
# 执行备份脚本
shell> xtrabackup --defaults-file=/etc/my.cnf --backup --parallel=2 --user=databak --password=1122333 --target-dir=/test/xtraback/`date '+%Y%m%d%H%M%S'`_full
mysql> set global general_log=0;
# 由于8.0 argument字段改成了mediumblob,看起来不方便，这边改成text下
mysql> alter table mysql.general_log modify column argument text not null;
[root@mysql.sock][yqtest]> select event_time,thread_id,left(argument, 150) from mysql.general_log;
+----------------------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| event_time | thread_id | left(argument, 150) |
+----------------------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2021-09-22 22:38:02.680648 | 9 | bkpuser@127.0.0.1 on using TCP/IP |
| 2021-09-22 22:38:02.688610 | 9 | SET SESSION wait_timeout=2147483 |
| 2021-09-22 22:38:02.689292 | 9 | SELECT CONCAT(@@hostname, @@port) |
| 2021-09-22 22:38:02.690200 | 9 | |
| 2021-09-22 22:38:02.714044 | 10 | bkpuser@127.0.0.1 on using SSL/TLS |
| 2021-09-22 22:38:02.717016 | 10 | SET SESSION wait_timeout=2147483 |
| 2021-09-22 22:38:02.717539 | 10 | SET SESSION autocommit=1 |
| 2021-09-22 22:38:02.717942 | 10 | SET NAMES utf8 |
| 2021-09-22 22:38:02.718320 | 10 | SHOW VARIABLES |
| 2021-09-22 22:38:02.721699 | 10 | SELECT COUNT(*) FROM information_schema.tables WHERE engine = 'MyISAM' OR engine = 'RocksDB' |
| 2021-09-22 22:38:02.736630 | 10 | SHOW ENGINES |
| 2021-09-22 22:38:02.737913 | 10 | SET SESSION lock_wait_timeout=31536000 |
| 2021-09-22 22:38:02.738255 | 10 | LOCK INSTANCE FOR BACKUP |
| 2021-09-22 22:38:02.738608 | 10 | SHOW ENGINE INNODB STATUS |
| 2021-09-22 22:38:02.741625 | 10 | SELECT * FROM performance_schema.keyring_component_status |
| 2021-09-22 22:38:02.745960 | 10 | SELECT PLUGIN_NAME, PLUGIN_LIBRARY FROM information_schema.plugins WHERE PLUGIN_STATUS = 'ACTIVE' AND PLUGIN_TYPE = 'KEYRING' |
| 2021-09-22 22:38:02.752015 | 10 | SELECT CONCAT(table_schema, '/', table_name), engine FROM information_schema.tables WHERE engine NOT IN ('MyISAM', 'InnoDB', 'CSV', 'MRG_MYISAM', 'RO |
| 2021-09-22 22:38:03.016930 | 11 | bkpuser@127.0.0.1 on using SSL/TLS |
| 2021-09-22 22:38:03.017381 | 11 | SET SESSION wait_timeout=2147483 |
| 2021-09-22 22:38:03.017960 | 11 | SET SESSION autocommit=1 |
| 2021-09-22 22:38:03.018129 | 11 | SET NAMES utf8 |
| 2021-09-22 22:38:03.018297 | 11 | SHOW VARIABLES |
| 2021-09-22 22:38:03.024539 | 11 | |
| 2021-09-22 22:38:03.111602 | 10 | SELECT T2.PATH, T2.NAME, T1.SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES T1 JOIN INFORMATION_SCHEMA.INNODB_TABLESPACES |
| 2021-09-22 22:38:04.447428 | 10 | FLUSH NO_WRITE_TO_BINLOG BINARY LOGS |
| 2021-09-22 22:38:04.455311 | 10 | SELECT server_uuid, local, replication, storage_engines FROM performance_schema.log_status |
| 2021-09-22 22:38:04.456571 | 10 | SHOW VARIABLES |
| 2021-09-22 22:38:04.460343 | 10 | SHOW VARIABLES |
| 2021-09-22 22:38:04.463204 | 10 | FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS |
| 2021-09-22 22:38:05.471839 | 10 | UNLOCK INSTANCE |
| 2021-09-22 22:38:05.474345 | 10 | SELECT UUID() |
| 2021-09-22 22:38:05.474661 | 10 | SELECT VERSION() |
| 2021-09-22 22:38:06.683480 | 10 | |
| 2021-09-22 22:38:43.231153 | 8 | set global general_log=0 |
+----------------------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
34 rows in set (0.00 sec)