innobackupex数据库备份原理

innobackupex是Percona开源的适MySQL热备份工具，在备份过程中不会锁定数据库。接下来我们就通过执行日志分析innobackupex的执行过程，从而总结备份的原理。

前提：
 innobackupex命令：
  /usr/bin/innobackupex --default-file=/etc/my.cnf --user “test” --password “test” --host 127.0.0.1 --parallel=2 --throttle=50 --slave-info /data/backup/
 MySQL版本：5.7.27
 nnobackupex版本：2.4.19

通过下方的执行日志我们可以得出innobackupex备份过程如下：

1、根据链接信息做数据库的连通性和版本检测
2、读取数据库相关的配置信息（数据和日志文件位置等信息）
3、开启redo日志拷贝线程，从最新的checkpoint开始顺序拷贝redolog到xtrabackup_logfile中
4、开始备份 .ibd ibdata1 undo 等innodb文件
5、执行FLUSH NO_WRITE_TO_BINLOG TABLES 和  FLUSH TABLES WITH READ LOCK
	1）关闭所有打开的表，强制关闭所有正在使用的表,不写入binlog
	2）关闭所有打开的表，强制关闭所有正在使用的表，并给所有表增加全局read lock
6、拷贝非innodb表相关文件信息
7、读取binlog pos信息并写入xtrabackup_binlog_info文件
8、执行 FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS
	1)刷新redo log buffer中的日志到磁盘文件，确保redo log copy线程拷贝这最后的redolog日志数据（为什么说是最后的
	redolog日志数据，因为在FLUSH TABLES WITH READ LOCK加锁之后，使用UNLOCK TABLES释放全局读锁之前，不会再有新
	的请求进来）
	2）为什么确保所有redo日志拷贝完整（因为没有拷贝binlog，如果最后一组提交的事物redo的commit标志没有落盘恢复时
	会丢失最后一组事物)
9、停止redolog日志拷贝线程
10、执行UNLOCK TABLES释放表锁
11、收尾生成backup-my.cnf和xtrabackup_info等文件，备份完成。

了解到innobackupex的备份过程后我们可以得知innobackupex备份存在的问题：

1、innobackupex在备份事务表时，是没有对数据库加锁的此时DDL是允许执行的，innobackupex持续在备份innodb事务表期间，如果被执行DDL的表是在innobackupex备份完成之后发起，那么在下一次scan lsn的时候innobackupex将发现DDL更改，报错终止，如果是在备份非事务表期间发起的DDL，那么将被FLUSH TABLE WITH READ LOCK语句阻塞。所以，对于使用innobackupex备份的生产环境，要执行DDL语句，也需要避开备份时间
2、DDL、大查询（正在执行的DML）会阻塞FTWRL导致实例所有DML操作堵塞（包含从库sql线程的执行）

执行日志：

xtrabackup: recognized server arguments: --parallel=2 
xtrabackup: recognized client arguments: 
210225 14:20:14 innobackupex: Starting the backup operation
210225 14:20:14  version_check Connecting to MySQL server with DSN
1、根据信息做数据库的连通性和版本检测
 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=127.0.0.1' as 'test'  (using password: YES).
210225 14:20:14  version_check Connected to MySQL server
210225 14:20:14  version_check Executing a version check against the server...
210225 14:20:14  version_check Done.
210225 14:20:14 Connecting to MySQL server host: 127.0.0.1, user: test, password: set, port: not set, socket: not set
Using server version 5.7.27-log
/usr/bin/innobackupex version 2.4.19 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c2d69da)
xtrabackup: uses posix_fadvise().
2、读取数据库相关的配置信息（数据和日志）
xtrabackup: cd to /data/mysql/data/
xtrabackup: open files limit requested 0, set to 655350
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 = /data/mysql/redolog
xtrabackup:   innodb_log_files_in_group = 4
xtrabackup:   innodb_log_file_size = 2147483648
InnoDB: Number of pools: 1
3、开启redo日志拷贝线程，从最新的checkpoint开始顺序拷贝redolog
210225 14:20:14 >> log scanned up to (28198520580)
InnoDB: Opened 3 undo tablespaces
InnoDB: 0 undo tablespaces made active
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 5 for mysql/plugin, old maximum was 3
xtrabackup: Starting 2 threads for parallel data files transfer
4、开始拷贝 .ibd ibdata1 undo 等innodb文件
210225 14:20:15 [02] Copying ./ibdata1 to /data/backup/2021-02-25_14-20-14/ibdata1
210225 14:20:15 [01] Copying /data/mysql/undolog/undo001 to /data/backup/2021-02-25_14-20-14/undo001
210225 14:20:15 [02]        ...done
210225 14:20:15 [01]        ...done
210225 14:20:15 >> log scanned up to (28198520580)
210225 14:20:16 [02] Copying /data/mysql/undolog/undo002 to /data/backup/2021-02-25_14-20-14/undo002
210225 14:20:16 [01] Copying /data/mysql/undolog/undo003 to /data/backup/2021-02-25_14-20-14/undo003
210225 14:20:23 >> log scanned up to (28198520580)
210225 14:20:24 >> log scanned up to (28198520580)
210225 14:20:25 [02]        ...done
210225 14:20:25 [01] Copying ./mysql/plugin.ibd to /data/backup/2021-02-25_14-20-14/mysql/plugin.ibd
210225 14:20:25 [01]        ...done
210225 14:20:25 [02] Copying ./mysql/servers.ibd to /data/backup/2021-02-25_14-20-14/mysql/servers.ibd
210225 14:20:25 [02]        ...done
5、执行FLUSH NO_WRITE_TO_BINLOG TABLES 和  FLUSH TABLES WITH READ LOCK
210225 14:20:28 >> log scanned up to (28198520580)
210225 14:20:29 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
210225 14:20:29 Executing FLUSH TABLES WITH READ LOCK...
6、拷贝非innodb表相关文件信息
210225 14:20:29 Starting to backup non-InnoDB tables and files
210225 14:20:29 [01] Copying ./mysql/db.opt to /data/backup/2021-02-25_14-20-14/mysql/db.opt
210225 14:20:29 [01]        ...done
210225 14:20:34 [01] Copying ./sys/statements_with_temp_tables.frm to /data/backup/2021-02-25_14-20-14/sys/statements_with_temp_tables.frm
210225 14:20:34 [01]        ...done
210225 14:20:40 Finished backing up non-InnoDB tables and files
7、读取binlog pos信息并写入xtrabackup_binlog_info文件
210225 14:20:40 [00] Writing /data/backup/2021-02-25_14-20-14/xtrabackup_slave_info
210225 14:20:40 [00]        ...done
210225 14:20:40 [00] Writing /data/backup/2021-02-25_14-20-14/xtrabackup_binlog_info
210225 14:20:40 [00]        ...done
8、执行 FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS
210225 14:20:40 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
9、停止redolog日志拷贝线程
xtrabackup: The latest check point (for incremental): '28198520571'
xtrabackup: Stopping log copying thread.
.210225 14:20:40 >> log scanned up to (28198520580)
10、执行UNLOCK TABLES释放表锁
210225 14:20:41 Executing UNLOCK TABLES
210225 14:20:41 All tables unlocked
11、收尾生成backup-my.cnf和xtrabackup_info等文件，备份完成
210225 14:20:41 [00] Copying ib_buffer_pool to /data/backup/2021-02-25_14-20-14/ib_buffer_pool
210225 14:20:41 [00]        ...done
210225 14:20:41 Backup created in directory '/data/backup/2021-02-25_14-20-14/'
MySQL binlog position: filename 'bin.000007', position '2549986', GTID of the last change 'dafc13c9-7eff-11ea-addc-5254010ec9c8:1-14,
dd1cffa1-7eff-11ea-a289-5254010ec9c8:1-56,
e0860904-7eff-11ea-989d-5254010ec9c8:1-3999'
210225 14:20:41 [00] Writing /data/backup/2021-02-25_14-20-14/backup-my.cnf
210225 14:20:41 [00]        ...done
210225 14:20:41 [00] Writing /data/backup/2021-02-25_14-20-14/xtrabackup_info
210225 14:20:41 [00]        ...done
xtrabackup: Transaction log of lsn (28198520571) to (28198520580) was copied.
210225 14:20:42 completed OK!