mysql xtrabackup 简介

不念不忘少年蓝@ 2022-10-16 09:57 4阅读 0赞

--------------------

### mysql xtrabackup 简介 ###

**官网：[https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html][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：**移动文件到对应目录

### \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* ###

**备份类型**

**全量备份（full backup）：**备份全部数据

**增量备份（incremental backup）：**复制新增数据

**部分备份（partial backup）：**复制部分数据库、表

**压缩备份（compressed backup）：**压缩备份文件

**限流备份（throttling backup）：**限制备份使用的读写线程数

[https_www.percona.com_doc_percona-xtrabackup_LATEST_index.html]: https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html