mysql xtrabackup 简介

最新推荐文章于 2023-11-14 13:36:04 发布

o_瓜田李下_o

最新推荐文章于 2023-11-14 13:36:04 发布

阅读量526

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43931625/article/details/117389058

版权

mysql 专栏收录该内容

28 篇文章 0 订阅

订阅专栏

mysql xtrabackup 简介

官网：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html

*********************

xtrabackup 简介

xtrabackup是开源的mysql数据备份工具，在数据备份期间不会锁定数据库

Percona XtraBackup is an open-source hot backup utility for MySQL 
- based servers that doesn’t lock your database during the backup

xtrabackup支持备份mysql、percona数据库

完全非阻塞备份：InnoDB、XtraDB、MyRocks

备份期间暂停写入：MyISAM、Merge、Archive

Percona XtraBackup works with MySQL and Percona Server for MySQL. It supports completely 
non-blocking backups of InnoDB, XtraDB, and MyRocks storage engines. In addition, it can 
back up the following storage engines by briefly pausing writes at the end of the backup: 
MyISAM, Merge, and Archive, including partitioned tables, triggers, and database options

xtrabackup 8不支持备份低于8版本的mysql数据库、percona数据库

Percona XtraBackup 8.0 does not support making backups of databases created in versions 
prior to 8.0 of MySQL, Percona Server for MySQL or Percona XtraDB Cluster. As the changes 
that MySQL 8.0 introduced in data dictionaries, redo log and undo log are incompatible with 
previous versions, it is currently impossible for Percona XtraBackup 8.0 to also support versions prior to 8.0.

xtrabackup 8.0.11及以下版本不支持mysql 8.0.20及以上版本

Due to changes in MySQL 8.0.20 released by Oracle at the end of April 2020, Percona 
XtraBackup 8.0, up to version 8.0.11, is not compatible with MySQL version 8.0.20 or 
higher, or Percona products that are based on it: Percona Server for MySQL and Percona XtraDB Cluster

xtrabackup 8.0.12可支持mysql 8.0.21及以上版本

Percona XtraBackup 8.0.12 now supports backup and restore processing for versions of MySQL 8.x; 
previous versions of Percona XtraBackup will not work with MySQL 8.0.20 and higher

备份percona服务器上non-InnoDB（非InnoDB存储引擎）上的数据，会使用更轻量级的锁Backup locks替换 flush tables with read lock

Backup locks is a lightweight alternative to FLUSH TABLES WITH READ LOCK available in Percona Server for MySQL. 
Percona XtraBackup uses them automatically to copy non-InnoDB data to avoid blocking DML queries that modify InnoDB tables

*********************

备份原理

strabackup基于InnoDB崩溃恢复功能实现：复制InnoDB数据文件，这些数据文件中的数据如果存在不一致，则进行数据恢复

Percona XtraBackup is based on InnoDB‘s crash-recovery functionality. It copies your InnoDB 
data files, which results in data that is internally inconsistent; but then it performs 
crash recovery on the files to make them a consistent, usable database again.

InnoDB维护了一个redo log（重做日志），该日志记录InnoDB数据变更；当InnoDB重启时，会检查数据文件和redo log，分情况执行以下操作：

事务提交：保存数据文件中事务操作造成的数据变更

事务没有提交：回滚数据文件中事务操作造成的数据变更

This works because InnoDB maintains a redo log, also called the transaction log. This 
contains a record of every change to InnoDB data. When InnoDB starts, it inspects the data 
files and the transaction log, and performs two steps. It applies committed transaction log 
entries to the data files, and it performs an undo operation on any transactions that 
modified data but did not commit

xtrabackup启动时，会记录日志序列号（LSN），同时复制InnoDB数据文件

Percona XtraBackup works by remembering the log sequence number (LSN) when it starts, and 
then copying away the data files. It takes some time to do this, so if the files are 
changing, then they reflect the state of the database at different points in time.

xtrabackup会在后台启动进程监听事务日志文件（redo log），并复制日志文件变化（复制日志变化需要持续进行，因为redo log可以重用）

At the same time, Percona XtraBackup runs a background process that watches the transaction 
log files, and copies changes from it. Percona XtraBackup needs to do this continually 
because the transaction logs are written in a round-robin fashion, and can be reused after 
a while. Percona XtraBackup needs the transaction log records for every change to the data 
files since it began execution

InnoDB、XtraDB复制完成后，如果还需要备份non_InnoDB（如MyISAM）数据，需要加锁

如果数据库中只有InnoDB存储的数据，则不会加锁

Percona XtraBackup uses Backup locks where available as a lightweight alternative to FLUSH 
TABLES WITH READ LOCK. This feature is available in Percona Server for MySQL 5.6+. MySQL 
8.0 allows acquiring an instance level backup lock via the LOCK INSTANCE FOR BACKUP statement.

Locking is only done for MyISAM and other non-InnoDB tables after Percona XtraBackup 
finishes backing up all InnoDB/XtraDB data and logs. Percona XtraBackup uses this 
automatically to copy non-InnoDB data to avoid blocking DML queries that modify InnoDB tables

xtrabackup tries to avoid backup locks and FLUSH TABLES WITH READ LOCK when the instance 
contains only InnoDB tables

xtrabackup首先复制InnoDB数据，然后添加锁复制MyISAM表；

复制完成后，开始备份元数据文件（.frm、.MRG、.MYD等）

When backup locks are supported by the server, xtrabackup first copies InnoDB data, runs 
the LOCK TABLES FOR BACKUP and then copies the MyISAM tables. Once this is done, the backup 
of the files will begin. It will backup .frm, .MRG, .MYD, .MYI, .ARM, .ARZ, .CSM, .CSV, 
.sdi and .par files

元数据文件说明
.frm：表的定义信息，服务器会为所有存储引擎的每张表都创建一个.frm文件

.MRG：MERGE存储引擎使用，记录和MyISAM关联的表名称（Each table using the MERGE storage engine, 
      besides of a .frm file, will have .MRG file containing the names of the MyISAM tables 
      associated with it）

.MYD：MyISAM存储引擎数据文件
.MYI：MyISAM存储引擎索引文件

.ARM：Archive存储引擎元数据文件
.ARZ：Archive存储引擎数据文件

.CSM：CSV存储引擎元数据文件
.CSV：CSV存储引擎数据文件

.par：分区表的分区信息

元数据文件备份完成过后，xtrabackup会锁住binlog（二进制文件），以阻塞所有会改变二进制日志偏移量的操作；

随后，xtrabackup复制redo log文件、获取二进制日志文件偏移量，执行完成后释放binlog锁、表锁

After that xtrabackup will use LOCK BINLOG FOR BACKUP to block all operations that might 
change either binary log position or Exec_Master_Log_Pos or Exec_Gtid_Set (i.e. source 
binary log coordinates corresponding to the current SQL thread state on a replication 
replica) as reported by SHOW MASTER/SLAVE STATUS. xtrabackup will then finish copying the 
REDO log files and fetch the binary log coordinates. After this is completed xtrabackup 
will unlock the binary log and tables

最后，binlog position将输出到控制台，如果一切运行正常，xtrabackup返回状态0退出

Finally, the binary log position will be printed to STDERR and xtrabackup will exit 
returning 0 if all went OK

备份操作会在备份目录中创建以下文件

backup-my.cnf：prepare阶段InnoDB默认使用的配置文件(不是mysql原始的my.cnf文件)


*****************
xtrabackup_checkpoints：备份文件类型

#全量复制
backup_type = full-backuped
from_lsn = 0
to_lsn = 15188961605
last_lsn = 15188961605

#增量复制
backup_type = incremental
from_lsn = 15188961605
to_lsn = 15189350111
last_lsn = 15189350111


*****************
xtrabackup_binlog_info：备份时使用的二进制文件、偏移量(show master status)
xtrabackup_binlog_pos_innodb：binlog偏移量
xtrabackup_binary：xtrabackup二进制文件
xtrabackup_logfile：prepare阶段使用的日志文件


*****************
<table_name>.delta.meta：增量复制使用的增量文件

示例
page_size = 16384
zip_size = 0
space_id = 0


*****************
xtrabackup_slave_info：使用--slave-info参数输出的文件
xtrabackup_galera_info：使用--galera-info参数输出的文件

*********************

数据恢复

prepare 阶段：使用redo log恢复InnoDB数据文件

During the prepare phase, Percona XtraBackup performs crash recovery against the copied 
data files, using the copied transaction log file. After this is done, the database is 
ready to restore and use.

The backed-up MyISAM and InnoDB tables will be eventually consistent with each other, 
because after the prepare (recovery) process, InnoDB‘s data is rolled forward to the point 
at which the backup completed, not rolled back to the point at which it started. This point 
in time matches where the FLUSH TABLES WITH READ LOCK was taken, so the MyISAM data and the 
prepared InnoDB data are in sync

#InnoDB数据文件最终会和MyISAM数据文件保持一致
#InnoDB回滚到备份完成的时候，在这个时间点会执行flush tables with read lock开始备份MyISAM文件

restore a backup：使用备份文件恢复数据

#从配置文件读取参数，检查目录是否存在
xtrabackup will read from the my.cnf the variables datadir, innodb_data_home_dir, 
innodb_data_file_path, innodb_log_group_home_dir and check that the directories exist

#首先复制MyISAM表、索引、元数据文件
It will copy the MyISAM tables, indexes, etc. (.MRG, .MYD, .MYI, .ARM, .ARZ, .CSM, .CSV, 
.sdi, and par files) first, 

#然后复制InnoDB表、索引，日志文件
InnoDB tables and indexes next and the log files at last. 

#复制文件的时候会保存文件属性
It will preserve file’s attributes when copying them, you may have to change the files’ 
ownership to mysql before starting the database server, as they will be owned by the user 
who created the backup

备份使用参数

--copy-back：复制文件

--move-back：移动文件到对应目录