MySQL邏輯備份的過程及相關重要參數說明

一、重要參數

邏輯備份常用的指令就是MySQL自帶的mysqldump指令，這個指令要着重關注下面的幾個關鍵參數：

--single-transaction：

該參數隻對innodb表起作用，作用是保證備份出來的innodb表資料是一緻性的。具體的實作原理是：将目前會話的事務隔離級别設定為RR級别，這樣保證在一個事務中讀取到一緻性資料；然後顯示地開啟一個事務，也就是dump線程會執行“start transaction”指令。這樣在這個事務期間，查詢并導出所有innodb表資料，就可以保證導出的資料的一緻性，這也就是為什麼邏輯備份的恢複起點是備份的開始時刻。

--master-data：

該參數的作用就是記錄邏輯備份開始時的binlog位置(也就是恢複的起點)。具體實作的方式是：這個參數會打開--lock-all-tables參數，這時dump線程就會執行“flush tabls with read lock”，鎖住資料庫中所有表；然後再執行“show master status”指令導出binlog位置資訊，以達到記錄binlog位置的目的。至于FTWRL鎖什麼時候釋放？如果同時指定了--single-transaction參數的話，在記錄完binlog位置後就會釋放，是以持有FTWRL鎖的時間很短；但如果沒有指定single-transaction參數，那麼會鎖定到備份結束。這個選項會自動關閉–lock-tables

--lock-all-tables：

該參數的作用是給資料庫中的所有庫的所有表都加上FTWRL鎖(非innodb和innodb表都加鎖)，一次性加鎖，直到備份結束後釋放鎖。是以這個參數一開啟的話，整個系統都不能寫入。并且這個參數和--single-transaction是沖突的，隻能二選其一。

--lock-tables：

該參數表示鎖定某個庫的所有表。如果你隻是備份一個庫，例如備份test庫，就可以使用--lock-tables來鎖定test庫的所有表，而其它庫的表并不會被鎖定。該參數與--lock-all-tables的不同就是其鎖定的範圍小，是以能使用--lock-tables就不使用--lock-all-tables，特别是隻備份部分庫的情況下。在備份時，該參數是一次性鎖定指定庫的所有表，并不是鎖定目前導出表（這個可以參考部落格：

https://www.cnblogs.com/kerrycode/p/6963880.html

）

二、mysqldump備份過程

通常進行邏輯備份時，我們都會使用“--single-transaction”+“--master-data”的參數組合。那麼這個備份過程是怎樣的呢?

備份過程可以分為下面幾大步：

1、執行FTWRL，全局禁止讀寫

一般資料庫的讀寫都很頻繁的，不全局鎖表的話後面是沒法記錄備份開始時的binlog位置的。

2、開啟快照讀

FTWRL鎖加完後，dump線程就在會話級别上設定隔離級别為RR，然後顯式地開啟一個事務：START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT 。

3、擷取binlog資訊

然後就是執行show master status指令。記錄下binlog位置。

4、釋放FTWRL鎖

記錄完binlog資訊後，立即釋放鎖(執行“unlock tables”)，這樣可以保證其它業務對表進行正常讀寫了。

5、備份innodb表資料

逐一地備份表資料，通過innodb的MVCC特性來達到備份時的innodb表資料一緻性，直接在事務内執行“select * from tb_name”語句擷取表資料；如果系統中還有myisam表的話，則會以同樣的方式備份myisam表，但備份出來myisam表資料并不一緻。至此備份過程結束。

三、邏輯備份相關問題說明

1、--single-transaction和--lock-all-tables為什麼沖突

--single-transaction參數會開啟一個事務，然後在該事務内通過select語句備份表資料；而--lock-all-tables參數在執行lock tables時會隐式送出所有pending事務，這會影響到--single-transaction參數，是以不能共用。

2、庫中既有innodb表又有myisam表，該如何保證備份資料一緻性

這是邏輯備份繞不開的一個話題：如果備份表隻有innodb表，那麼通過--single-transaction就可以保證資料一緻性；如果備份表隻有myisam表，那麼隻能通過--lock-all-tables或者--lock-tables參數鎖表來達到資料一緻性。

如果待備份的庫基本都是innodb表，但含有少量myisam表，為了保證最大的并發性，可這樣實施：

先隻備份innodb表，通過--ignore-table參數忽略myisam表；

再單獨備份myisam表，通過參數--tables指定myisam表；

這時兩個備份的時間點是不一緻的，第一個備份比第二個備份時間點要早，可以先将第一個備份通過跑binlog跑到第二個備份的時間點來達到一緻性

以上方式可最大限度提高并發性，但實施較複雜，如果希望快速備份，那麼就直接--lock-all-talbes鎖定全表備份。

3、為什麼邏輯備份這麼慢

邏輯備份一般隻适合小容量的備份。可以從邏輯備份的實際過程來看，以innodb表備份為例，mysql是在開啟的事務内通過執行“select * from tb_name”的方式來備份資料的，如果該表是一張千萬級别乃至億級别的大表，如果有很多這樣的大表......是以對于大資料量的備份基本采用實體備份。