MySQL 官方出品，比 mydumper 更快的多執行緒邏輯備份工具-MySQL Shell Dump & Load

iVictor發表於2022-05-30
MySql執行緒

mysqldump 和 mydumper 是我們常用的兩個邏輯備份工具。

無論是 mysqldump 還是 mydumper 都是將備份資料通過 INSERT 的方式寫入到備份檔案中。

恢復時，myloader（ mydumper 中的恢復工具 ） 是多執行緒匯入，且一個 INSERT 語句中包含多條記錄，多個 INSERT 操作批量提交。基本上，凡是我們能想到的，有助於提升匯入速度的，myloader 都會使用或有選項提供。

單就恢復速度而言，可以說，myloader 就是邏輯恢復工具的天花板。

既然如此，還有辦法能繼續提升邏輯恢復工具的恢復速度麼？畢竟，備份的恢復速度直接影響著災難發生時資料庫服務的 RTO。

答案，有！

這個就是官方在 MySQL Shell 8.0.21 中推出的 Dump & Load 工具。

與 myloader 不一樣的是，MySQL Shell Load 是通過 LOAD DATA LOCAL INFILE 命令來匯入資料的。

而 LOAD DATA 操作，按照官方文件的說法，比 INSERT 操作快 20 倍。

下面，我們看看 MySQL Shell Dump & Load 的具體用法和實現原理。

本文主要包括以下幾部分：

  1. 什麼是 MySQL Shell。
  2. MySQL Shell的安裝。
  3. MySQL Shell Dump & Load的使用。
  4. util.dumpInstance的關鍵特性。
  5. util.loadDump的關鍵特性。
  6. util.dumpInstance的備份流程。
  7. util.dumpInstance的引數解析。
  8. util.loadDump的引數解析。
  9. 使用 MySQL Shell Dump & Load時的注意事項。

 

什麼是 MySQL Shell

MySQL Shell 是 MySQL 的一個高階客戶端和程式碼編輯器，是第二代 MySQL 客戶端。第一代 MySQL 客戶端即我們常用的 mysql。

相比於 mysql，MySQL Shell 不僅支援 SQL，還具有以下關鍵特性：

  1. 支援 Python 和 JavaScript 兩種語言模式。基於此，我們可以很容易地進行一些指令碼開發工作。
  2. 支援 AdminAPI。AdminAPI 可用來管理 InnoDB Cluster、InnoDB ClusterSet 和 InnoDB ReplicaSet。
  3. 支援 X DevAPI。X DevAPI 可對文件（ Document ）和表（ Table ）進行 CRUD（Create，Read，Update，Delete）操作。

除此之外，MySQL Shell 還內建了很多實用工具，包括：

  • checkForServerUpgrade：檢測目標例項能否升級到指定版本。

  • dumpInstance：備份例項。

  • dumpSchemas：備份指定庫。

  • dumpTables：備份指定表。

  • loadDump：恢復通過上面三個工具生成的備份。

  • exportTable：將指定的表匯出到文字檔案中。只支援單表，效果同 SELECT INTO OUTFILE 一樣。

  • importTable：將指定文字的資料匯入到表中。

    線上上，如果我們有個大檔案需要匯入，建議使用這個工具。它會將單個檔案進行拆分，然後多執行緒並行執行 LOAD DATA LOCAL INFILE 操作。不僅提升了匯入速度，還規避了大事務的問題。

  • importJson：將 JSON 格式的資料匯入到 MySQL 中，譬如將 MongoDB 中通過 mongoexport 匯出的資料匯入到 MySQL 中。

在使用時注意：

  1. 通過 dumpInstance，dumpSchemas，dumpTables 生成的備份只能通過 loadDump 來恢復。
  2. 通過 exportTable 生成的備份只能通過 importTable 來恢復。

下面，我們重點說說 Dump & Load 相關的工具，包括 dumpInstance，dumpSchemas，dumpTables 和 loadDump。

 

MySQL Shell 的安裝

MySQL Shell 下載地址：https://dev.mysql.com/downloads/shell/。

同 MySQL 一樣，提供了多個版本的下載。這裡使用 Linux 二進位制版本（ Linux - Generic ）。

# cd /usr/local/
# wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.29-linux-glibc2.12-x86-64bit.tar.gz
# tar xvf mysql-shell-8.0.29-linux-glibc2.12-x86-64bit.tar.gz 
# ln -s mysql-shell-8.0.29-linux-glibc2.12-x86-64bit mysql-shell
# export PATH=$PATH:/usr/local/mysql-shell/bin

 

MySQL Shell Dump & Load 的使用

util.dumpInstance(outputUrl[, options])

備份例項。

其中，outputUrl 是備份目錄，其必須為空。options 是可指定的選項。

首先，看一個簡單的示例。

# mysqlsh -h 10.0.20.4 -P3306 -uroot -p
mysql-js> util.dumpInstance('/data/backup/full',{compression: "none"})
Acquiring global read lock
Global read lock acquired
Initializing - done
1 out of 5 schemas will be dumped and within them 1 table, 0 views.
4 out of 7 users will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
101% (650.00K rows / ~639.07K rows), 337.30K rows/s, 65.89 MB/s
Dump duration: 00:00:01s
Total duration: 00:00:01s
Schemas dumped: 1
Tables dumped: 1
Data size: 126.57 MB
Rows written: 650000
Bytes written: 126.57 MB
Average throughput: 65.30 MB/s

命令中的 /data/backup/full 是備份目錄，compression: "none" 指的是不壓縮，這裡設定為不壓縮主要是為了方便檢視資料檔案的內容。線上使用建議開啟壓縮。


接下來我們看看備份目錄中的內容。

# ll /data/backup/full/
total 123652
-rw-r----- 1 root root      273 May 25 21:13 @.done.json
-rw-r----- 1 root root      854 May 25 21:13 @.json
-rw-r----- 1 root root      240 May 25 21:13 @.post.sql
-rw-r----- 1 root root      288 May 25 21:13 sbtest.json
-rw-r----- 1 root root 63227502 May 25 21:13 sbtest@sbtest1@0.tsv
-rw-r----- 1 root root      488 May 25 21:13 sbtest@sbtest1@0.tsv.idx
-rw-r----- 1 root root 63339214 May 25 21:13 sbtest@sbtest1@@1.tsv
-rw-r----- 1 root root      488 May 25 21:13 sbtest@sbtest1@@1.tsv.idx
-rw-r----- 1 root root      633 May 25 21:13 sbtest@sbtest1.json
-rw-r----- 1 root root      759 May 25 21:13 sbtest@sbtest1.sql
-rw-r----- 1 root root      535 May 25 21:13 sbtest.sql
-rw-r----- 1 root root      240 May 25 21:13 @.sql
-rw-r----- 1 root root     6045 May 25 21:13 @.users.sql

其中，

  • @.done.json：會記錄備份的結束時間，備份集的大小。備份結束時生成。

  • @.json：會記錄備份的一些後設資料資訊，包括備份時的一致性位置點資訊：binlogFile，binlogPosition 和 gtidExecuted，這些資訊可用來建立複製。

  • @.sql，@.post.sql：這兩個檔案只有一些註釋資訊。不過在通過 util.loadDump 匯入資料時，我們可以通過這兩個檔案自定義一些 SQL。其中，@.sql 是資料匯入前執行，@.post.sql 是資料匯入後執行。

  • sbtest.json：記錄 sbtest 中已經備份的表、檢視、定時器、函式和儲存過程。

  • *.tsv：資料檔案。我們看看資料檔案的內容。

    # head -2 sbtest@sbtest1@0.tsv
    1 6461363 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 22195207048-70116052123-74140395089-76317954521-98694025897
    2 1112248 13241531885-45658403807-79170748828-69419634012-13605813761-77983377181-01582588137-21344716829-87370944992-02457486289 28733802923-10548894641-11867531929-71265603657-36546888392

    TSV 格式，每一行儲存一條記錄，欄位與欄位之間用製表符（\t）分隔。

  • sbtest@sbtest1.json：記錄了表相關的一些後設資料資訊，如列名，欄位之間的分隔符（fieldsTerminatedBy）等。

  • sbtest@sbtest1.sql：sbtest.sbtest1 的建表語句。

  • sbtest.sql：建庫語句。如果這個庫中存在儲存過程、函式、定時器，也是寫到這個檔案中。

  • @.users.sql：建立賬號及授權語句。預設不會備份 mysql.session，mysql.session，mysql.sys 這三個內部賬號。


util.dumpSchemas(schemas, outputUrl[, options])

備份指定庫的資料。

用法同 util.dumpInstance 類似。其中，第一個引數必須為陣列，即使只需備份一個庫，如，

util.dumpSchemas(['sbtest'],'/data/backup/schema')

支援的配置大部分與 util.dumpInstance 相同。

從 MySQL Shell 8.0.28 開始，可直接使用 util.dumpInstance 中的 includeSchemas 選項進行指定庫的備份。


util.dumpTables(schema, tables, outputUrl[, options])

備份指定表的資料。

用法同 util.dumpInstance 類似。其中，第二個引數必須為陣列，如，

util.dumpTables('sbtest',['sbtest1'],'/data/backup/table')

支援的配置大部分與 util.dumpInstance 相同。

從 MySQL Shell 8.0.28 開始，可直接使用 util.dumpInstance 中的 includeTables 選項進行指定表的備份。


util.loadDump(url[, options])

匯入通過 dump 命令生成的備份集。如，

# mysqlsh -S /data/mysql/3307/data/mysql.sock
mysql-js> util.loadDump("/data/backup/full",{loadUsers: true})
Loading DDL, Data and Users from '/data/backup/full' using 4 threads.
Opening dump...
Target is MySQL 8.0.27. Dump was produced from MySQL 8.0.27
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
2 thds loading - 100% (126.57 MB / 126.57 MB), 11.43 MB/s, 0 / 1 tables done
Recreating indexes - done
Executing user accounts SQL...
NOTE: Skipping CREATE/ALTER USER statements for user 'root'@'localhost'
NOTE: Skipping GRANT statements for user 'root'@'localhost'
Executing common postamble SQL
2 chunks (650.00K rows, 126.57 MB) for 1 tables in 1 schemas were loaded in 10 sec (avg throughput 13.96 MB/s)
0 warnings were reported during the load.

命令中的 /data/backup/full 是備份目錄，loadUsers: true 是匯入賬號，預設不會匯入。

 

util.dumpInstance 的關鍵特性

util.dumpInstance 的關鍵特性如下：

  1. 多執行緒備份。併發執行緒數由 threads 決定，預設是 4。
  2. 支援單表 chunk 級別的並行備份，前提是表上存在主鍵或唯一索引。
  3. 預設是壓縮備份。
  4. 支援備份限速。可通過 maxRate 限制單個執行緒的資料讀取速率。

 

util.loadDump 的關鍵特性

util.loadDump 的關鍵特性如下：

  1. 多執行緒恢復。併發執行緒數由 threads 決定，預設是 4。

  2. 支援斷點續傳功能。

    在匯入的過程中，會在備份目錄生成一個進度檔案，用於記錄匯入過程中的進度資訊。

    檔名由 progressFile 指定，預設是 load-progress.<server_uuid>.progress。

    匯入時，如果備份目錄中存在 progressFile，預設會從上次完成的地方繼續執行。如果要從頭開始執行，需將 resetProgress 設定為 true。

  3. 支援延遲建立二級索引。

  4. 支援邊備份，邊匯入。

  5. 通過 LOAD DATA LOCAL INFILE 命令來匯入資料。

  6. 如果單個檔案過大，util.loadDump 在匯入時會自動進行切割，以避免產生大事務。

 

util.dumpInstance 的備份流程

util.dumpInstance 的備份流程如下圖所示。

圖片

不難看出，util.dumpInstance 的備份流程與 mysqldump 大致相同，不同的地方主要體現在以下兩點：

  1. util.dumpInstance 會加備份鎖。備份鎖可用來阻塞備份過程中的 DDL。
  2. util.dumpInstance 是並行備份，相對於 mysqldump 的單執行緒備份，備份效率更高。

 

util.dumpInstance 的引數解析

util.dumpInstance 的引數可分為如下幾類：

過濾相關

以下是過濾相關的選項。

  • excludeSchemas：忽略某些庫的備份，多個庫之間用逗號隔開，如，

    excludeSchemas: ["db1", "db2"]

  • includeSchemas：指定某些庫的備份。

  • excludeTables：忽略某些表的備份，表必須是 schema.table 的格式，多個表之間用逗號隔開，如，

    excludeTables: ["sbtest.sbtest1", "sbtest.sbtest2"]

  • includeTables：指定某些表的備份。

  • events：是否備份定時器，預設為 true。

  • excludeEvents：忽略某些定時器的備份。

  • includeEvents：指定某些定時器的備份。

  • routines：是否備份函式和儲存過程，預設為 true。

  • excludeRoutines：忽略某些函式和儲存過程的備份。

  • includeRoutines：指定某些函式和儲存過程的備份。

  • users：是否備份賬號資訊，預設為 true。

  • excludeUsers：忽略某些賬號的備份，可指定多個賬號。

  • includeUsers：指定某些賬號的備份，可指定多個賬號。

  • triggers：是否備份觸發器，預設為 true。

  • excludeTriggers：忽略某些觸發器的備份。

  • includeTriggers：指定某些觸發器的備份。

  • ddlOnly：是否只備份表結構，預設為 false。

  • dataOnly：是否只備份資料，預設為 false。


並行備份相關

  • chunking：是否開啟 chunk 級別的並行備份功能，預設為 true。
  • bytesPerChunk：每個 chunk 檔案的大小，預設 64M。
  • threads：併發執行緒數，預設為 4。

OCI（甲骨文雲）相關

  • ocimds：是否檢查備份集與 MySQL Database Service（甲骨文雲的 MySQL 雲服務，簡稱 MDS ）的相容性，預設為 false，不檢查。如果設定為 true，會輸出所有的不相容項及解決方法。不相容項可通過下面的 compatibility 來解決。

  • compatibility：如果要將備份資料匯入到 MDS 中，為了保證與後者的相容性，可在匯出的過程中進行相應地調整。具體來說：

  1. create_invisible_pks：對於沒有主鍵的表，會建立一個隱藏主鍵：my_row_id BIGINT UNSIGNED AUTO_INCREMENT INVISIBLE PRIMARY KEY。隱藏列是 MySQL 8.0.23 引入的。
  2. force_innodb：將表的引擎強制設定為 InnoDB。
  3. ignore_missing_pks：忽略主鍵缺失導致的錯誤，與 create_invisible_pks 互斥，不能同時指定。
  4. skip_invalid_accounts：忽略沒有密碼，或者使用了 MDS 不支援的認證外掛的賬號。
  5. strip_definers：去掉檢視、儲存過程、函式、定時器、觸發器中的 DEFINER=account 子句。
  6. strip_restricted_grants：去掉 MDS 中不允許 GRANT 的許可權。
  7. strip_tablespaces：去掉建表語句中的 TABLESPACE=xxx 子句。

  • osBucketName，osNamespace，ociConfigFile，ociProfile，ociParManifest，ociParExpireTime：OCI 物件儲存相關。


    • 其它選項

    • tzUtc：是否設定 TIME_ZONE = '+00:00'，預設為 true。
    • consistent：是否開啟一致性備份，預設為 true。若設定為 false，則不會加全域性讀鎖，也不會開啟事務的一致性快照。
    • dryRun：試執行。此時只會列印備份資訊，不會執行備份操作。
    • maxRate：限制單個執行緒的資料讀取速率，單位 byte，預設為 0，不限制。
    • showProgress：是否列印進度資訊，如果是 TTY 裝置（命令列終端），則為 true，反之，則為 false。
    • defaultCharacterSet：字符集，預設為 utf8mb4。
    • compression：備份檔案的壓縮演算法，預設為 zstd。也可設定為 gzip 或 none（不壓縮）。

     

    util.loadDump 的引數解析

    util.loadDump 的引數可分為如下幾類：

    過濾相關

    • excludeEvents：忽略某些定時器的匯入。
    • excludeRoutines：忽略某些函式和儲存過程的匯入。
    • excludeSchemas：忽略某些庫的匯入。
    • excludeTables：忽略某些表的匯入。
    • excludeTriggers：忽略某些觸發器的匯入。
    • excludeUsers：忽略某些賬號的匯入。
    • includeEvents：匯入指定定時器。
    • includeRoutines：匯入指定函式和儲存過程。
    • includeSchemas：匯入指定庫。
    • includeTables：匯入指定表。
    • includeTriggers：匯入指定觸發器。
    • includeUsers：匯入指定賬號。
    • loadData：是否匯入資料，預設為 true。
    • loadDdl：是否匯入 DDL 語句，預設為 true。
    • loadUsers：是否匯入賬號，預設為 false。注意，即使將 loadUsers 設定為 true，也不會匯入當前正在執行匯入操作的使用者。
    • ignoreExistingObjects：是否忽略已經存在的物件，預設為 off。

    並行匯入相關

    • backgroundThreads：獲取後設資料和 DDL 檔案內容的執行緒數。備份集如果儲存在本地，backgroundThreads 預設和 threads 一致。
    • threads：併發執行緒數，預設為 4。
    • maxBytesPerTransaction：指定單個 LOAD DATA 操作可載入的最大位元組數。預設與 bytesPerChunk 一致。這個引數可用來規避大事務。

    斷點續傳相關

    • progressFile：在匯入的過程中，會在備份目錄生成一個 progressFile，用於記錄載入過程中的進度資訊，這個進度資訊可用來實現斷點續傳功能。預設為 load-progress.<server_uuid>.progress。
    • resetProgress：如果備份目錄中存在 progressFile，預設會從上次完成的地方繼續執行。如果要從頭開始執行，需將 resetProgress 設定為 true。該引數預設為 off。

    OCI 相關

    osBucketName，osNamespace，ociConfigFile，ociProfile。


    二級索引相關

    • deferTableIndexes：是否延遲（資料載入完畢後）建立二級索引。可設定：off（不延遲），fulltext（只延遲建立全文索引，預設值），all（延遲建立所有索引）。
    • loadIndexes：與 deferTableIndexes 一起使用，用來決定資料載入完畢後，最後的二級索引是否建立，預設為 true。

    其它選項

    • analyzeTables：表載入完畢後，是否執行 ANALYZE TABLE 操作。預設是 off（不執行），也可設定為 on 或 histogram（只對有直方圖資訊的表執行）。
    • characterSet：字符集，無需顯式設定，預設會從備份集中獲取。
    • createInvisiblePKs：是否建立隱式主鍵，預設從備份集中獲取。這個與備份時是否指定了 create_invisible_pks 有關，若指定了則為 true，反之為 false。
    • dryRun：試執行。
    • ignoreVersion：忽略 MySQL 的版本檢測。預設情況下，要求備份例項和匯入例項的大版本一致。
    • schema：將表匯入到指定 schema 中，適用於通過 util.dumpTables 建立的備份。
    • showMetadata：匯入時是否列印一致性備份時的位置點資訊。
    • showProgress：是否列印進度資訊。
    • skipBinlog：是否設定 sql_log_bin=0 ，預設 false。這一點與 mysqldump、mydumper 不同，後面這兩個工具預設會禁用 Binlog。
    • updateGtidSet：更新 GTID_PURGED。可設定：off（不更新，預設值）, replace（替代目標例項的 GTID_PURGED）, append（追加）。
    • waitDumpTimeout：util.loadDump 可匯入當前正在備份的備份集。處理完所有檔案後，如果備份還沒有結束（具體來說，是備份集中沒有生成 @.done.json），util.loadDump 會報錯退出，可指定 waitDumpTimeout 等待一段時間，單位秒。

     

    MySQL Shell Dump & Load 的注意事項

    1. 表上存在主鍵或唯一索引才能進行 chunk 級別的並行備份。欄位的資料型別不限。不像 mydumper，分片鍵只能是整數型別。

    2. 對於不能進行並行備份的表，目前會備份到一個檔案中。如果該檔案過大，不用擔心大事務的問題，util.loadDump 在匯入時會自動進行切割。

    3. util.dumpInstance 只能保證 InnoDB 表的備份一致性。

    4. 預設不會備份 information_schema，mysql，ndbinfo，performance_schema，sys。

    5. 備份例項支援 MySQL 5.6 及以上版本，匯入例項支援 MySQL 5.7 及以上版本。

    6. 備份的過程中，會將 BLOB 等非文字安全的列轉換為 Base64，由此會導致轉換後的資料大小超過原資料。匯入時，注意 max_allowed_packet 的限制。

    7. 匯入之前，需將目標例項的 local_infile 設定為 ON。

     

    參考

    [1] Instance Dump Utility, Schema Dump Utility, and Table Dump Utility

    [2] MySQL Shell Dump & Load part 1: Demo!

    [3] MySQL Shell Dump & Load part 2: Benchmarks

    [4] MySQL Shell Dump & Load part 3: Load Dump

    [5] MySQL Shell Dump & Load part 4: Dump Instance & Schemas

    [6] Backup/Restore Performance Conclusion: mysqldump vs MySQL Shell Utilities vs mydumper vs mysqlpump vs XtraBackup

    [7] Optimizing INSERT Statements

    相關文章