MySQL[3] 線上熱備工具Xtrabackup

XtraBackup更多進階特性通常隻能在innodb存儲引擎上實作，而且進階特性還都依賴于mysql資料庫對innodb引擎實作了單獨表空間，否則沒辦法實作單表或單庫導出

<code>mysql> show global variables like </code><code>'%innodb_file_per_table%'</code><code>;</code>

<code>| Variable_name | Value |</code>

<code>| innodb_file_per_table | ON |</code>

一、簡介

Xtrabackup是由percona提供的mysql資料庫開源熱備工具。特點如下：

(1)備份過程快速、可靠；

(2)備份過程不會打斷正在執行的事務；

(3)能夠基于壓縮等功能節約磁盤空間和流量；

(4)自動實作備份檢驗；

(5)還原速度快；

二、安裝

XtraBackup目前最新版為2.2.9，本篇部落格采用yum安裝方式

1、yum安裝

<code># yum -y install libaio perl-Time-HiRes perl-DBD-MySQL perl-IO-Socket-SSL</code>

<code># rpm -ivh http://www.percona.com/downloads/XtraBackup/XtraBackup-2.2.9/binary/redhat/6/x86_64/percona-xtrabackup-2.2.9-5067.el6.x86_64.rpm</code>

2、檢視Xtrabackup安裝的工具

<code># rpm -ql percona-xtrabackup |grep bin</code>

<code>/</code><code>usr</code><code>/</code><code>bin</code><code>/</code><code>innobackupex </code>

<code>/</code><code>usr</code><code>/</code><code>bin</code><code>/</code><code>xbcrypt </code>

<code>/</code><code>usr</code><code>/</code><code>bin</code><code>/</code><code>xbstream </code>

<code>/</code><code>usr</code><code>/</code><code>bin</code><code>/</code><code>xtrabackup</code>

3、XtraBackup中主要包含了三個工具

xbsteam：支援流式備份

xtrbackup：用于熱備innodb、xtradb表中資料的工具，不能備份其它類型的表，也不能備份資料表結構

innobackupex：是将xtrabackup進行封裝的perl腳本，提供了備份MyISAM表的能力

三、innobackupex幾個非常重要的參數

--apply-log

一般情況下，在備份完成後，資料尚且不能用于恢複操作，因為備份的資料中可能會包含尚未送出的事務或已經送出但尚未同步至資料檔案中的事務。是以，此時資料檔案仍處理不一緻狀态。“準備”的主要作用正是通過復原未送出的事務及同步已經送出的事務至資料檔案也使得資料檔案處于一緻性狀态。

--redo-only

準備(prepare)增量備份與整理完全備份有着一些不同，尤其要注意的是：

(1)需要在每個備份(包括完全和各個增量備份)上，将已經送出的事務進行“重放”。“重放”之後，所有的備份資料将合并到完全備份上。

(2)基于所有的備份将未送出的事務進行“復原”。

--copy-back

該選項用于執行恢複(還原)操作，其通過複制所有資料相關的檔案至mysql伺服器DATADIR目錄中來執行恢複過程。innobackupex通過backup-my.cnf來擷取DATADIR目錄的相關資訊。

四、innobackup備份文法

完全備份+完全恢複

<code># innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/</code>

<code>準備一個完全備份</code>

<code># innobackupex --apply-log /path/to/BACKUP-DIR</code>

<code>從一個完全備份中恢複資料</code>

<code># innobackupex --copy-back /path/to/BACKUP-DIR</code>

<code>修改datadir目錄權限</code>

<code># chown -R mysql:mysql /mydata/data/</code>

完全備份+增量備份+完全恢複

<code>第一次增量備份</code>

<code># innobackupex --user=DBUSER --password=DBUSERPASS --incremental /backup --incremental-basedir=BASEDIR</code>

<code>第二次增量備份</code>

<code>執行完全備份redo</code>

<code># innobackupex --apply-log --redo-only BASE-DIR</code>

<code>執行第一次增量備份redo</code>

<code># innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1</code>

<code>執行第二次增量備份redo</code>

<code># innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2</code>

<code># innobackupex --copy-back BASE-DIR</code>

<code>其中BASE-DIR指的是完全備份所在的目錄，</code>

<code>而INCREMENTAL-DIR-1指的是第一次增量備份的目錄，</code>

<code>INCREMENTAL-DIR-2指的是第二次增量備份的目錄，</code>

<code>其它依次類推，即如果有多次增量備份，每一次都要執行如上操作；</code>

五、以上兩種方式案列重放

<code>1、實驗前的準備工作</code>

<code># service mysqld stop</code>

<code># rm -rf /mydata/data/* </code>

<code># /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/mydata/data/ --basedir=/usr/local/mysql/</code>

<code># service mysqld start</code>

<code># mysqladmin -uroot -p password 123456</code>

<code># mysql -uroot -p123456</code>

<code>mysql> create database jiaowu;</code>

<code>mysql> use jiaowu;</code>

<code>mysql> </code><code>set</code> <code>sql_log_bin = 0;</code>

<code>mysql> </code><code>source</code> <code>/root/tutor</code><code>.sql; </code><code>//</code><code>導入tutor資料表</code>

<code>mysql> </code><code>set</code> <code>sql_log_bin = 1;</code>

<code>mysql> </code><code>select</code> <code>* from tutor;</code>

<code>| 1 | ZhengYansheng | M | 25 |</code>

<code>| 2 | LiJian | M | 26 |</code>

<code>| 3 | OuYangyu | M | 27 |</code>

<code>| 4 | LuoChenghui | M | 25 |</code>

<code>| 5 | LiuYunbo | M | 25 |</code>

<code>| 6 | FuJian | M | 24 |</code>

<code>| 7 | LiMenglu | F | 23 |</code>

<code>| 8 | BaoYintu | M | 28 |</code>

<code>| 9 | WangYana | F | 25 |</code>

<code>2、innobackupex對DB進行完全備份</code>

<code># innobackupex --user=root --password=123456 /backup/</code>

<code>如果執行正确，其最後輸出的幾行資訊通常如下：</code>

<code>innobackupex: Backup created </code><code>in</code> <code>directory </code><code>'/backup/2015-03-18_21-00-17'</code>

<code>innobackupex: MySQL binlog position: filename </code><code>'mysql-bin.000003'</code><code>, position 332</code>

<code>150318 21:00:23 innobackupex: Connection to database server closed</code>

<code>150318 21:00:23 innobackupex: completed OK!</code>

<code>3、檢視備份目錄和檔案</code>

<code># ls /backup/</code>

<code># ls /backup/2015-03-18_21-00-17/</code>

<code>backup-my.cnf jiaowu performance_schema xtrabackup_binlog_info xtrabackup_info</code>

<code>ibdata1 mysql </code><code>test</code> <code>xtrabackup_checkpoints xtrabackup_logfile</code>

<code>4、準備一個完全備份</code>

<code># innobackupex --user=root --password=123456 --apply-log /backup/2015-03-18_21-00-17/</code>

<code>#####模拟資料庫故障#####</code>

<code>5、這裡還是采用老方法直接删除所有的資料檔案</code>

<code># rm -rf /mydata/data/*</code>

<code>6、從一個完全備份中恢複資料庫</code>

<code># innobackupex --copy-back /backup/2015-03-18_21-00-17/</code>

<code>7、修改資料目錄權限</code>

<code># chown -R mysql.mysql /mydata/data/</code>

<code>8、啟動mysqld服務</code>

<code>9、登陸mysql檢視是否是否一緻</code>

<code># mysql -e 'use jiaowu;select * from tutor;'</code>

<code>#資料已經成功恢複到資料庫當中</code>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

<code># rm -rf /mydata/data/* //删除原來的備份檔案</code>

<code># rm -rf /backup/*</code>

<code>mysql> </code><code>source</code> <code>/root/tutor</code><code>.sql; </code><code>//</code><code>導入tutor資料表</code>

<code>innobackupex: Backup created </code><code>in</code> <code>directory </code><code>'/backup/2015-03-18_21-14-49'</code>

<code>150318 21:14:54 innobackupex: Connection to database server closed</code>

<code>150318 21:14:54 innobackupex: completed OK!</code>

<code>3、僅檢視備份目錄</code>

<code>4、操作資料庫并插入資料</code>

<code># mysql jiaowu;</code>

<code>mysql> insert into tutor(TID) values(11);</code>

<code>mysql> insert into tutor(TID) values(12);</code>

<code>mysql> insert into tutor(TID) values(13);</code>

<code>5、執行第一次增量備份并檢視備份目錄</code>

<code># innobackupex --user=root --password=123456 --incremental /backup/ --incremental-basedir=/backup/2015-03-18_21-14-49/</code>

<code>6、再次操作資料庫并插入多條資料</code>

<code>mysql> insert into tutor(TID) values(21);</code>

<code>mysql> insert into tutor(TID) values(22);</code>

<code>mysql> insert into tutor(TID) values(23);</code>

<code>7、執行第二次增量備份并檢視備份檔案</code>

<code># innobackupex --user=root --password=123456 --incremental /backup/ --incremental-basedir=/backup/2015-03-18_21-18-45/</code>

<code>2015-03-18_21-14-49：為innobackupex的完全備份目錄</code>

<code>2015-03-18_21-18-45：為innobackupex的第一次增量備份目錄</code>

<code>2015-03-18_21-22-31：為innobackupex的第二次增量備份目錄</code>

<code>8、開始準備innobackupex</code>

<code>首先執行完全備份redo-only</code>

<code># innobackupex --user=root --password=123456 --apply-log --redo-only /backup/2015-03-18_21-14-49/</code>

<code>執行第一個增量備份redo-only</code>

<code># innobackupex --user=root --password=123456 --apply-log --redo-only /backup/2015-03-18_21-14-49/ --incremental-dir=/backup/2015-03-18_21-18-45/</code>

<code>執行第二個增量備份redo-only</code>

<code># innobackupex --user=root --password=123456 --apply-log --redo-only /backup/2015-03-18_21-14-49/ --incremental-dir=/backup/2015-03-18_21-22-31/</code>

<code>9、這裡還是采用老方法直接删除所有的資料檔案</code>

<code>10、從完全備份中恢複資料庫</code>

<code># innobackupex --user=root --password=123456 --copy-back /backup/2015-03-18_21-14-49/</code>

<code>11、修改資料目錄權限</code>

<code>12、啟動mysqld服務</code>

<code>13、登陸mysql檢視是否是否一緻</code>

<code>#兩次增量添加的資料也已經成功添加到資料庫當中。恢複成功</code>

六、Xtrabackup的進階功能

流式壓縮功能

Xtrabackup對備份的資料檔案支援“流”功能，即可以将備份的資料通過STDOUT傳輸給tar程式進行歸檔，而不是預設的直接儲存至某備份目錄中。要使用此功能，僅需要使用--stream選項即可。如：

<code># innobackupex --user=root --password=123456 --stream=tar /backup | gzip > /backup/`date +%F_%H-%M-%S`.tar.gz</code>

甚至也可以使用類似如下指令将資料備份至其它伺服器：強烈推薦這種方式

<code># innobackupex --user=root --password=123456 --stream=tar /backup | ssh [email protected] 'cat - > /backup/`date +%F_%H-%M-%S`.tar'</code>

在執行本地備份時，還可以使用--parallel選項對多個檔案進行并行複制（暫時還沒有看懂此選項）

<code>此外，在執行本地備份時，還可以使用--parallel選項對多個檔案進行并行複制。此選項用于指定在複制時啟動的線程數目。當然，在實際進行備份時要利用此功能的便利性，也需要啟用innodb_file_per_table選項或共享的表空間通過innodb_data_file_path選項存儲在多個ibdata檔案中。對某一資料庫的多個檔案的複制無法利用到此功能。其簡單使用方法如下：</code>

<code># innobackupex --parallel /path/to/backup</code>

<code>同時，innobackupex備份的資料檔案也可以存儲至遠端主機，這可以使用--remote-host選項來實作：</code>

<code># innobackupex [email protected] /path/IN/REMOTE/HOST/to/backup</code>

七、使用Xtrabackup對資料庫進行部分備份

Xtrabackup也可以實作部分備份，即隻備份某個或某些指定的資料庫或某資料庫中的某個或某些表。但要使用此功能，必須啟用innodb_file_per_table選項，即每張表儲存為一個獨立的檔案。同時，其也不支援--stream選項，即不支援将資料通過管道傳輸給其它程式進行處理。

此外，還原部分備份跟還原全部資料的備份也有所不同，即你不能通過簡單地将prepared的部分備份使用--copy-back選項直接複制回資料目錄，而是要通過導入表的方向來實作還原。當然，有些情況下，部分備份也可以直接通過--copy-back進行還原，但這種方式還原而來的資料多數會産生資料不一緻的問題，是以，無論如何不推薦使用這種方式。

(1)建立部分備份

建立部分備份的方式有三種：

正規表達式(--include)

枚舉表檔案(--tables-file)

列出要備份的資料庫(--databases)。

<code>(a)使用--include</code>

<code>使用--include時，要求為其指定要備份的表的完整名稱，即形如databasename.tablename，如：</code>

<code># innobackupex --include='^mageedu[.]tb1' /path/to/backup</code>

<code>(b)使用--tables-</code><code>file</code>

<code>此選項的參數需要是一個檔案名，此檔案中每行包含一個要備份的表的完整名稱；如：</code>

<code># echo -e 'mageedu.tb1\nmageedu.tb2' > /tmp/tables.txt</code>

<code># innobackupex --tables-file=/tmp/tables.txt /path/to/backup</code>

<code>(c)使用--databases</code>

<code>此選項接受的參數為資料名，如果要指定多個資料庫，彼此間需要以空格隔開；同時，在指定某資料庫時，也可以隻指定其中的某張表。此外，此選項也可以接受一個檔案為參數，檔案中每一行為一個要備份的對象。如：</code>

<code># innobackupex --databases="mageedu testdb" /path/to/backup</code>

(2)整理(preparing)部分備份

<code>prepare部分備份的過程類似于導出表的過程，要使用--</code><code>export</code><code>選項進行：</code>

<code># innobackupex --apply-log --export /pat/to/partial/backup</code>

<code>此指令執行過程中，innobackupex會調用xtrabackup指令從資料字典中移除缺失的表，是以，會顯示出許多關于“表不存在”類的警告資訊。同時，也會顯示出為備份檔案中存在的表建立.exp檔案的相關資訊。</code>

(3)還原部分備份

<code>還原部分備份的過程跟導入表的過程相同。當然，也可以通過直接複制prepared狀态的備份直接至資料目錄中實作還原，不要此時要求資料目錄處于一緻狀态。</code>

案列示範：

對jiaowu資料庫進行備份和還原

<code>1、檢視資料庫和表</code>

<code>mysql> show databases;</code>

<code>| Database |</code>

<code>| information_schema |</code>

<code>| jiaowu |</code>

<code>| mysql |</code>

<code>| performance_schema |</code>

<code>mysql> </code><code>select</code> <code>* from jiaowu.tutor;</code>

<code>2、innobackupex進行備份</code>

<code># innobackupex --user=root --password=123456 --databases='jiaowu' /opt/</code>

<code>3、準備并導入jiaowu表</code>

<code># innobackupex --user=root --password=123456 --apply-log --export /opt/2015-03-18_22-46-47/</code>

<code>4、删除jiaowu資料庫</code>

<code># rm -rf /mydata/data/jiaowu</code>

<code>5、登陸資料庫檢視是否還存在jiaowu資料庫</code>

<code>[root@localhost ~]</code><code># mysql -e 'show databases;'</code>

<code>#jiaowu資料庫不存在了</code>

<code>6、還原jiaowu資料庫</code>

<code># cp -a /opt/2015-03-18_22-46-47/jiaowu/ /mydata/data/</code>

<code>7、修改jiaowu權限</code>

<code># chown -R mysql.mysql /mydata/data/jiaowu</code>

<code>8、再次檢視jiaowu資料庫是否存在</code>

<code>[root@localhost ~]</code><code># mysql -e 'use jiaowu;show tables;'</code>

<code>| Tables_in_jiaowu |</code>

<code>| tutor |</code>

<code>#jiaowu資料庫已經被還原并且表tutor還在。成功！！！</code>

八、導入或導出單張表

預設情況下，InnoDB表不能通過直接複制表檔案的方式在mysql伺服器之間進行移植，即便使用了innodb_file_per_table選項。而使用Xtrabackup工具可以實作此種功能，

不過，此時需要“導出”表的mysql伺服器啟用了innodb_file_per_table選項（嚴格來說，是要“導出”的表在其建立之前，mysql伺服器就啟用了innodb_file_per_table選項），

并且“導入”表的伺服器同時啟用了innodb_file_per_table和innodb_expand_import選項。

(1)“導出”表

<code>導出表是在備份的prepare階段進行的，是以，一旦完全備份完成，就可以在prepare過程中通過--</code><code>export</code><code>選項将某表導出了：</code>

<code># innobackupex --apply-log --export /path/to/backup</code>

<code>此指令會為每個innodb表的表空間建立一個以.exp結尾的檔案，這些以.exp結尾的檔案則可以用于導入至其它伺服器。</code>

(2)“導入”表

<code>要在mysql伺服器上導入來自于其它伺服器的某innodb表，需要先在目前伺服器上建立一個跟原表表結構一緻的表，而後才能實作将表導入：</code>

<code>mysql> CREATE TABLE mytable (...) ENGINE=InnoDB;</code>

<code>然後将此表的表空間删除：</code>

<code>mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;</code>

<code>接下來，将來自于“導出”表的伺服器的mytable表的mytable.ibd和mytable.exp檔案複制到目前伺服器的資料目錄，然後使用如下指令将其“導入”：</code>

<code>mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;</code>

<code>Database changed</code>

<code>mysql> show tables;</code>

<code>2、修改表的存儲引擎為InnoDB</code>

<code>mysql> alter table tutor engine=innodb;</code>

<code>Query OK, 15 rows affected (0.05 sec)</code>

<code>Records: 15 Duplicates: 0 Warnings: 0</code>

<code>3、innobackupex對其進行備份</code>

<code># innobackupex --user=root --password=123456 --databases='jiaowu.tutor' /opt/</code>

<code>4、準備并導出</code>

<code># innobackupex --user=root --password=123456 --apply-log --export /opt/2015-03-18_23-05-44/</code>

<code>5、删除此表的表空間</code>

<code>mysql> ALTER TABLE jiaowu.tutor DISCARD TABLESPACE;</code>

<code>Query OK, 0 rows affected (0.01 sec)</code>

<code>mysql> </code><code>select</code> <code>* from tutor; </code><code>#資料已經不存在了</code>

<code>ERROR 1030 (HY000): Got error -1 from storage engine</code>

<code>6、接下來，将來自于“導出”表的伺服器的mytable表的mytable.ibd和mytable.exp檔案複制到目前伺服器的資料目錄，然後使用如下指令将其“導入”：并修改權限</code>

<code># cp /opt/2015-03-18_23-24-23/jiaowu/{tutor.exp,tutor.ibd} /mydata/data/jiaowu/</code>

<code>cp</code><code>：是否覆寫</code><code>"/mydata/data/jiaowu/tutor.exp"</code><code>？ </code><code>yes</code>

<code># chown -R mysql.mysql /mydata/data/jiaowu/*</code>

<code>mysql> ALTER TABLE jiaowu.tutor IMPORT TABLESPACE;</code>

<code>Query OK, 0 rows affected (0.00 sec)</code>

<code>#單表還原已經成功！結束。</code>

本文轉自zys467754239 51CTO部落格，原文連結http://blog.51cto.com/467754239/1621711：，如需轉載請自行聯系原作者

MySQL[3] 線上熱備工具Xtrabackup

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述