圖解MySQL邏輯備份的實現流程

jyzhou發表於2022-06-07
圖解MySql

1. 摘要

資料作為一家公司的重要資產，其重要程度不言而喻。資料庫為資料提供存取服務，擔任著重要的角色，如果因資料誤刪、伺服器故障、病毒入侵等原因導致資料丟失或服務不可用，會對公司造成重大損失，所以資料庫備份是資料系統中最為重要的一環。

MySQL備份按照型別分為邏輯備份、物理備份、快照備份，本文將通過圖文方式對常見的邏輯備份工具的一致性備份流程進行說明，來進一步瞭解邏輯備份的整個流程是怎麼實現的。

具體的資訊可以看： 圖解MySQL邏輯備份的實現流程。

 

2. 概念

邏輯備份是資料庫物件級的備份，其將資料庫裡的物件通過SQL查詢出來並轉儲到檔案中，包含了用於建立轉儲物件（資料庫，表，觸發器、自定義函式、儲存過程等）的CREATE語句，和用於將資料載入到表中的INSERT語句。

 

一致性備份是指在某個時間點，匯出的資料與匯出的備份檔案資訊匹配，如果匯出了多張表的資料，這些不同表之間的資料都是同一個時間點的資料，MySQL可以通過全域性鎖（FTWRL，鎖表備份）和事務（single-transaction，一致性快照）實現。

 

鎖表備份在Server層實現，備份期間該例項只能進行SELECT操作；事務的一致性快照備份在引擎層實現，支援MVCC引擎表（InnoDB）的備份，期間例項可以對任何表進行DML操作，DDL操作需要根據具體情況分析，本文會對該情況（一致性快照備份）進行說明。

 

常見的邏輯備份工具：MySQL官方的mysqldump、mysqlpump、mysqlshell的dump方法和第三方開源的mydumper。

 

3. 工具說明

3.1 mysqldump

  • 說明

mysqldump使用單執行緒對錶進行SELECT查詢並轉儲到檔案來達到備份的目的，作為MySQL最“古老”的備份工具，被廣泛的使用在備份中。

 

  • 備份命令

mysqldump -udump_user -p -P3306 -h127.0.0.1 --master-data=2 --single-transaction --default-character-set=utf8 --all-databases --triggers --routines --events > all.sql
 

  • 備份流程

開啟 general_log 檢視備份流程，大致的備份流程如下圖所示：

流程說明：

1. 連線資料庫，設定當前變數，刷髒頁並加一個全域性讀鎖，此刻資料庫例項只能SELECT，不能執行其他任何型別的操作（會影響到業務），再設定事務隔離級別和開啟一致性快照，並獲取BINLOG和GITD資訊，此時所有的支援事務的表（INNODB）資料均來自同一時間點。最後再釋放全域性讀鎖，此刻資料庫例項可以執行任何操作（正常情況下，加全域性讀鎖和釋放鎖的時間很短）。

2. 獲取備份物件的後設資料資訊並單執行緒匯出表「SLEECT *」。匯出表分3種情況：已經匯出完成、還未匯出和正在匯出：

    • 對於已匯出的表，可以做DDL操作（使用SAVEPOINT提前釋放匯出表的metadata lock）；

    • 對還未匯出的表，INNODB表的DDL操作，能否執行成功取決於DDL的操作方式：no-rebuild方式的DDL執行成功，rebuild方式的DDL執行失敗（Table definition has changed），MyISAM引擎的表都能執行成功；

    • 對正在匯出的表，DDL會出現MDL，此時對該表後續的查詢都會出現MDL，導致業務不可用（時間根據備份時長決定），直到該表匯出完成。

3. 獲取除表外的其他物件：自定義函式、儲存過程、VIEW等。

4. 獲取當前的GTID資訊，所有物件的匯出均轉儲到一個檔案，完成備份。

 
3.2 mysqlpump

  • 說明

mysqlpump並行匯出功能的架構為：佇列+執行緒，允許有多個佇列，每個佇列下有多個執行緒，一個佇列可以繫結1個或者多個資料庫。在mysqldump的基礎上額外支援了：並行備份、延遲建立索引、備份使用者、物件的萬用字元過濾、DEFINER忽略等特性。

 

mysqlpump的備份是基於表並行的，對於每張表的匯出只能是單個執行緒的，如果一張表非常大，大部分的時間都是消耗在這個表的備份上，並行備份的效果可能就不明顯。

 

  • 備份命令

    mysqlpump -udump_user -p -P3306 -h127.0.0.1 --set-gtid-purged=on --default-parallelism=2  --single-transaction --default-character-set=utf8 --exclude-databases=mysql,sys,information_schema,performance_schema > all.sql

     

  • 備份流程

開啟 general_log 檢視備份流程，大致的備份流程如下圖所示：

圖片

流程說明：

1. 多執行緒連線資料庫，設定當前變數，刷髒頁並加一個全域性讀鎖，此刻資料庫例項只能SELECT，不能執行任何型別的操作（會影響業務），再設定事務隔離級別和開啟一致性快照讀並獲取GITD 資訊，此時所有的支援事務的表（INNODB）資料均來自同一時間點。最後再釋放全域性讀鎖，此刻資料庫例項可以執行任何操作（正常情況下，加全域性讀鎖和釋放鎖的時間很短）。

2. 獲取除表外的其他物件：自定義函式、儲存過程、VIEW等。

3. 獲取備份物件的後設資料資訊並多執行緒匯出表「SLEECT col1,col2,...」。匯出表分3種情況：已經匯出完成、還未匯出和正在匯出：

    • 對於已匯出的表，不能做DDL操作（不支援SAVEPOINT）；

    • 對還未匯出的表，INNODB表的DDL操作，能否執行成功取決於DDL的操作方式：no-rebuild方式的DDL執行成功，rebuild方式的DDL執行失敗（Table definition has changed），MyISAM引擎的表都能執行成功，但如果表結構先於DDL匯出，再匯出資料，則在還原的時候會報異常（表結構和匯出的資料不一致）；

    • 對正在匯出的表，DDL會出現MDL，此時對該表後續的查詢都會出現MDL，導致業務不可用（時間根據備份時長決定），直到該表匯出完成。

4. 所有物件的備份均轉儲到一個檔案，完成備份。備份檔案中儲存的表結構中只有主鍵，二級索引單獨一行儲存，目的是在恢復完資料後再新增二級索引，提高恢復效率（延遲建立索引）。

 

3.3 mydumper

  • 說明

mydumper利用INNODB的MVCC版本控制的功能，實現多執行緒併發獲取一致性資料。特別是表以chunk的方式批量匯出，即支援一張表多個執行緒以chunk的方式批量匯出（基於行的多執行緒），備份的物件支援正則匹配。

 

  • 備份命令

mydumper -u dump_user -p -h 127.0.0.1 -P 3306 --use-savepoints --trx-consistency-only -r 100000 -t 2 -G -R -E -B sbtest -o /data/backup/

　　--trx-consistency-only：如果不加，則FTWRL的鎖在備份完成之後釋放。加了會在獲取到一致性快照讀之後釋放（UNLOCK TABLES）。

　　--rows：-r，分片匯出的行數。

　　--use-savepoints 和 --rows互斥。

 

  • 備份流程

開啟 general_log 檢視備份流程，大致的備份流程如下圖所示：

圖片

流程說明：

1. 連線資料庫，設定當前變數，刷髒頁並加一個全域性讀鎖，此刻資料庫例項只能SELECT，不能執行任何型別的操作（會影響業務），再獲取BINLOG和GITD 資訊並設定事務隔離級別和開啟一致性快照，此時所有的支援事務的表（INNODB）資料均來自同一時間點。最後再釋放全域性讀鎖，此刻資料庫例項可以執行任何操作（正常情況，加全域性讀鎖和釋放鎖的時間很短）。

2. 獲取備份物件的後設資料資訊並多執行緒匯出表「SLEECT *」。匯出表分3種情況：已經匯出完成、還未匯出和正在匯出：

    • 對於已匯出的表，可以做DDL操作（使用SAVEPOINT提前釋放匯出表的metadata lock，如果使用分片匯出，SAVEPOINT將不可用）；

    • 對還未匯出的表，INNODB表的DDL操作，能否執行成功取決於DDL的操作方式：no-rebuild方式的DDL執行成功，rebuild方式的DDL執行失敗（Table definition has changed），MyISAM引擎的表都能執行成功；

    • 對正在匯出的表，DDL會出現MDL，此時對該表後續的查詢都會出現MDL，導致業務不可用（時間根據備份時長決定），直到該表匯出完成。

3. 所有表都匯出完成後，再獲取除表外的其他物件：自定義函式、儲存過程、VIEW等

4. 所有物件的匯出均轉儲到多個檔案（將表資料分塊匯出成多個資料檔案），完成備份。

 

3.4 mysqlshell

  • 說明

MySQL Shell 是Oracle官方提供的一個互動式工具，用於開發和管理MySQL的伺服器。其中的util.dumpInstance、util.dumpSchemas、util.loadDump 等是對MySQL進行備份管理，使用zstd實時壓縮演算法，支援多執行緒備份，以chunk的方式批量匯出，支援一張表多個執行緒以chunk的方式批量匯出。

 

  • 備份命令

util.dumpSchemas(['sbtest'],'/data/backup',{"threads":1,"consistent":true})

 

  • 備份流程

圖片

流程說明：

1. 連線資料庫，設定當前變數，刷髒頁並加一個全域性讀鎖，此刻資料庫例項只能SELECT，不能執行任何型別的操作（會影響業務），再獲取BINLOG、GITD和備份物件的後設資料資訊，接著設定事務隔離級別和啟動一致性快照，此時所有的支援事務的表（INNODB）資料均來自同一時間點。最後再釋放全域性讀鎖，此刻資料庫例項可以執行任何操作（正常情況，加全域性讀鎖和釋放鎖的時間很短）。

2. 獲取備份物件：自定義函式、儲存過程、VIEW等。

3. 多執行緒匯出表「SLEECT col1,col2,...」。匯出表分3種情況：已經匯出完成、還未匯出和正在匯出：

  • 對於已匯出的表，不能做DDL操作（不支援SAVEPOINT）；

  • 對還未匯出的表，需要區分有沒有PK或UK：

    • 沒有PK和UK，還要根據DDL的操作方式：no-rebuild方式的DDL執行成功，rebuild方式的DDL執行失敗（Table definition has changed），MyISAM引擎的表都能執行成功；

    • 有PK或UK，和正在匯出的表情況一樣，原因是在獲取分片資訊時需要查詢表的最大最小值。

  • 對正在匯出的表，DDL會出現MDL，此時對該表後續的查詢都會出現MDL，導致業務不可用（時間根據備份時長決定），直到該表匯出完成。

4. 所有物件的匯出均轉存到多個檔案（將表資料分塊匯出成多個資料檔案），完成備份。

 

3.5 小結

從上面各邏輯備份的流程中看到，在一致性備份下，所有表的DML操作不影響（除FTWRL短暫的時刻外），而DDL操作都存在一定的風險。所以在邏輯備份期間，要儘量避免DDL操作。

 

各邏輯備份工具的引數可以看官方文件，對比各工具之後，如表所示：

圖片

推薦使用的邏輯備份，需要具備的條件：支援一致性備份、行級別的分片多執行緒匯出、匯出到多個檔案（恢復快），延遲建立索引和savepoint能力。從上面表中看到，mydumper和mysqlshell中的dump方法能滿足較多的條件。

 

 

4. 總結

希望通過閱讀本文，能讓大家更清晰的瞭解邏輯備份的整個實現流程，在選擇合適的邏輯備份時有幫助。

 

 

相關文章