mysql邏輯備份 mysqldump

2024-07-24 12:35:45

字體：大中小

供稿：網(wǎng)友

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

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

　　現(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將當前會話的事務隔離級別恢復成默認的RR模式，使得當前事務可重復讀。
　　5開啟一個事務，并設置成快照級別。
　　6查詢數(shù)據(jù)庫的GTID是否開啟。
　　7獲取binlog的文件名及position的位置。
　　8釋放全局鎖。
　　9通過select語句查詢test1庫的狀態(tài)。
　　10查詢一下字典。
　　11進入要備份的test1庫。
　　12查看建庫語句。
　　13創(chuàng)建一個事務恢復點sp。
　　14查看需要備份的庫中都有哪些表。
　　15查看表T1狀態(tài)。
　　16設置表名和列名的格式。
　　17設置字符集為二進制。
　　18查看T1的建表語句。
　　19設置字符集為UTF8。
　　20輸出表的所有信息。
　　21查詢T1中數(shù)據(jù)，如果表中數(shù)據(jù)很大，mysql會使用limit來進行分段獲取。
　　22設置字符集為二進制。
　　23進入test1庫。
　　24查看數(shù)據(jù)庫的編碼格式。
　　25查看T1表的觸發(fā)器。
　　26設置字符集為UTF8。
　　27ROLLBAKC到sp事務點。
　　28相同操作獲取test1庫中的T2表數(shù)據(jù)。
　　29當所有數(shù)據(jù)都獲取完成后釋放掉事務回滾點sp。

　　分析mysqldump過程：

　　FLUSH /!40101 LOCAL / TABLES Closes all open tables, forces all tables in use to be closed, and flushes the query cache.
　　FLUSH TABLES WITH READ LOCK 執(zhí)行flush tables操作，并加一個全局讀鎖，很多童鞋可能會好奇，這兩個命令貌似是重復的，為什么不在第一次執(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.
　　/
　　簡而言之，是為了避免較長的事務操作造成FLUSH TABLES WITH READ LOCK操作遲遲得不到鎖，但同時又阻塞了其它客戶端操作。
　　SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 設置當前會話的事務隔離等級為RR，RR可避免不可重復讀和幻讀。 START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT / 獲取當前數(shù)據(jù)庫的快照，這個是由mysqldump中--single-transaction決定的。我們對比下加了該參數(shù)和沒加的區(qū)別，日志如下：
　　有--single-transaction 參數(shù)：

　　無--single-transaction 參數(shù)：

　　可以看到，當我們不加參數(shù)--single-transaction 參數(shù)時，將會少了對隔級別設置，少了開啟事物一致性快照，少了unlock tables；
　　SHOW MASTER STATUS 這個是由--master-data決定的，記錄了開始備份時，binlog的狀態(tài)信息，包括MASTER_LOG_FILE和MASTER_LOG_POS
　　UNLOCK TABLES 釋放鎖。
　　在備份過程中還有一個操作，設置保存點，其實，這樣做不會因為元數(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; 設置保存點，通過rollback to SAVEPOINT sp; 相當于在select 完成后，立即釋放了該表的元數(shù)據(jù)鎖，而不會等到本次會話提交，這樣可以避免DDL長時間無法獲得元數(shù)據(jù)鎖，從而導致該表的其他查詢操作等待。
　　從這三個測試我們也可以看出START TRANSACTION WITH CONSISTENT SNAPSHOT開啟的事務只能通過commit或者rollback來結(jié)束，而不是ROLLBACK TO SAVEPOINT sp。
　　需要注意的是，如果 alter table 發(fā)生在select from t1 之前，也就是第二種測試情況，DDL是可以提交成功的，那么我們再進行 select from t1 查詢的時候，是會報錯的，
　　mysql邏輯備份 mysqldump
　　發(fā)生在備份中，同樣會報錯！
　　總結(jié)：
　　至此，我們可以總結(jié)下mysqldump的備份原理：通過設置READ LOCK獲取數(shù)據(jù)庫全局鎖后，RR事務隔離級別下記錄當前的日志文件名和日志位置position，然后釋放掉全局鎖。接下來創(chuàng)建一個事務的回滾點，所有數(shù)據(jù)的獲取都是獲取的是這個sp回滾點數(shù)據(jù)。最后釋放掉回滾點sp。當然，對于MyISAM存儲引擎，備份是直接鎖全表的。
　　值得注意點：從分析mysqldump過程中我們可以知道，此命令在開始時刻會帶來數(shù)據(jù)庫瞬時的鎖定（FLUSHTABLES WITH READ LOCK），雖然鎖定時間是非常短暫的，但是卻會帶來非常大的數(shù)據(jù)庫隱患，因為在此過程中，如果執(zhí)行有DDL語句，就會導致此命令堵塞并最終異常退出。所以在做備份時間節(jié)點的選擇上，需要根據(jù)數(shù)據(jù)庫環(huán)境選擇在負載壓力最小，且沒有以上操作時候進行備份。

　　mysqldump的本質(zhì)是通過select * from tab來獲取表的數(shù)據(jù)的。
　　mysqldump只適合放到業(yè)務低峰期做，如果備份的過程中數(shù)據(jù)操作很頻繁，會造成Undo表空間越來越大，undo表空間默認是放到共享表空間中的，而ibdata的特性是一旦增大，就不會收縮。
　　mysqldump的效率還是比較低下，START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT /只能等到所有表備份完后才結(jié)束，其實效率比較高的做法是備份完一張表就提交一次，這樣可盡快釋放Undo表空間快照占用的空間。但這樣做，就無法實現(xiàn)對所有表的一致性備份。
　　為什么備份完成后沒有commit操作 /*

　　server. */
　　mysql邏輯備份 mysqldump

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

（編輯：武林網(wǎng)）

上一篇：mysql常用函數(shù)的用法

下一篇：怎樣使用mysql變量