The process of logical backup mysqldump and physical backup Xtrabackup

Mysqldump Backup principle

The basic process for backup is as follows:

1. FLUSH TABLES

   功能：关闭实例上所有打开表目的：为第二步prepare，为了避免较长的事务操作造成FLUSH TABLES WITH READ LOCK操作迟迟得不到锁，但同时又阻塞了其它客户端操作

2. FLUSH TABLES with READ LOCK

   功能：加全局读锁目的：获得DB一致性状态

3. SET SESSION TRANSACTION Isolation level repeatable READ

   功能：设置当前会话的事务隔离等级为RR，RR可避免不可重复读和幻读目的：确保在备份事务中任何时刻数据都相同

4. START TRANSACTION with consistent SNAPSHOT

   功能：获取当前数据库的快照，这个是由mysqldump中--single-transaction决定的目的： 简而言之，就是开启事务并对所有表执行了一次SELECT操作，这样可保证备份时，在任意时间点执行select * from table得到的数据和执行START TRANSACTION WITH CONSISTENT SNAPSHOT时的数据一致

5. Obtain Log position

   功能：获取binlog的相关信息，这个是由--master-data决定的目的：记录了开始备份时，binlog的状态信息，包括MASTER_LOG_FILE和MASTER_LOG_POS

6. Backing up non-InnoDB table data (. Frm,.myi,.myd, etc.)
   
7. Unlock tables (non-InnoDB table backup completed)
   
8. Backing Up InnoDB table data
   
9. Backup complete
   

Xtrabackup Backup principle

The essence of Innobackupex: Innobackupex scripts are used to back up non-InnoDB tables, and Xtrabackup commands are invoked to back up InnoDB tables

The basic process for backup is as follows:

2. Innobackupex after booting, it will fork a process, start the xtrabackup process, and wait for Xtrabackup to back up the IBD data file
   ?
4. Xtrabackup in the backup InnoDB related data, there are 2 kinds of threads, 1 are redo copy thread, is responsible for copying redo files, 1 kinds of IBD copy thread, responsible for copying IBD files; Redo copy thread only one, before IBD copy threads, in IBD Ends when the thread ends. After the xtrabackup process begins execution, the redo copy thread is started, the redo log is copied sequentially from the latest checkpoint point, and then the IBD data copy thread is started, and during the xtrabackup copy of the IBD process, the INNOBACKUPEX process Wait (wait for file to be created)
   ?
6. Xtrabackup Copy completes IDB, notifies Innobackupex (by creating a file) while waiting for itself (redo thread continues to copy)
   ?
8. After Innobackupex receives the XTRABACKUP notification, executes flush TABLES with READ LOCK (FTWRL), obtains the consistency site, and then starts to back up non-InnoDB files (including frm, MYD, MYI, CSV, opt, par , etc.). Copy non-InnoDB file process, because the database is in the global read-only state, if the main library backup in the business, to be particularly careful, non-InnoDB table (mainly MyISAM) more than the whole library read time will be longer, this impact must be evaluated to
   ?
10. When Innobackupex copies all non-InnoDB table files, notify Xtrabackup (by deleting the file) and enter the wait (waiting for another file to be created)
    ?
12. Xtrabackup receive Innobackupex back up non-InnoDB notification, stop redo copy thread and notify Innobackupex redo log copy complete (by creating file)
    ?
14. Innobackupex receives redo backup completion notification, it starts unlocking, performing UNLOCK TABLES
    ?
16. Finally, the Innobackupex and xtrabackup processes finish their work, such as releasing resources, writing backup metadata information, etc., Innobackupex wait for xtrabackup child process to exit
    ?

The process of logical backup mysqldump and physical backup Xtrabackup