mysql之 xtrabackup原理、備份日誌分析、備份資訊獲取

一、 xtrabackup備份恢復工作原理：

                                            extrabackup備份簡要步驟

InnoDB引擎很大程度上與Oracle類似，使用redo，undo機制，XtraBackup在備份的時候,以read-write模式開啟innodb的資料檔案，然後對其進行復制。與此同時，XtraBackup還有另外一個執行緒監視著transactions log，一旦log發生變化，就把變化過的log pages複製走，直到全部innoDB資料檔案複製完成之後，停止監控log buffer及日誌複製。如果log buffer沒有及時寫出將被日誌的迴圈寫特性覆蓋。xtrabackup在啟動時會記住log sequence number（LSN），然後一頁一頁地複製InnoDB的資料。

xtrabackup在恢復期間對提交的事務前滾，未提交或失敗的事務進行回滾，從而保證資料的一致性。因此對於InnoDB表在備份期間不會鎖表。由於XtraBackup其內建的InnoDB庫開啟檔案的時候是rw的，所以執行XtraBackup的使用者，必須對InnoDB的資料檔案具有讀寫許可權。

補充：
1.
在備份innodb page的過程中，XtraBackup每次讀寫1MB的資料，1MB/16KB=64個page。這個不可配置。讀1MB資料之 後，XtraBackup一頁一頁地遍歷這1MB資料，使用innodb的buf_page_is_corrupted()函式檢查此頁的資料是否正常， 如果資料不正常，就重新讀取這一頁，最多重新讀取10次，如果還是失敗，備份就失敗了，退出。在複製transactions log的時候，每次讀寫512KB的資料。同樣不可以配置。

2. 如果要使用一個最小許可權的使用者進行備份，則可基於如下命令建立此類使用者：
mysql> CREATE USER 'bkuser'@'localhost' IDENTIFIED BY 'secret';
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'bkuser';
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkuser'@'localhost';
mysql> FLUSH PRIVILEGES;

二、 完全備份演示及日誌分析：
[root@mysql01 full]# innobackupex --user=root --password=oracle /xtrabackup/full/
170602 20:24:02 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".

170602 20:24:02 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: not set
Using server version 5.6.25-log
innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 6f7a799)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: open files limit requested 0, set to 1024
## 從配置檔案獲取有關innodb的配置資訊 
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 = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
## 掃描innodb日誌lsn並複製inndodb系統表空間
170602 20:24:02 >> log scanned up to (1626057)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 1 for mysql/innodb_table_stats, old maximum was 0
170602 20:24:02 [01] Copying ./ibdata1 to /xtrabackup/full/2017-06-02_20-24-02/ibdata1
170602 20:24:02 [01] ...done
170602 20:24:03 [01] Copying ./mysql/innodb_table_stats.ibd to /xtrabackup/full/2017-06-02_20-24-02/mysql/innodb_table_stats.ibd
。。。。
170602 20:24:03 [01] ...done
170602 20:24:03 [01] Copying ./mysql/slave_master_info.ibd to /xtrabackup/full/2017-06-02_20-24-02/mysql/slave_master_info.ibd
170602 20:24:03 [01] ...done
170602 20:24:03 >> log scanned up to (1626057)
## 緩衝寫出到資料檔案並鎖表
170602 20:24:03 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
170602 20:24:03 Executing FLUSH TABLES WITH READ LOCK...
## 開始複製非innodb表及相關檔案 
170602 20:24:03 Starting to backup non-InnoDB tables and files
170602 20:24:03 [01] Copying ./mysql/help_category.MYI to /xtrabackup/full/2017-06-02_20-24-02/mysql/help_category.MYI
170602 20:24:03 [01] ...done
170602 20:24:03 [01] Copying ./mysql/plugin.frm to /xtrabackup/full/2017-06-02_20-24-02/mysql/plugin.frm
170602 20:24:03 [01] ...done
170602 20:24:03 [01] Copying ./mysql/plugin.MYD to /xtrabackup/full/2017-06-02_20-24-02/mysql/plugin.MYD
。。。。
170602 20:24:05 [01] Copying ./performance_schema/events_stages_history_long.frm to /xtrabackup/full/2017-06-02_20-24-02/performance_schema/events_stages_history_long.frm
170602 20:24:05 [01] ...done
## 結束複製非innodb表及相關檔案 
170602 20:24:05 Finished backing up non-InnoDB tables and files
170602 20:24:05 [00] Writing xtrabackup_binlog_info
170602 20:24:05 [00] ...done
## 強制將commit log重新整理到redo,保證事務是完整的
170602 20:24:05 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
## 獲取最新的lsn
xtrabackup: The latest check point (for incremental): '1626057'
xtrabackup: Stopping log copying thread.
.170602 20:24:05 >> log scanned up to (1626057)
## 釋放鎖
170602 20:24:05 Executing UNLOCK TABLES
170602 20:24:05 All tables unlocked
## 列出備份位置
170602 20:24:05 Backup created in directory '/xtrabackup/full/2017-06-02_20-24-02/'
## 列出binlog的位置
MySQL binlog position: filename 'binlog.000001', position '120'
170602 20:24:05 [00] Writing backup-my.cnf
170602 20:24:05 [00] ...done
170602 20:24:05 [00] Writing xtrabackup_info
170602 20:24:05 [00] ...done
xtrabackup: Transaction log of lsn (1626057) to (1626057) was copied.
170602 20:24:05 completed OK!

三、獲取備份的相關資訊
1. 檢視備份期間生成的檔案
[root@mysql01 full]# ls /xtrabackup/full/2017-06-02_20-24-02/
backup-my.cnf performance_schema xtrabackup_checkpoints
ibdata1 test xtrabackup_info
mysql xtrabackup_binlog_info xtrabackup_logfile
說明：

2. 檢視備份有關的總體資訊 
[root@mysql01 full]# more /xtrabackup/full/2017-06-02_20-24-02/xtrabackup_info
uuid = 5e8841af-478e-11e7-a0d7-000c2944297a
name = 
tool_name = innobackupex
tool_command = --user=root --password=... /xtrabackup/full/
tool_version = 2.4.7
ibbackup_version = 2.4.7
server_version = 5.6.25-log
start_time = 2017-06-02 20:24:02
end_time = 2017-06-02 20:24:05
lock_time = 0
binlog_pos = filename 'binlog.000001', position '120'
innodb_from_lsn = 0
innodb_to_lsn = 1626057
partial = N
說明>xtrabackup_info 記錄： 整個備份資訊的概要
3. 檢視備份檢查點的相關資訊 
[root@mysql01 full]# more /xtrabackup/full/2017-06-02_20-24-02/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1626057
last_lsn = 1626057
compact = 0
recover_binlog_info = 0
說明>xtrabackup_checkpoints 記錄：備份型別（如完全或增量）、備份狀態（如是否已經為prepared狀態）和LSN(日誌序列號)範圍資訊； 
4. 檢視備份binlog有關的資訊 
[root@mysql01 full]# more /xtrabackup/full/2017-06-02_20-24-02/xtrabackup_binlog_info 
binlog.000001 120
說明>xtrabackup_binlog_info記錄: mysql伺服器當前正在使用的二進位制日誌檔案及至備份這一刻為止二進位制日誌事件的位置。
5. 檢視備份命令的配置選項資訊
[root@mysql01 full]# more /xtrabackup/full/2017-06-02_20-24-02/backup-my.cnf 
# This MySQL options file was generated by innobackupex.
# The MySQL server
[mysqld]
innodb_checksum_algorithm=innodb
innodb_log_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:12M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=50331648
innodb_fast_checksum=false
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_directory=.
innodb_undo_tablespaces=0

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31383567/viewspace-2140206/，如需轉載，請註明出處，否則將追究法律責任。