MySQL物理备份-Percona XtraBackup 8.0 操作说明

Xtrabackup 8.0 说明

官方文档：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html 简单过程说明

1. start backup,Executing LOCK INSTANCE FOR BACKUP.
2. copy .ibd file
3. backup non-InnoDB tables and files
4. Executed FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
5. Selecting LSN and binary log position from perfomance_schema.log_status
6. copy last binlog file
7. Writing /root/lpb/mysql_backup/full/mysql-bin.index
8. Writing xtrbackup_binlog_info
9. Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
10. copy ib_buffer_pool
11. completed OK!

由以上步骤可知， XtraBackup 8.0 对 MySQL 8.0的备份与 XtraBackup 2.4 略有不同，根据 Percona 官方文档的信息，MySQL 8.0 不再执行 FTWRL（当使用 --slave-info 启动 xtrabackup 时，MySQL 8.0 中仍然需要 FTWRL） ,而是在备份开始执行一个 LOCK INSTANCE FOR BACKUP 获取备份锁, XtraBackup 8.0 会通过以下 SQL 语句从 performance_schema.log_status来获取 LSN、binlog position 和 GTID

mysql> SELECT server_uuid, local, replication, storage_engines FROM performance_schema.log_status\G
*************************** 1. row ***************************
    server_uuid: 5b07de60-66d1-11eb-a073-5254003716cd
          local: {"gtid_executed": "5b07de60-66d1-11eb-a073-5254003716cd:1-64", "binary_log_file": "mysql-bin.000013", "binary_log_position": 196}
    replication: {"channels": []}
storage_engines: {"InnoDB": {"LSN": 315193449, "LSN_checkpoint": 315193449}}

官方文档：https://dev.mysql.com/doc/mysql-perfschema-excerpt/8.0/en/performance-schema-log-status-table.html performance_schema.log_status 是 MySQL 8.0 提供给在线备份工具获取复制日志文件信息的视图，查询 log_status 表时，服务器将阻止日志的记录和相关的更改来获取足够的时间以填充该表，然后释放资源。Log_status 表通知在线备份工具应记录主库 binlog 的 position 值和 gtid_executed值，及每个通道复制的 relay log ，它还为各个存储引擎提供相关信息，例如 InnoDB 存储引擎使用的最后一个日志序列号 LSN，和最后一个检查点的 LSN。

PXB准备工作

PXB 工具安装

# 安装依赖包
$ yum install -y rsync perl l perl-Digest-MD5 perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
# 在官网下下载对应的PXB版本，这边测试环境是8.0.23
$ wget wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.23-16/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.23-16.1.el7.x86_64.rpm
$ yum -y install ./percona-xtrabackup-80-8.0.23-16.1.el7.x86_64.rpm 
$ xtrabackup -version
xtrabackup: recognized server arguments: --server-id=1 --datadir=/data/mysql --innodb_file_per_table=1 --log_bin=/var/log/mysql/mysql-bin --innodb_directories=/opt/data8.0/tmpdata 
xtrabackup version 8.0.23-16 based on MySQL server 8.0.23 Linux (x86_64) (revision id: 934bc8f)

参数说明

$ xtrabackup --help  # 现在新版的PXB工具中 innobackupex 工具已经合并到xtrabackup二进制包中
--target-dir=name               destination directory 全量备份存放目录
--backup                        take backup to target-dir 全量备份然后配合参数 --target-dir 指定全备目录
--defaults-file                 待备份mysql实例的配置文件
--user=USER                     备份用户，默认为当前系统用户,建议使用备份专用账号
--password=PASSWD               备份用户密码
--port=PORT                     数据库端口，默认3306
--host=HOST                     需备份数据库的IP
-S, --socket=name               MySQL实例的socket存放路径
--apply-log                     前滚、回滚日志，恢复前用
--database                      指定需要备份的数据库，多个数据库之间以空格分开
--copy-back                     将备份数据复制到目标路径
--incremental                   增量备份，后面接的参数值为指定的增量备份的路径
--incremental-basedir=DIRECTORY 增量备份时使用指向上一次的增量备份所在的目录
--incremental-dir=DIRECTORY     增量备份还原的时候用来合并增量备份到全量，用来指定全备路径
--redo-only                     对增量备份进行合并，在prepare阶段使用
--rsync                         加快本地文件传输，适用于non-InnoDB数据库引擎。不与--stream共用
--no-timestamp                  生成的备份文件不以时间戳为目录  # PXB8.0以后参数废弃不用

创建备份账号

备份账号建议单独创建，并授予相关权限官方文档权限说明：https://www.percona.com/doc/percona-xtrabackup/LATEST/using_xtrabackup/privileges.html

mysql> CREATE USER 'pxbuser'@'localhost' IDENTIFIED BY '1234';
mysql> GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'pxbuser'@'localhost';
mysql> GRANT SELECT ON performance_schema.log_status TO 'pxbuser'@'localhost';
mysql> FLUSH PRIVILEGES;

权限对应说明

# 数据库层面：
1.RELOAD,LOCK TABLES，这些权限用于实现备份锁，例如执行flush tables with read lock和flush engine logs
2.BACKUP_ADMIN，此权限用于查询performance_schema.log_status，和执行lock instance for backup,lock binlog for backup,lock tables for backup，针对MySQL8.0
3.replication client，用于查看binlog位点，拥有该权限可以执行show master status,show slave status,show binary logs
4.create tablespace，此权限用于导入表，在恢复单表或表级恢复时需要用到
5.process，用于执行show engine innodb status和show processlist
6.super，此权限用于控制复制线程
7.create，用于创建percona_schema.xtrabackup_history
8.insert，用于插入percona_schema.xtrabackup_history
9.select，用于在使用–incremental-history-name或–incremental-history-uuid时在PERCONA_SCHEMA.xtrabackup_history查询innodb_to_lsn的值

MySQL 8.0 增加了一个轻量级的备份锁，这个锁可以保证备份一致性，而且阻塞的操作相对比较少，PXB 8.0 备份就引用了这个特性 PXB官方文档说明：https://www.percona.com/doc/percona-server/8.0/management/backup_locks.html

备份过程说明

开始全量备份

# 创建备份文件存储目录
$ mkdir ./lpb/mysql_backup
# 在MySQL实例配置文件中指定socket文件位置
$ vim /etc/my.cnf
[client]
port=3306
socket=/var/log/mysql/mysql.sock

# 开始全量备份
$ xtrabackup --defaults-file=/etc/my.cnf --user=pxbuser --password=1234 --no-timestamp  --backup --target-dir=./lpb/mysql_backup/full
$ tree  -L 1 ./lpb/mysql_backup/full/
./lpb/mysql_backup/full/
-- backup-my.cnf
-- ib_buffer_pool
-- ibdata1
-- #innodb_temp
-- mysql
-- mysql-bin.000013
-- mysql-bin.index
-- mysql.ibd
-- performance_schema
-- sys
-- test_binlog
-- undo_001
-- undo_002
-- world
-- xtrabackup_binlog_info
-- xtrabackup_checkpoints
-- xtrabackup_info
-- xtrabackup_logfile
`-- xtrabackup_tablespaces

可以看到备份文件已经在目录下，注意这里和 innobackupex 不一样，xtrabackup 备份不会建立一个当前时间命名的文件件 --no-timestamp 参数已经废弃

命令提示部分

$ xtrabackup --defaults-file=/etc/my.cnf --user=pxbuser --password=1234   --backup --target-dir=./lpb/mysql_backup/full
xtrabackup: recognized server arguments: --server-id=1 --datadir=/data/mysql --innodb_file_per_table=1 --log_bin=/var/log/mysql/mysql-bin --innodb_directories=/opt/data8.0/tmpdata 
xtrabackup: recognized client arguments: --port=3306 --socket=/var/log/mysql/mysql.sock --user=pxbuser --password=* --backup=1 --target-dir=./lpb/mysql_backup/full 
xtrabackup version 8.0.23-16 based on MySQL server 8.0.23 Linux (x86_64) (revision id: 934bc8f)
210817 11:35:56  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/var/log/mysql/mysql.sock' as 'pxbuser'  (using password: YES).

上述日志打印出 innobackpex 可以用的一些参数

连接数据库

210817 11:35:56 Connecting to MySQL server host: localhost, user: pxbuser, password: set, port: 3306, socket: /var/log/mysql/mysql.sock
Using server version 8.0.23 # 数据库版本
210817 11:35:56 Executing LOCK INSTANCE FOR BACKUP... # PXB8.0中使用了MySQL8.0备份锁
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql
xtrabackup: open files limit requested 0, set to 100001
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
Number of pools: 1
210817 11:35:56 Connecting to MySQL server host: localhost, user: pxbuser, password: set, port: 3306, socket: /var/log/mysql/mysql.sock

连接数据库，检查数据库版本，直接加MDL 备份锁，进入数据文件目录检查参数，开始备份。

备份系统表空间及InnoDB引擎数据

xtrabackup: Redo Log Archiving is not set up.  # 这个提示是因为MySQL8.0.17开始支持redo日志归档，当前实例未开启
210817 11:27:46 >> log scanned up to (315193409)  
xtrabackup: Generating a list of tablespaces
xtrabackup: Generating a list of tablespaces
Scanning './'
Scanning '/opt/data8.0/tmpdata/'
Completed space ID check of 2 files.
Allocated tablespace ID 106 for test_binlog/sync_test, old maximum was 0
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
210817 11:35:57 [01] Copying ./ibdata1 to /root/lpb/mysql_backup/full/ibdata1
210817 11:35:57 [01]        ...done
210817 11:35:57 [01] Copying ./sys/sys_config.ibd to /root/lpb/mysql_backup/full/sys/sys_config.ibd
210817 11:35:57 [01]        ...done
210817 11:35:57 [01] Copying ./test_binlog/sync_test.ibd to /root/lpb/mysql_backup/full/test_binlog/sync_test.ibd
210817 11:35:57 [01]        ...done
210817 11:35:57 [01] Copying ./world/city.ibd to /root/lpb/mysql_backup/full/world/city.ibd
210817 11:35:57 [01]        ...done
210817 11:35:57 [01] Copying ./world/country.ibd to /root/lpb/mysql_backup/full/world/country.ibd
210817 11:35:57 [01]        ...done
210817 11:35:57 [01] Copying ./world/countrylanguage.ibd to /root/lpb/mysql_backup/full/world/countrylanguage.ibd
210817 11:35:57 [01]        ...done
210817 11:35:57 [01] Copying ./mysql.ibd to /root/lpb/mysql_backup/full/mysql.ibd
210817 11:35:57 [01]        ...done
210817 11:35:57 [01] Copying ./undo_002 to /root/lpb/mysql_backup/full/undo_002
210817 11:35:57 [01]        ...done
210817 11:35:57 [01] Copying ./undo_001 to /root/lpb/mysql_backup/full/undo_001
210817 11:35:57 [01]        ...done
210817 11:35:57 >> log scanned up to (315193429)

这部分首先列出数据库中有哪些表空间

系统表空间（ibdata）
undo 表空间（undo）
用户表空间（innodb_file_per_table开启后每张表和表空间一一对应）

首先拷贝系统表空间然后是undo表空间文件，最后是innodb表文件。 xtrabackup 一直在扫描 redo 日志文件以反映数据的变化。

备份非 innodb表数据

210817 11:35:58 Starting to backup non-InnoDB tables and files  # 这一步并没有加FTWRL锁，因为使用了 LOCK TABLE FOR BACKUP
210817 11:35:58 [00] Writing /root/lpb/mysql_backup/full/#innodb_temp/db.opt
210817 11:35:58 [00]        ...done
210817 11:35:58 [01] Copying mysql/general_log.CSV to /root/lpb/mysql_backup/full/mysql/general_log.CSV
210817 11:35:58 [01]        ...done
210817 11:35:58 [01] Copying mysql/slow_log.CSV to /root/lpb/mysql_backup/full/mysql/slow_log.CSV
210817 11:35:58 [01]        ...done
....
210817 11:35:58 Finished backing up non-InnoDB tables and files

拷贝二进制文件

210817 11:35:58 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS   # 切换binlog序列号
210817 11:35:58 Selecting LSN and binary log position from p_s.log_status # 查看performance_schema.log_status 信息
210817 11:35:58 [00] Copying /var/log/mysql/mysql-bin.000013 to /root/lpb/mysql_backup/full/mysql-bin.000013 up to position 196
210817 11:35:58 [00]        ...done
210817 11:35:58 [00] Writing /root/lpb/mysql_backup/full/mysql-bin.index
210817 11:35:58 [00]        ...done
210817 11:35:58 [00] Writing /root/lpb/mysql_backup/full/xtrabackup_binlog_info
210817 11:35:58 [00]        ...done
210817 11:35:58 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...  # 拷贝完切换的二进制日志后，再次切换日志，保证数据的一致性
xtrabackup: The latest check point (for incremental): '315193429'
xtrabackup: Stopping log copying thread at LSN 315193429.
Starting to parse redo log at lsn = 315193389

根据 performance_schema.log_status 来拷贝备份非 InnoDB 时期的事务情况保证数据一致性

完成备份

210817 11:35:58 Executing UNLOCK INSTANCE  # 解锁备份锁
210817 11:35:58 All tables unlocked # 解锁所有表
210817 11:35:58 [00] Copying ib_buffer_pool to /root/lpb/mysql_backup/full/ib_buffer_pool  # 拷贝idb_buffer_pool文件
210817 11:35:58 [00]        ...done
210817 11:35:58 Backup created in directory '/root/lpb/mysql_backup/full/'
MySQL binlog position: filename 'mysql-bin.000013', position '196', GTID of the last change '5b07de60-66d1-11eb-a073-5254003716cd:1-64'
210817 11:35:58 [00] Writing /root/lpb/mysql_backup/full/backup-my.cnf # 记录binlog位置信息
210817 11:35:58 [00]        ...done
210817 11:35:58 [00] Writing /root/lpb/mysql_backup/full/xtrabackup_info
210817 11:35:58 [00]        ...done
xtrabackup: Transaction log of lsn (315193429) to (315193439) was copied. # 最后拷贝在备份期间变化的redo日志
210817 11:36:00 completed OK!

PXB 生成文件说明

backup-my.cnf

$ cat ./lpb/mysql_backup/full/backup-my.cnf 
# This MySQL options file was generated by innobackupex.

# The MySQL server
[mysqld]
innodb_checksum_algorithm=crc32
innodb_log_checksums=1
innodb_data_file_path=ibdata1:12M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=50331648
innodb_page_size=16384
innodb_undo_directory=./
innodb_undo_tablespaces=2
server_id=1      # 数据库的server ID
innodb_log_checksums=ON
innodb_redo_log_encrypt=OFF
innodb_undo_log_encrypt=OFF
server_uuid=5b07de60-66d1-11eb-a073-5254003716cd  # 数据库的UUID
master_key_id=0

xtrabackup_binlog_info

$ cat ./lpb/mysql_backup/full/xtrabackup_binlog_info  # 记录备份完成时 binlog 的位置及GTID信息
mysql-bin.000013        196     5b07de60-66d1-11eb-a073-5254003716cd:1-64

xtrabackup_checkpoints

$ cat ./lpb/mysql_backup/full/xtrabackup_checkpoints 
backup_type = full-backuped  # 备份方式
from_lsn = 0                 # 开始的LSN号
to_lsn = 315193429           # 结束的LSN号，用于增量备份的起点
last_lsn = 315193429         # 最后检查点LSN号，用户最后恢复的位置
flushed_lsn = 315193429      # 最终拷贝的LSN号

xtrbackup_info

$ cat ./lpb/mysql_backup/full/xtrabackup_info  # xtrabackup 工具备份信息
uuid = 3cc57583-ff0c-11eb-a10c-5254003716cd
name = 
tool_name = xtrabackup
tool_command = --defaults-file=/etc/my.cnf --user=pxbuser --password=... --backup --target-dir=./lpb/mysql_backup/full
tool_version = 8.0.23-16
ibbackup_version = 8.0.23-16
server_version = 8.0.23
start_time = 2021-08-17 11:35:56
end_time = 2021-08-17 11:35:58
lock_time = 0
binlog_pos = filename 'mysql-bin.000013', position '196', GTID of the last change '5b07de60-66d1-11eb-a073-5254003716cd:1-64'
innodb_from_lsn = 0
innodb_to_lsn = 315193429
partial = N
incremental = N
format = file
compressed = N
encrypted = N

database > MySQL

MySQL物理备份-Percona XtraBackup 8.0 操作说明

http://www.qiqios.cn/2021/08/17/mysql物理备份-percona-xtrabackup-8-0-操作说明/

作者

一亩三分地

发布于

2021年8月17日

许可协议