mysql邏輯備份 mysqldump

MySQL邏輯備份mysqldump 是我們平時用的比較多的備份方式，那么myqldump的備份原理是什么？是如何保證備份數據一致性的呢？
為了觀察mysql在邏輯備份mysqldump 的時候，究竟做了哪些操作，我們開啟全量日志！

然后我們開始我們的備份操作：mysqldump -uroot -p123456 --master-data=2 --single-transaction -A >/tmp/yh2.sql
這里說說比較重要的兩個參數：
--single-transaction

在開啟dump操作時，會創建一個快照，這相當于此刻的一致性視圖，即在整個dump的過程中是被當做一個事務的，這樣就能保證同一事務下讀取的數據都是一致的。但是此時如果有其他會話在做DDL操作（ALTER/DROP/RENAME/TRUNCATE TABLE）,則會破壞數據的一致性，所以需要添加lock-tables鎖住表用來保證數據的一致性。
--master-data

此命令是為了獲取在dump時候的master 的binlog文件名和position的位置。當他等于1時，顯示change master的輸出結果。等于2時，注釋掉此命令的輸出結果。由此我們可以知道，當等于1時dump出來的數據，恢復在slave上是非常方便的。

現在我們查看剛才我們備份時候的全量日志，日質量比較打，我貼出部分主要的，
2017-12-07T07:32:24.917291Z 40 Connect root@localhost on using Socket
2017-12-07T07:32:24.917690Z 40 Query /!40100 SET @@SQL_MODE='' /
2017-12-07T07:32:24.926840Z 40 Query /!40103 SET TIME_ZONE='+00:00' /
2017-12-07T07:32:24.927033Z 40 Query FLUSH /!40101 LOCAL / TABLES
2017-12-07T07:32:24.928911Z 40 Query FLUSH TABLES WITH READ LOCK
2017-12-07T07:32:24.928994Z 40 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-12-07T07:32:24.929079Z 40 Query START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT /
2017-12-07T07:32:24.929252Z 40 Query SHOW VARIABLES LIKE 'gtid_mode'
2017-12-07T07:32:24.992104Z 40 Query SELECT @@GLOBAL.GTID_EXECUTED
2017-12-07T07:32:24.992528Z 40 Query SHOW MASTER STATUS
2017-12-07T07:32:24.992613Z 40 Query UNLOCK TABLES
2017-12-07T07:32:24.992735Z 40 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2017-12-07T07:32:24.995255Z 40 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2017-12-07T07:32:24.996104Z 40 Query SHOW DATABASES
2017-12-07T07:32:24.997945Z 40 Query SHOW VARIABLES LIKE 'ndbinfo_version'
2017-12-07T07:32:24.999984Z 40 Init DB mysql
2017-12-07T07:32:25.000096Z 40 Query SHOW CREATE DATABASE IF NOT EXISTS mysql
2017-12-07T07:32:25.000211Z 40 Query SAVEPOINT sp
2017-12-07T07:32:25.000314Z 40 Query show tables
省略…………………………
2017-12-07T07:32:25.142111Z 40 Query use yhte
2017-12-07T07:32:25.142187Z 40 Query select @@collation_database
2017-12-07T07:32:25.142272Z 40 Query SHOW TRIGGERS LIKE 't'
2017-12-07T07:32:25.142579Z 40 Query SET SESSION character_set_results = 'utf8'
2017-12-07T07:32:25.142651Z 40 Query ROLLBACK TO SAVEPOINT sp
2017-12-07T07:32:25.142714Z 40 Query RELEASE SAVEPOINT sp
2017-12-07T07:32:25.142775Z 40 Init DB yhtest
2017-12-07T07:32:25.142830Z 40 Query SHOW CREATE DATABASE IF NOT EXISTS yhtest
2017-12-07T07:32:25.142991Z 40 Query SAVEPOINT sp
2017-12-07T07:32:25.143060Z 40 Query show tables
2017-12-07T07:32:25.143298Z 40 Query show table status like 't1'
2017-12-07T07:32:25.143799Z 40 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-12-07T07:32:25.143872Z 40 Query SET SESSION character_set_results = 'binary'
2017-12-07T07:32:25.143972Z 40 Query show create table t1
2017-12-07T07:32:25.144064Z 40 Query SET SESSION character_set_results = 'utf8'
2017-12-07T07:32:25.144154Z 40 Query show fields from t1
2017-12-07T07:32:25.144543Z 40 Query show fields from t1
2017-12-07T07:32:25.144951Z 40 Query SELECT /!40001 SQL_NO_CACHE / FROM t1
2017-12-07T07:32:25.145135Z 40 Query SET SESSION character_set_results = 'binary'
2017-12-07T07:32:25.145207Z 40 Query use yhtest
2017-12-07T07:32:25.145282Z 40 Query select @@collation_database
2017-12-07T07:32:25.145366Z 40 Query SHOW TRIGGERS LIKE 't1'
2017-12-07T07:32:25.145668Z 40 Query SET SESSION character_set_results = 'utf8'
2017-12-07T07:32:25.145740Z 40 Query ROLLBACK TO SAVEPOINT sp
2017-12-07T07:32:25.145813Z 40 Query show table status like 't122'
2017-12-07T07:32:25.146389Z 40 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-12-07T07:32:25.146464Z 40 Query SET SESSION character_set_results = 'binary'
2017-12-07T07:32:25.146533Z 40 Query show create table t122
2017-12-07T07:32:25.146621Z 40 Query SET SESSION character_set_results = 'utf8'
2017-12-07T07:32:25.146702Z 40 Query show fields from t122
2017-12-07T07:32:25.147099Z 40 Query show fields from t122
2017-12-07T07:32:25.147395Z 40 Query SELECT /!40001 SQL_NO_CACHE / FROM t122
2017-12-07T07:32:25.147545Z 40 Query SET SESSION character_set_results = 'binary'
2017-12-07T07:32:25.147615Z 40 Query use yhtest
2017-12-07T07:32:25.147690Z 40 Query select @@collation_database
2017-12-07T07:32:25.147771Z 40 Query SHOW TRIGGERS LIKE 't122'
2017-12-07T07:32:25.148128Z 40 Query SET SESSION character_set_results = 'utf8'
2017-12-07T07:32:25.148201Z 40 Query ROLLBACK TO SAVEPOINT sp
2017-12-07T07:32:25.148273Z 40 Query show table status like 'yh2'
2017-12-07T07:32:25.149056Z 40 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-12-07T07:32:25.149129Z 40 Query SET SESSION character_set_results = 'binary'
2017-12-07T07:32:25.149199Z 40 Query show create table yh2
2017-12-07T07:32:25.149582Z 40 Query SET SESSION character_set_results = 'utf8'
2017-12-07T07:32:25.149665Z 40 Query show fields from yh2
2017-12-07T07:32:25.150010Z 40 Query show fields from yh2
2017-12-07T07:32:25.150293Z 40 Query SELECT /!40001 SQL_NO_CACHE / * FROM yh2
2017-12-07T07:32:25.150443Z 40 Query SET SESSION character_set_results = 'binary'
2017-12-07T07:32:25.150513Z 40 Query use yhtest
2017-12-07T07:32:25.150587Z 40 Query select @@collation_database
2017-12-07T07:32:25.150668Z 40 Query SHOW TRIGGERS LIKE 'yh2'
2017-12-07T07:32:25.151022Z 40 Query SET SESSION character_set_results = 'utf8'
2017-12-07T07:32:25.151096Z 40 Query ROLLBACK TO SAVEPOINT sp
2017-12-07T07:32:25.151158Z 40 Query RELEASE SAVEPOINT sp
2017-12-07T07:32:25.158814Z 40 Quit

通過全量日志，我們大致可以看出mysqldump的執行過程
1可以看出dump命令鏈接正式進入數據庫。
2 flushtables操作。該操作會將內存中緩存的表結構數據同步到磁盤中。
3做了FLUSH TABLES WITH READ LOCK操作獲得一個全局鎖，確保此時數據是一致的。
4將當前會話的事務隔離級別恢復成默認的RR模式，使得當前事務可重復讀。
5開啟一個事務，并設置成快照級別。
6查詢數據庫的GTID是否開啟。
7獲取binlog的文件名及position的位置。
8釋放全局鎖。
9通過select語句查詢test1庫的狀態。
10查詢一下字典。
11進入要備份的test1庫。
12查看建庫語句。
13創建一個事務恢復點sp。
14查看需要備份的庫中都有哪些表。
15查看表T1狀態。
16設置表名和列名的格式。
17設置字符集為二進制。
18查看T1的建表語句。
19設置字符集為UTF8。
20輸出表的所有信息。
21查詢T1中數據，如果表中數據很大，mysql會使用limit來進行分段獲取。
22設置字符集為二進制。
23進入test1庫。
24查看數據庫的編碼格式。
25查看T1表的觸發器。
26設置字符集為UTF8。
27ROLLBAKC到sp事務點。
28相同操作獲取test1庫中的T2表數據。
29當所有數據都獲取完成后釋放掉事務回滾點sp。

分析mysqldump過程：

1. FLUSH /!40101 LOCAL / TABLES

   Closes all open tables, forces all tables in use to be closed, and flushes the query cache.

2. FLUSH TABLES WITH READ LOCK

   執行flush tables操作，并加一個全局讀鎖，很多童鞋可能會好奇，這兩個命令貌似是重復的，為什么不在第一次執行flush tables操作的時候加上鎖呢？

   下面看看源碼中的解釋：
   /
   We do first a FLUSH TABLES. If a long update is running, the FLUSH TABLES
   will wait but will not stall the whole mysqld, and when the long update is
   done the FLUSH TABLES WITH READ LOCK will start and succeed quickly. So,
   FLUSH TABLES is to lower the probability of a stage where both mysqldump
   and most client connections are stalled. Of course, if a second long
   update starts between the two FLUSHes, we have that bad stall.
   /
   簡而言之，是為了避免較長的事務操作造成FLUSH TABLES WITH READ LOCK操作遲遲得不到鎖，但同時又阻塞了其它客戶端操作。

3. SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

   設置當前會話的事務隔離等級為RR，RR可避免不可重復讀和幻讀。

   START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT /

   獲取當前數據庫的快照，這個是由mysqldump中--single-transaction決定的。我們對比下加了該參數和沒加的區別，日志如下：
   有--single-transaction 參數：
   
   無--single-transaction 參數：
   
   可以看到，當我們不加參數--single-transaction 參數時，將會少了對隔級別設置，少了開啟事物一致性快照，少了unlock tables；

4. SHOW MASTER STATUS

   這個是由--master-data決定的，記錄了開始備份時，binlog的狀態信息，包括MASTER_LOG_FILE和MASTER_LOG_POS

5. UNLOCK TABLES 釋放鎖。

在備份過程中還有一個操作，設置保存點， 其實，這樣做不會因為元數據鎖阻塞在備份期間對已經備份表的ddl操作。
/
ROLLBACK TO SAVEPOINT in --single-transaction mode to release metadata
lock on table which was already dumped. This allows to avoid blocking
concurrent DDL on this table without sacrificing correctness, as we
won't access table second time and dumps created by --single-transaction
mode have validity point at the start of transaction anyway.
Note that this doesn't make --single-transaction mode with concurrent
DDL safe in general case. It just improves situation for people for whom
it might be working.*
/

模仿這個步驟測試一下

測試1：
隔離級別 RR模式，開啟一個會話A執行如下操作：

開啟一個會話B執行如下操作：

開啟一個會話C 查看狀態，show processlist

我們可以看到，這樣B會話是會被A會話元數據所阻塞的，這個時候，如果在有其他會話訪問t1 表，同樣會被B會話阻塞！

測試2

在A會話執行如下操作：

在B會話執行

我們看到B會話的DDL操作是可以執行成功的。
在A會話執行 如下操作

在B會話執行

B會話也是可以執行成功的。

測試三：
A會話執行如下操作：

B會話執行

這個時候我們看出B會話被A會話阻塞，通過show processlist； 也可以看出B會話等待元數據鎖，
我們在A會話跑 rollback to SAVEPOINT sp;

這個時候我們看到 B會話執行成功了

通過 SAVEPOINT sp; 設置保存點，通過rollback to SAVEPOINT sp; 相當于在select 完成后，立即釋放了該表的元數據鎖，而不會等到本次會話提交，這樣可以避免DDL長時間無法獲得元數據鎖，從而導致該表的其他查詢操作等待。
從這三個測試我們也可以看出START TRANSACTION WITH CONSISTENT SNAPSHOT開啟的事務只能通過commit或者rollback來結束，而不是ROLLBACK TO SAVEPOINT sp。
需要注意的是，如果 alter table 發生在select from t1 之前，也就是第二種測試情況，DDL是可以提交成功的，那么我們再進行 select from t1 查詢的時候，是會報錯的，

發生在備份中，同樣會報錯！
總結：
至此，我們可以總結下mysqldump的備份原理：通過設置READ LOCK獲取數據庫全局鎖后，RR事務隔離級別下記錄當前的日志文件名和日志位置position，然后釋放掉全局鎖。接下來創建一個事務的回滾點，所有數據的獲取都是獲取的是這個sp回滾點數據。最后釋放掉回滾點sp。當然，對于MyISAM存儲引擎，備份是直接鎖全表的。
值得注意點：從分析mysqldump過程中我們可以知道，此命令在開始時刻會帶來數據庫瞬時的鎖定（FLUSHTABLES WITH READ LOCK），雖然鎖定時間是非常短暫的，但是卻會帶來非常大的數據庫隱患，因為在此過程中，如果執行有DDL語句，就會導致此命令堵塞并最終異常退出。所以在做備份時間節點的選擇上，需要根據數據庫環境選擇在負載壓力最小，且沒有以上操作時候進行備份。

1. mysqldump的本質是通過select * from tab來獲取表的數據的。

   2. mysqldump只適合放到業務低峰期做，如果備份的過程中數據操作很頻繁，會造成Undo表空間越來越大，undo表空間默認是放到共享表空間中的，而ibdata的特性是一旦增大，就不會收縮。

2. mysqldump的效率還是比較低下，START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT /只能等到所有表備份完后才結束，其實效率比較高的做法是備份完一張表就提交一次，這樣可盡快釋放Undo表空間快照占用的空間。但這樣做，就無法實現對所有表的一致性備份。

3. 為什么備份完成后沒有commit操作

   /*
   No reason to explicitely COMMIT the transaction, neither to explicitely
   UNLOCK TABLES: these will be automatically be done by the server when we
   disconnect now. Saves some code here, some network trips, adds nothing to
   server.

   */

從以上截圖可以看出，當我們開啟第一個會話后，不進行提交，這個回收，第二個會話dump是 處于waiting for table flush 狀態，第三個會話，讀取和第一個會話同一張表的時候，也是會處于waiting for table flush 狀態， 而不是被dump 阻塞， 當第三個會話讀取其他表的時候，是可以正常讀取的，也不會被dump阻塞