mysql中xtrabackup原理、備份日志分析、備份信息獲取的示例分析

小編給大家分享一下mysql中xtrabackup原理、備份日志分析、備份信息獲取的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

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

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

以上是“mysql中xtrabackup原理、備份日志分析、備份信息獲取的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！