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

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

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

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

石首網(wǎng)站制作公司哪家好，找成都創(chuàng)新互聯(lián)！從網(wǎng)頁設(shè)計、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、成都響應(yīng)式網(wǎng)站建設(shè)等網(wǎng)站項目制作，到程序開發(fā)，運營維護。成都創(chuàng)新互聯(lián)于2013年開始到現(xiàn)在10年的時間，我們擁有了豐富的建站經(jīng)驗和運維經(jīng)驗，來保證我們的工作的順利進行。專注于網(wǎng)站建設(shè)就選成都創(chuàng)新互聯(lián)。

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

   執(zhí)行flush tables操作，并加一個全局讀鎖，很多童鞋可能會好奇，這兩個命令貌似是重復(fù)的，為什么不在第一次執(zhí)行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.
   /
   簡而言之，是為了避免較長的事務(wù)操作造成FLUSH TABLES WITH READ LOCK操作遲遲得不到鎖，但同時又阻塞了其它客戶端操作。

3. SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

   設(shè)置當(dāng)前會話的事務(wù)隔離等級為RR，RR可避免不可重復(fù)讀和幻讀。

   START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT /

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

4. SHOW MASTER STATUS

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

5. UNLOCK TABLES 釋放鎖。

在備份過程中還有一個操作，設(shè)置保存點， 其實，這樣做不會因為元數(shù)據(jù)鎖阻塞在備份期間對已經(jīng)備份表的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執(zhí)行如下操作：

開啟一個會話B執(zhí)行如下操作：

開啟一個會話C 查看狀態(tài)，show processlist

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

測試2

在A會話執(zhí)行如下操作：

在B會話執(zhí)行

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

在B會話執(zhí)行

B會話也是可以執(zhí)行成功的。

測試三：
A會話執(zhí)行如下操作：

B會話執(zhí)行

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

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

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

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

1. mysqldump的本質(zhì)是通過select * from tab來獲取表的數(shù)據(jù)的。

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

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

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.

   */

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

當(dāng)前標(biāo)題：mysql邏輯備份mysqldump
網(wǎng)站URL：http://chinadenli.net/article42/gigcec.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供動態(tài)網(wǎng)站、網(wǎng)站制作、ChatGPT、定制開發(fā)、定制網(wǎng)站、全網(wǎng)營銷推廣

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)