Introduction to mysql xtrabackup

Introduction to mysql xtrabackup

Official website:


Introduction to xtrabackup

xtrabackup is an open source mysql data backup tool that will not lock the database during data backup

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

xtrabackup supports backup of mysql and percona databases

Fully non-blocking backup: InnoDB, XtraDB, MyRocks

Pause writing during backup: 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 does not support backup of mysql databases and percona databases lower than version 8

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 and below do not support mysql 8.0.20 and above

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 can support mysql 8.0.21 and above

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

Backing up data on non-InnoDB (non-InnoDB storage engine) on the percona server will replace flush tables with read lock with a more lightweight lock Backup locks

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


Backup principle

Strabackup is based on the InnoDB crash recovery function: copy InnoDB data files, if the data in these data files are inconsistent, data recovery is performed

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 maintains a redo log (redo log), which records InnoDB data changes; when InnoDB restarts, it checks the data file and redo log, and performs the following operations according to the situation:

Transaction commit: save data changes caused by transaction operations in the data file

The transaction is not committed: roll back the data changes caused by the transaction operation in the data file

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

When xtrabackup starts, it will record the log sequence number (LSN) and copy the InnoDB data file

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 will start a process in the background to monitor the transaction log file (redo log) , and copy log file changes (copy log changes need to be continued, because redo log can be reused)

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

After InnoDB and XtraDB copy is completed, if you need to back up non_InnoDB (such as MyISAM) data, you need to lock

If there is only data stored in InnoDB in the database, it will not be locked

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 first copies InnoDB data, and then adds locks to copy MyISAM tables;

After copying is complete, start to back up metadata files (.frm, .MRG, .MYD, etc.)

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:分区表的分区信息

After the metadata file is backed up, xtrabackup will lock the binlog (binary file) to block all operations that will change the offset of the binary log;

Subsequently, xtrabackup copies the redo log file, obtains the offset of the binary log file , and releases the binlog lock and table lock after the execution is complete

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

Finally, the binlog position will be output to the console , if everything is running normally, xtrabackup returns to status 0 and exits

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

The backup operation creates the following files in the backup directory

backup-my.cnf:prepare阶段InnoDB默认使用的配置文件(不是mysql原始的my.cnf文件)  *****************xtrabackup_checkpoints:备份文件类型 #全量复制backup_type = full-backupedfrom_lsn = 0to_lsn = 15188961605last_lsn = 15188961605 #增量复制backup_type = incrementalfrom_lsn = 15188961605to_lsn = 15189350111last_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 = 16384zip_size = 0space_id = 0  *****************xtrabackup_slave_info:使用--slave-info参数输出的文件xtrabackup_galera_info:使用--galera-info参数输出的文件


Data Recovery

prepare phase: use redo log to restore InnoDB data files

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: Use the backup file to restore data

#从配置文件读取参数,检查目录是否存在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

Backup usage parameters

--copy-back: copy files

--move-back: move the file to the corresponding directory


Backup type

Full backup (full backup): Back up all data

Incremental backup: copy new data

Partial backup (partial backup): copy part of the database, table

Compressed backup: compressed backup files

Throttling backup : limit the number of read and write threads used for backup