Innobackupex 和 mydumper，mysql備援工具

----------------------------------------------- -

------實體備份工具Innobackupex------

--------------------- ---------------------------

官方Manual：https://www.percona.com/doc/percona- xtrabackup/LATEST/index.html

主要用於熱備採用InnoDB、MyISAM等引擎儲存的數據，備份時將要備份的資料載入到記憶體中，再寫入磁碟上backup datafile中。對於備份期間變更的數據，將同redo log恢復的方式一樣增補到備份檔案。

============================================== ==================================================== ==

innobackupex全備流程：

1、啟用xtrabackup_logfile。用於在整個熱備過程中，InnoDB儲存引擎下新的DML操作產生資料變更時，在xtrabackup_logfile中即時記錄這些新的資料變化，記錄格式同redo log

2、以page為單位複製InnoDB儲存的資料檔：共享表空間ibdataX和.ibd檔。由於複製時page可能正在寫入，page的頭、尾checksum值將會不同。故之後產生備份檔案在時間使用前先需要apply log，修復部分不完整的page。

3、flush tables with read lock。對MyISAM表加讀鎖，用於複製非事務引擎MyISAM儲存的資料

4、複製 .frm、.MYD、.MYI檔。

5、取得備份完成那一刻，binlog走到的最新位置點：xtrabackup_binlog_info（InnoDB資料檔可能有更新）。

6、unlock tables;

7、（1）備份結束，記錄啟動備份所需的最小參數到backup-my.cnf

（2）記錄LSN到xtrabackup_logfile。

（3）記錄備份類型（full-backuped：全量、incremental：增量；已經apply log過的備份將會修改為full-prepared）等資訊到xtrabackup_checkpoints。

（4）記錄其他一些備份資訊：xtrabackup_info

下面整理一下除了拷貝資料目錄下的庫表資料、表空間檔案（ibdata）、redo log（ib_logfile）以外，全備產生的檔案：

（1）backup-my.cnf

（2）xtrabackup_binlog_info：夾雜MyISAM進行資料備份時，較xtrabackup_binlog_pos_innodb更準缺

（3）xtrabackup_binlog_pos_innodb：apply log之後新產生的文件，只記錄innodb的binlog位點，而不會計算MyISAM產生的binlog

（4）xtrabackup_checkpoints

#（5）xtrabackup_info

（6）xtrabackup_logfile（核心檔案）

（7）xtrabackup_slave_info（從庫備份重要檔案）：需要在備份時加上--slave-info選項，將在該檔案中記錄「change master to ...” 訊息。使用備份檔案對從庫進行還原後，將會依賴該資訊重新指向主庫進行同步。

============================================== ==================================================== ==

innobackupex增量備份過程

innobackupex在增量備份InnoDB表資料的時候，相較於全備的過程，增備在複製page的時候，將會比備份文件與目前資料的page的LSN，有變更的資料相關的page，其LSN就會成長。所以innobackupex只需要備份LSN有變化的page。

而備份MyISAM時，仍執行的是全備的操作。

============================================== ==================================================== ==

備份語句範例

備份帳戶所需的權限：RELOAD、LOCK TABLES、REPLICATION CLIENT

（1）全備：

step1：

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=username --password='user_passwd' --host=【HOST】-- port=【PORT】 --no-timestamp /tmp/innobackup_all

step2：

innobackupex --apply-log --defaults-file=/tmp/innobackup_all/backup-my.cnf --user=username --password='user_passwd' --host=【HOST】--port=【PORT】 --/tmp/innobackup_all

（2）部分備份（Partial Backups）：備份形如：mydatabase.mytable

step1：

使用--include配合正規表示式

###innobackupex --include='^mydatabase[.]mytable' /path/to /backup --no-timestamp#########使用--tables-file配合一份記錄完整表名的文字檔案（每行一個表名）######echo "mydatabase.mytable " > /tmp/tables.txt######innobackupex --tables-file=/tmp/tables.txt /path/to/backup --no-timestamp#######

使用--databases指定庫、表（例如備份表：mydatabase.mytable和庫：mysql）

innobackupex --databases="mydatabase.mytable mysql" /path/to/backup --no -timestamp --user=backup --password=backup

step2：

prepare部分備份：innobackupex --apply-log --export /path/to/backup/

（--databases未指定的庫表，在prepare階段會提示“does note exist”，可以忽略此資訊）

（3）增量備份（假設已有全備，路徑：$FULLBACKUP）

step1：

第一次增量備份（基於全備）：innobackupex --incremental $INCREMENTALBACKUP_1 --incremental-basedir=$FULLBACKUP --user=USER --password=PASSWORD

第二次增量備份（基於第一次增量備份）：innobackupex --incremental $INCREMENTALBACKUP_2 --incremental-basedir=NCREMENTALBACKUP_1 --user=USER -- password=PASSWORD

（......）

第N次

step2：prepare

innobackupex -- apply-log --redo-only $FULLBACKUP --use-memory=1G --user=USER --password=PASSWORD

innobackupex --apply-log --redo-only $FULLBACKUP--incremental- dir=$INCREMENTALBACKUP_1 --use-memory=1G --user=DVADER --password=D4RKS1D3

innobackupex --apply-log --redo-only $FULLBACKUP --incremental-dir=$ INCREMENTALBACKUP_2 --use-memory=1G --user=DVADER --password=D4RKS1D3

（......）

innobackupex --apply-log--redo -only  $FULLBACKUP --incremental-dir=$INCREMENTALBACKUP_N --use-memory=1G --user=DVADER --password=D4RKS1D3

innobackupex --apply-log $FULLBACK memory=1G --user=$USERNAME --password=$PASSWORD

--use-memory：指定prepare可以使用的內存，與--apply-log 配合使用，加快prepare的速度

prepare階段，第一次全備及增量備份整合過程中需要新增--redo-only。最後，在所有增量備份整合過，需要再一次prepare整合進增量備份的全備檔案。

============================================== ==================================================== ==

其他一些常用參數：

搭配使用：--stream=xbstream --compress --compress-threads=8 --parallel=4 > backupfile.xbstream（xbstream選項會逐一對錶的ibd檔案進行compress和stream，故需要innodb-file-per-table參數開啟）

--parallel：備份並發數（指的是拷貝ibd檔案，區別於compress-threads是執行壓縮的執行緒數）

--stream：tar、xbstream。常常如此搭配使用：innobackupex [...] --stream=tar /backupdir/ | gzip - > backupfile.tar.gz

--tmpdir：在stream到遠端機器前，暫存的目錄位置

--encryption：備份加密。實際情況下較常用的是

（1）openssl，在上述tar+gz的方式上加入加密選項：innobackupex [...] --stream=tar /backupdir/ | gzip -  | openssl aes -256-cbc -k "abc" > backupfile.tar.gz.aes-256-cbc

（2）des3，innobackupex [...] --stream=tar /backupdir/ | gzip - | openssl des3 -salt -k "abc" > backupfile.tar.gz.des3

====================== ==================================================== ========================

innobackupex恢復過程

1、innobackupex -- apply-log，目的是從xtrabackup_log取得redo日誌，更新部分不完整的page，使頭尾checksum值，而LSN更新到備份過程中最新的LSN號；（實際應劃分到備份過程中）

2、複製備份資料到資料庫資料目錄；

3、修改資料目錄權限，並啟動。

============================================== ==================================================== ==

恢復語句範例：

1、恢復前關閉實例

2、備份原有資料目錄（redo log、undo log分立出去的話也需要備份）

3、innobackupex --copy-back --user=username --password='user_passwd' --socket=/usr/local/mysql/run/mysqld.sock --defaults-file=/usr /local/mysql/my.cnf  /tmp/innobackup_all （或直接拷貝prepared過的備份檔案）

4、修改目錄權限，啟動mysql

===== ==================================================== =========================================

從全備中匯出單表資料（前提是需要開啟innodb_file_per_table選項）

With the Percona XtraBackup, you can export individual tables from any InnoDB data, and import them into Percona Server with XtraDB or MySQL 5.6 (The source source source' t have to be XtraDB or or MySQL 5.6, but the destination does). This only works on individual .ibd files, and cannot export a table that is not contained in its own .ibd file.

##. prepare階段透過--export選項將單表匯出：

Once a full backup is created, prepare it with the --export option:

$ innobackupex --apply-log --export /path/to/backup

This will create for each InnoDB with its own tablespace a file with .exp extension.

將會為每個innodb表的表空間建立一個以.exp結尾的檔案

輸出檔形如：

/data/backups/mysql/test/export_test.exp
/data/backups/mysql/test/export_test.ibd
/data/backups/mysql/test/export_test.cfg

在其他伺服器進行表格的匯入時，需要先create table（是因為獨立表格檔案內無表格結構資訊）：

mysqlfrm --diagnostic /data/2017-03-22_16-13-00/yayun/t1.frm（使用mysql-utilities工具中的mysqlfrm從備份檔案讀取表格結構）

mysql> CREATE TABLE mytable (...) ENGINE=InnoDB;（依據先前讀取的表格結構，去create table）

#刪除表格空間檔案：

mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

將匯出的.ibd 和.exp檔案複製到資料目錄下：

#After this , copy mytable.ibd and mytable.exp ( or mytable.cfg if importing to MySQL 5.6) files to database's home

#再import tablespace：

mysql> ALTER TABLE mydatabase.table IMPORT TABLESPACE;

----------------------------------------- ---

------邏輯備份工具mydumper------

------------------- -------------------------

部分英文文獻摘自GitHub上的README：https://github.com/maxbube/ mydumper

在5.5/5.6版本的MySQL資料庫中，相較於採用官方提供的mysqldump進行單執行緒備份，多執行緒備份工具mydumper有著得天獨厚的優勢。 （MySQL 5.7.11之後的版本，官方終於修復了平行邏輯備份工具mysqlpump 一致性備份的問題。關於mysqlpump可參考姜承堯大牛的介紹：http://www.tuicool.com/articles/E77bYz7 ）

劣勢：較難以並發流的方式備份到遠端備份中心，更多的是直接本地落盤。

== How does consistent snapshot work? ==
This is all done following best MySQL practices and traditions:

#* As a precaution, slow running queries on the server either aborabort the dump, 或 get killed
* Global write lock is acquired ("FLUSH TABLES WITH READ LOCK")
* Various metadata is read ("SHOW SLAVE STATUS","SHOW MASTER STATUS")
* Other threads* Other threads* Other threads* Other threads* Other threads* connect and establish snapshots ("START TRANSACTION WITH CONSISTENT SNAPSHOT")
** On pre-4.1.8 it creates dummy InnoDB table, and reads from it.
* Once all worker threads announce the lishna "UNLOCK TABLES" and starts queueing jobs.

mydumper對於一致性的實作機制：

* 遇到有慢查詢的情況，要麼dump停止執行，要麼mydumper將慢查詢kill掉。 （由--long-query-guard 參數約定一個慢查詢的時間、預設為60秒，--kill-long-queries加入該參數則主動kill掉慢查詢，不加則mydumper在遇到慢查詢的情況下將停止運行）
* 使用"FLUSH TABLES WITH READ LOCK" 施加全域的讀鎖，會阻止DML語句
* 查看元資料："SHOW SLAVE STATUS","SHOW MASTER STATUS"

* "START TRANSACTION WITH consistent snapshot"：start transaction開啟交易的同時，立即建立目前交易一致性讀的快照。沒有with選項的話，會等到執行到事務中的第一條語句時，才真正開始事務、建立一致性讀取的快照

* 4.1.8版本開始，mydumper創建InnoDB類型的虛表，從中讀資料

* 一旦所有的執行緒都回饋一致性快照建立完成，則執行 "UNLOCK TABLES"，並開啟佇列任務。

備份語句範例：

mydumper --user=username --password=user_passwd --socket=/... --regex '^( ?!(mysql))' --output=/backupdir --compress --verbose=3 --logfile=/backupdir/mydumper_backup.log

常用參數釋疑：

--database指定需要備份的函式庫
--tables-list 指定需要備份的表，用，分隔（與regex option衝突時，以regex為準）

--regex '^(?!(mysql |test))'：資料庫過濾選項

--output=/backupdir：備份檔案輸出路徑

--compress：壓縮方式輸出檔案（.gz後綴）

--verbose=3：輸出日誌等級info，方便觀察備份狀況（0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2）

--logfile=/ backupdir/mydumper_backup.log：指定mydumper執行日誌檔案的位置

--threads 指定備份時使用的執行緒數，預設為4

#--statement-size ：限制sql語句的最大長度（mydumper在備份時會合併sql）
--rows ： 以行數分割表。提高myloader時的並發效能
--chunk-filesize ： 依輸出檔案的大小分割表格資料。提高myloader時的並發效能
--no-locks ： 不鎖定表（可能資料不一致）
--binlogs ：備份binlog。當備份失敗時，可以查看備份的binlog，在備份時位置點附近尋找出錯原因

#輸出備份檔案目錄：

* 庫結構：dbname-schema-create. sql.gz

* 表結構：dbname.tblname1-schema.sql.gz

#

* 表格資料：dbname.tblname1.sql.gz

（每個函式庫、表格都有自己獨立的備份檔案。當只需進行單表還原時，透過mydumper還原單表全量資料+binlog復原增量）

* metadata：包含備份時，binlog目前位置點

------------------ ----------------------------------------------

#Started dump at: 2017-07-04 09:45:57
SHOW MASTER STATUS:
Log: mysql-bin.000048
Pos: 107
GTID:(null)
Finished dump at: 2017-07-04 09:45:57

--------------------------- --------------------------------------

* mydumper_backup.log ：記錄了備份程式運行的情況

恢復指令myloader相關參數釋疑
--directory 備份檔案位置
--queries-per-transaction 每個交易執行的sql數，預設為1000
--overwrite-tables 已存在的表先drop掉再恢復（要求備份檔案時候要備份表結構）
--database 指定需要還原的資料庫
--enable-binlog 為還原資料的作業記錄binlog
--threads 指定還原時使用的執行緒數，預設為4

--enable-binlog：還原已備份的binlog

註：myloader只能在庫層級層面進行恢復，單表恢復可以直接調用備份檔案中對應的含有sql語句的檔案

此外，innobackupex備份的是備份完整時，這一個時間點之前的數據，而mydumper（包括mysqldump 、mysqlpump等）備份的資料的時間點是備份開始時的時間。

提一點復原的主體思想：無論是採用實體備份，或是邏輯備份，最穩健的復原前提是資料庫需暫時禁止資料的寫入。然後先恢復全量備份，應用增量備份到最近的故障點，再套用binlog日誌並跳過故障點。

始終要考慮，為了在單一表上的幾條誤操作語句而在線上伺服器上停止寫入操作、透過全量+增量恢復的方法未免有點興師動眾，得不償失。若沒有複製延遲的策略的備庫的話，採用mydumper備份的檔案去還原單表、或退一步利用flashback才是又快又好的處理方法。

Tips：

在使用 Innobackupex 或 mydumper還原大部分資料後，利用mysqlbing補齊上述備份程式無法覆寫的資料部分。

mysqlbinlog參數釋疑：

–start-position=N（讀取時包含）
從二進位日誌中第1個位置等於N參量時的事件開始讀取。
–stop-position=N（讀取時不包含）
從二進位日誌中第1個位置等於和大於N參量時的事件起停止讀取。

使用mysqlbinlog應用binlog日誌時，如果需要跨多個文件，則同時讀取多個，start-position為第一個binlog文件的起始點，stop-position為最後一個文件的終止點。

範例：mysql-bin.000048（pos856），mysql-bin.000051（pos1042）

/usr/local/mysql/bin/mysqlbinlog mysql-bin.000048 mysql-bin. 000049 mysql-bin.000050 mysql-bin.000051 --start-position=856 --stop-position=1042 > /tmp/backup1/backup_new.sql

Tips：

總是要有備份監控；

上面兩款備份工具的備份物件主要包含在資料目錄中，要注意的是binlog中也含有一部分數據，對binlog也需要做好備份。

關於備份策略簡單提一點，我們所製定的備份策略是要根據業務類型來決定的。

對於資料成長型業務採取的是全量+增量的策略，而對於資料更新型則採用全量備份。

為了進行MySQL版本升級或單表還原等作業常常採用的邏輯備份。

綜上考慮，線上資料庫一般是採取實體備份為主，邏輯備份為輔，再加上binlog的備份。

參考文件：

innobackupex備份產生文件說明：

http://fordba.com/xtrabackup-produce-file-intruduction.html

# Recipes for innobackupex：

https://www.percona.com/doc/percona-xtrabackup/LATEST/how-tos.html#recipes-ibk

#如何從innobackupex產生的全備中恢復單表：

https://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/restoring_individual_tables_ibk.html

https:// www.percona.com/blog/2012/01/25/how-to-recover-a-single-innodb-table-from-a-full-backup/

#https://www .percona.com/blog/2017/03/15/restore-single-innodb-table-full-backup-accidentally-dropping/

##實際還原單表案例：

# http://www.cnblogs.com/gomysql/p/6600616.html

建立及恢復Partial Backups：

https://www.percona.com /doc/percona-xtrabackup/2.2/innobackupex/partial_backups_innobackupex.html

mysqldump結合binlog進行恢復的案例：

http://blog.chinaunix.net/uid-25135004-id- 1761635.html

http://www.cnblogs.com/hanyifeng/p/5756462.html

使用mysqldump全備檔案進行單庫復原（待測試）

https://stackoverflow.com/questions/1013852/can-i-restore-a-single-table-from-a-full-mysql-mysqldump-file