mysqldump和xtrabackup備份原理實作說明

MySQL資料庫備份分為邏輯備份和實體備份兩大類，猶豫到底用那種備份方式的時候先了解下它們的差異：

到底選擇那種備份方式，具體根據自己的實際情況，如需要的是熱備還是冷備？資料量大不大？磁盤空間夠不夠等因素決定。

邏輯備份工具主要有：mysqldump、mysqlpump、mydumper，實體備份工具主要有：xtrabackup。

現在使用最多的備份就是mysqldump、xtrabackup（都支援支援熱備），本文就mysqldump（mysqlpump）和xtrabackup的原理進行下大緻的說明。

1）參數說明，具體的參數可以用mysqldump --help檢視。

在說明mysqldump之前先了解下它的相關參數，可以通過mysqldump --help進行檢視，也可以通過mysqldump、mysqlpump備份工具說明和mysqldump的流程進行了解。這裡再重申一下幾個比較重要的參數。

①：--single-transaction

②：--master-data

③：--lock-all-tables ，-x

④：<code>--lock-tables，</code><code>-l</code>

⑤：<code>--flush-logs，</code><code>-F</code>

⑥：--opt

2）執行說明

①：邏輯備份就是導出SQL形式的檔案，其的具體實作步驟可以直接打開genaral_log。

②：備份需要保證資料庫的一緻性，即在某一時刻，整個資料庫的狀态是一緻的，這樣可以通過備份進行恢複成為另一個從庫。資料庫目前使用最多的存儲引擎是InnoDB，也有可能部分是MyISAM，建議把MyISAM存儲引起改成InnoDB。現在重點說明關于InnoDB的備份。

MyISAM表的備份選項：上面提過因為mysqldump預設開啟--opt選項，而--opt裡包含--lock-tables的選項，這個選項不能保證在多個資料庫下資料備份的一緻性，是以要麼--skip-opt，再把需要的選項添加進去，要麼就再使用--lock-all-tables的選項（開啟之後會關閉--lock-tables的選項），要是在從庫備份則隻需要添加--master-data選項（開啟之後自動打開--lock-all-tables選項）即可。

備份myisam表的指令：

因為開啟了--lock-all-tables選項（--master-data），保證一緻性讀和資料的一緻性。在備份開始時就會執行FLUSH TABLES WITH READ LOCK指令，這個指令是server層面的鎖，這樣後面任何存儲引擎執行DML、DDL語句都會 Waiting for global read lock狀态，這樣就保證了從備份點之後資料的一緻性。

InnoDB表的備份選項：和上面介紹MyISAM表的備份選項一樣，在此基礎上增加了--single-transaction的選項，這個選項保證了通過将導出操作封裝在一個事務(Repeatable Read)内來使得導出的資料是一個一緻性快照。隻有當表使用支援MVCC的存儲引擎（目前隻有InnoDB）時才可以工作，其他引擎不能保證導出是一緻的。這個選項開啟後會自動關閉--lock-tables選項，而--master-data選項自動打開–lock-all-tables選項，在設定了--single-transaction這種情況下，全局讀鎖隻會在開始dump的時候加上一小段時間（5.7之前），5.7之後不需要加鎖了。

一緻性快照，即一緻性讀取，那是如何保證一緻性讀的呢？具體的說明可以看MySQL 一緻性讀 深入研究這篇文章。大緻的說明可以看下面的測試說明：

測試1： 

sesseion A

session B

mysql&gt; set tx_isolation='repeatable-read';

Query OK, 0 rows affected (0.00 sec)

mysql&gt; begin;

Query OK, 0 rows affected (0.01 sec)

mysql&gt; select * from t1;

Empty set (0.00 sec)

mysql&gt; insert into t1(c1,c2) values(1,1);

Query OK, 1 row affected (0.01 sec)

+----+------+

| c1 | c2   |

|  1 |    1 |

1 row in set (0.00 sec)

結論1：RR隔離級别下的一緻性讀，不是以begin開始的時間點作為snapshot建立時間點，因為測試看出再begin一個事務的時候，表是沒有資料的，sessionB寫入資料之後，卻能看到資料。

測試2：

session A

mysql&gt; select * from t;

Empty set (0.00 sec) 

結論2：RR隔離級别下的一緻性讀，是以第一條select語句的執行點作為snapshot建立的時間點的，即使是不同表的select語句。這裡因為session A在insert之前對 t 表執行了select，是以建立了snapshot，是以後面的select * from t1 不能讀取到insert的插入的值(snapshot的時候t1表沒有資料)。 

測試3： 

Empty set (0.01 sec)

結論3：session A 的第一條語句，發生在session B的 insert語句送出之前，是以session A中的第二條select還是不能讀取到資料。因為RR中的一緻性讀是以事務中第一個select語句執行的時間點作為snapshot建立的時間點的。而此時，session B的insert語句還沒有執行，是以讀取不到資料。

測試4：

mysql&gt; insert into t1(c1,c2) values(1,1),(2,2);

|  2 |    2 |

2 rows in set (0.01 sec)

mysql&gt; update t1 set c2=100 where c1=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

|  1 |  100 |

結論4：本事務中進行修改的資料，即使沒有送出，在本事務中的後面也可以讀取到。update 語句因為進行的是“目前讀”，是以它可以修改成功。

通過上面的幾個測試得出的結論：對同一個表或者不同表進行的第一次select語句建立了該事務中一緻性讀的snapshot，在snapshot建立之後送出的資料，一緻性讀就讀不到，之前送出的資料就可以讀到。事務一緻性讀的起始點其實是以執行的第一條語句為起始點的，而不是以begin作為事務的起始點的。

一般begin/start transaction是開始一個事務的标志，但不是事務開始的時間點，也就是說執行了start transaction之後的第一個語句（任何語句），事務才真正的開始。但是如果要達到将 start transaction作為事務開始的時間點，那麼必須使用： 

這樣開啟事務效果等價于： start transaction 之後，馬上執行一條 select 語句(此時會建立一緻性讀的snapshot)。

測試5：

mysql&gt; start transaction;

測試6： 

mysql&gt; start transaction with consistent snapshot;

上面測試說明了 start transaction 和 start tansaction with consistent snapshot的差別。測試5說明，start transaction執行之後，一緻性讀并沒有開始，是以可以讀到session B插入的值。測試6說明，start transaction with consistent snapshot已經開始了一緻性快照，是以insert語句發生在事務開始之後，是以讀不到insert的資料。

是以事務一緻性快照開始時間點，分為兩種情況：

到此，大緻說明了參數single-transaction的意義，其實就是通過start transaction with consistent snapshot實作一緻性快照讀：通過将導出操作封裝在一個事務内來使得導出的資料是一個一緻性快照。隻有當表使用支援MVCC的存儲引擎（目前隻有InnoDB）時才可以工作，其他引擎（MyISAM）不能保證導出是一緻的。如備份開啟之後，通過一緻性快照記錄了mysql的binlog和position，此時往innodb表裡寫入資料，因為有一緻性快照，備份讀不到最新的記錄，但是該操作會在之前記錄binlog之後位置裡記錄，當還原的時候，直接應用之後的binlog記錄即可。而myisam不支援事務，沒有快照，備份直接取最新資料，而寫入操作會持續記錄到binlog裡，是以還原的時候會導緻一緻性被破環。當還原到一個新從并開啟同步的change（備份裡面記錄的點）之後，myisam表會出現主鍵沖突，而innodb表不會。

備份InnoDB表的指令：

上面講了這麼多，現在通過general_log看看mysqlbinlog備份步驟：

3）實作過程

通過2）裡的general_log的這些步驟，可以看到mysqldump的大緻實作過程是：連接配接 -&gt; 初始化資訊 -&gt; 重新整理表（鎖表）-&gt; 記錄偏移量 -&gt; 開啟事務（一緻性快照）-&gt; 記錄偏移量 -&gt; 解鎖表，因為開啟了一緻性讀，可以得到innodb的一緻性，又因為解鎖表了，MyISAM表一緻性得不到保證，是以盡量别使用MyISAM表。

本文轉自帥氣的頭頭部落格51CTO部落格，原文連結http://blog.51cto.com/12902932/1927046如需轉載請自行聯系原作者

sshpp