MySQL8.x 中新增了一個輕量級的備份鎖,它允許在 online 備份的時候進行 DML 操作,同時可防止快照不一致. 這個鎖禁止的操作很少,它禁止的操作包括:

1. 文件的創(chuàng)建, 刪除, 改名 2. 賬號的管理 3. REPAIR TABLE 4. TRUNCATE TABLE 5. OPTIMIZE TABLE

長查詢對 flush tables with read lock 是有影響的,整個系統(tǒng)會 hung 住, 這時其它的數(shù)據(jù)庫查詢也不能用, 包括 use database

而 lock instance for backup 沒有這個問題. Oracle自己的備份產(chǎn)品 (MySQL Enterprise Backup 8) 就用這個鎖, Percona 的 Xtrabackup 在 8 的版本也是用這個鎖

percona 自己的 MySQL 也有一種輕量級的備份鎖 lock table for backup 也是一種比 flush tables with read lock 要輕量級的鎖, 它不會刷新表, 即存儲引擎不會強制關閉表, 并且表不會從表緩存中排出. 因此, LOCK TABLES FOR BACKUP 僅等待沖突的語句完成, 它不會等待 SELECT 或更新 InnoDB 表來完成

測試結果: 鎖定后可以 DML,但是會阻塞 EVENT事件

1.在服務器上創(chuàng)建一個復制用戶

2.檢查服務器上的二進制日志:

在服務器上找到第一個可用的二進制日志,可以從這里開始備份. 例如 binlog.000005

3.登錄到備份服務器并執(zhí)行以下命令,會將二進制日志從MySQL服務器復制到備份服務器. 你可以使用 nohup 或 disown

4.驗證是否正在備份二進制日志

MySQL Shell 介紹

MySQL Shell 是 MySQL 的一個高級客戶端和代碼編輯器, 是第二代 MySQL 客戶端. 第一代 MySQL 客戶端即我們常用的 mysqlMySQL Shell 關鍵特性

支持 Python 和 JavaScript 兩種語言模式. 基于此, 我們可以很容易地進行一些腳本開發(fā)工作

支持 AdminAPI. AdminAPI 可用來管理 InnoDB Cluster、InnoDB ClusterSet 和 InnoDB ReplicaSet

支持 X DevAPI. X DevAPI 可對文檔( Document )和表( Table )進行 CRUD(Create, Read, Update, Delete)操作MySQL Shell 核心優(yōu)勢

1. 并行 Parallelization 默認4個線程進行導出或者導入數(shù)據(jù), 可根據(jù)情況自己定義

2. 分塊 Chunking 默認開啟, 將每個表數(shù)據(jù)分成大小相等的數(shù)據(jù)文件, 默認64兆一個文件, 由參數(shù)bytesPerChunk控制, 最小128k（針對那些小表, 小于64兆的表）

3. 壓縮 Compression

默認開啟壓縮, 壓縮類型為時下最新的zstd壓縮, 另外還支持gzip壓縮, zstd在壓縮和解壓之間獲得了一個折中的平衡, 相對比gzip壓縮率高的情況下還要比gzip解壓時間更短.compression：zstd 默認開啟, 不用設置

4. 輸出格式 Output format

MySQL Shell Utilities工具默認使用load data語句預期的默認格式進行數(shù)據(jù)導出和導入, 這會讓輸出文件更小, 同時導入速度也更加快速.

mysqldump, msqlpump,mydumper這三類導出工具都是以insert語句的形式導出數(shù)據(jù), 這在速度上明顯要落后于load data語句, 官方文檔中描述load data方式要比insert方式快20倍MySQL Shell 使用時注意

通過 dumpInstance, dumpSchemas, dumpTables 生成的備份只能通過 loadDump 來恢復

通過 exportTable 生成的備份只能通過 importTable 來恢復

使用限制: MySQL Shell工具目前在數(shù)據(jù)導出導入時,目標庫的版本只適用于MySQL5.7和8.0版本,不適用低于5.7的版本

MySQL Shell 的安裝

1. 備份例子

2. 備份產(chǎn)生的內(nèi)容

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

@.json：會記錄備份的一些元數(shù)據(jù)信息, 包括備份時的一致性位置點信息：binlogFile, binlogPosition 和 gtidExecuted, 這些信息可用來建立復制

@.sql, @.post.sql：這兩個文件只有一些注釋信息. 在通過 util.loadDump 導入數(shù)據(jù)時, 我們可以通過這兩個文件自定義一些 SQL. 其中, @.sql 是數(shù)據(jù)導入前執(zhí)行, @.post.sql 是數(shù)據(jù)導入后執(zhí)行

*.tsv：數(shù)據(jù)文件. 我們看看數(shù)據(jù)文件的內(nèi)容

TEST@a_news.json：記錄了表相關的一些元數(shù)據(jù)信息, 如列名, 字段之間的分隔符(fieldsTerminatedBy)等

TEST@a_news.sql：建表語句

TEST.json：記錄數(shù)據(jù)庫中已經(jīng)備份的表、視圖、定時器、函數(shù)和存儲過程

TEST.sql：建庫語句. 如果這個庫中存在存儲過程、函數(shù)、定時器, 也是寫到這個文件中

@.users.sql：創(chuàng)建賬號及授權語句. 默認不會備份 mysql.session, mysql.session, mysql.sys 這三個內(nèi)部賬號

3. 恢復例子

4. 恢復產(chǎn)生的內(nèi)容

load-progress..progress: 在導入的過程中, 會在備份目錄生成一個 progressFile, 用于記錄加載過程中的進度信息, 可用來實現(xiàn)斷點續(xù)傳功能

1. 備份命令

1) 備份實例 其中, outputUrl 是備份目錄, 其必須為空. options 是可指定的選項

util.dumpInstance(outputUrl[, options])

2) 備份指定庫的數(shù)據(jù). 第一個參數(shù)必須為數(shù)組, 如 util.dumpSchemas(['TEST'],'/backup/schema') 從 MySQL Shell 8.0.28 開始, 可直接使用 util.dumpInstance 中的 includeSchemas 選項進行指定庫的備份

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

3) 備份指定表的數(shù)據(jù). 第二個參數(shù)必須為數(shù)組, 如 util.dumpTables('TEST',['t'],'/backup/table') 從 MySQL Shell 8.0.28 開始, 可直接使用 util.dumpInstance 中的 includeTables 選項進行指定表的備份

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

2. 恢復命令

util.loadDump(url[, options])

3. 不進入mysqlsh, 操作系統(tǒng)命令行執(zhí)行備份

mysqlsh --uri root@127.0.0.1:3308/mysql -e "util.dumpInstance('/backup/full1')"

1. util.dumpInstance 的關鍵特性

多線程備份. 并發(fā)線程數(shù)由 threads 決定, 默認是 4

支持單表 chunk 級別的并行備份, 前提是表上存在主鍵或唯一索引

默認是壓縮備份

支持備份限速. 可通過 maxRate 限制單個線程的數(shù)據(jù)讀取速率

util.loadDump 的關鍵特性

2. util.loadDump 的關鍵特性如下：

多線程恢復. 并發(fā)線程數(shù)由 threads 決定, 默認是 4

支持斷點續(xù)傳功能

在導入的過程中, 會在備份目錄生成一個進度文件, 用于記錄導入過程中的進度信息

文件名由 progressFile 指定, 默認是 load-progress..progress

導入時, 如果備份目錄中存在 progressFile, 默認會從上次完成的地方繼續(xù)執(zhí)行. 如果要從頭開始執(zhí)行, 需將 resetProgress 設置為 true

支持延遲創(chuàng)建二級索引

支持邊備份, 邊導入

通過 LOAD DATA LOCAL INFILE 命令來導入數(shù)據(jù)

如果單個文件過大, util.loadDump 在導入時會自動進行切割, 以避免產(chǎn)生大事務

3. util.dumpInstance 與 mysqldump 不同的地方

util.dumpInstance 會加備份鎖. 備份鎖可用來阻塞備份過程中的 DDL

util.dumpInstance 是并行備份, 相對于 mysqldump 的單線程備份, 備份效率更高

1. util.dumpInstance 的參數(shù)解析

過濾相關的選項:

excludeSchemas：忽略某些庫的備份, 多個庫之間用逗號隔開, 如, excludeSchemas: ["db1", "db2"]

includeSchemas：指定某些庫的備份

excludeTables：忽略某些表的備份, 表必須是 schema.table 的格式, 多個表之間用逗號隔開, 如, excludeTables: ["sbtest.sbtest1", "sbtest.sbtest2"]

includeTables：指定某些表的備份

events：是否備份定時器, 默認為 true

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

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

routines：是否備份函數(shù)和存儲過程, 默認為 true

excludeRoutines：忽略某些函數(shù)和存儲過程的備份

includeRoutines：指定某些函數(shù)和存儲過程的備份

users：是否備份賬號信息, 默認為 true

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

includeUsers：指定某些賬號的備份, 可指定多個賬號

triggers：是否備份觸發(fā)器, 默認為 true

excludeTriggers：忽略某些觸發(fā)器的備份

includeTriggers：指定某些觸發(fā)器的備份

ddlOnly：是否只備份表結構, 默認為 false

dataOnly：是否只備份數(shù)據(jù), 默認為 false

并行備份相關的選項

chunking：是否開啟 chunk 級別的并行備份功能, 默認為 true

bytesPerChunk：每個 chunk 文件的大小, 默認 64M

threads：并發(fā)線程數(shù), 默認為 4

OCI(甲骨文云)相關:

ocimds：是否檢查備份集與甲骨文云的 MySQL 云服務 MySQL Database Service(簡稱 MDS )的兼容性, 默認為 false, 不檢查. 如果設置為 true, 會輸出所有的不兼容項及解決方法. 不兼容項可通過下面的 compatibility 來解決.

compatibility：如果要將備份數(shù)據(jù)導入到 MDS 中, 為了保證與后者的兼容性, 可在導出的過程中進行相應地調(diào)整. 具體來說：

1) create_invisible_pks：對于沒有主鍵的表, 會創(chuàng)建一個隱藏主鍵：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：去掉視圖、存儲過程、函數(shù)、定時器、觸發(fā)器中的 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：試運行. 此時只會打印備份信息, 不會執(zhí)行備份操作

maxRate：限制單個線程的數(shù)據(jù)讀取速率, 單位 byte, 默認為 0, 不限制

showProgress：是否打印進度信息, 如果是 TTY 設備(命令行終端), 則為 true, 反之, 則為 false

defaultCharacterSet：字符集, 默認為 utf8mb4

compression：備份文件的壓縮算法, 默認為 zstd. 也可設置為 gzip 或 none(不壓縮)

2. util.loadDump 的參數(shù)解析

過濾相關:

excludeEvents：忽略某些定時器的導入

excludeRoutines：忽略某些函數(shù)和存儲過程的導入

excludeSchemas：忽略某些庫的導入

excludeTables：忽略某些表的導入

excludeTriggers：忽略某些觸發(fā)器的導入

excludeUsers：忽略某些賬號的導入

includeEvents：導入指定定時器

includeRoutines：導入指定函數(shù)和存儲過程

includeSchemas：導入指定庫

includeTables：導入指定表

includeTriggers：導入指定觸發(fā)器

includeUsers：導入指定賬號

loadData：是否導入數(shù)據(jù), 默認為 true

loadDdl：是否導入 DDL 語句, 默認為 true

loadUsers：是否導入賬號, 默認為 false. 注意, 即使將 loadUsers 設置為 true, 也不會導入當前正在執(zhí)行導入操作的用戶

ignoreExistingObjects：是否忽略已經(jīng)存在的對象, 默認為 off

并行導入相關:

backgroundThreads：獲取元數(shù)據(jù)和 DDL 文件內(nèi)容的線程數(shù). 備份集如果存儲在本地, backgroundThreads 默認和 threads 一致

threads：并發(fā)線程數(shù), 默認為 4

maxBytesPerTransaction：指定單個 LOAD DATA 操作可加載的最大字節(jié)數(shù). 默認與 bytesPerChunk 一致. 這個參數(shù)可用來規(guī)避大事務

斷點續(xù)傳相關:

progressFile：在導入的過程中, 會在備份目錄生成一個 progressFile, 用于記錄加載過程中的進度信息, 這個進度信息可用來實現(xiàn)斷點續(xù)傳功能. 默認為 load-progress..progress

resetProgress：如果備份目錄中存在 progressFile, 默認會從上次完成的地方繼續(xù)執(zhí)行. 如果要從頭開始執(zhí)行, 需將 resetProgress 設置為 true. 該參數(shù)默認為 off

OCI 相關:

osBucketName, osNamespace, ociConfigFile, ociProfile

二級索引相關:

deferTableIndexes：是否延遲(數(shù)據(jù)加載完畢后)創(chuàng)建二級索引. 可設置：off(不延遲), fulltext(只延遲創(chuàng)建全文索引, 默認值), all(延遲創(chuàng)建所有索引)

loadIndexes：與 deferTableIndexes 一起使用, 用來決定數(shù)據(jù)加載完畢后, 最后的二級索引是否創(chuàng)建, 默認為 true

其它選項:

analyzeTables：表加載完畢后, 是否執(zhí)行 ANALYZE TABLE 操作. 默認是 off(不執(zhí)行), 也可設置為 on 或 histogram(只對有直方圖信息的表執(zhí)行)

characterSet：字符集, 無需顯式設置, 默認會從備份集中獲取

createInvisiblePKs：是否創(chuàng)建隱式主鍵, 默認從備份集中獲取. 這個與備份時是否指定了 create_invisible_pks 有關, 若指定了則為 true, 反之為 false

dryRun：試運行

ignoreVersion：忽略 MySQL 的版本檢測. 默認情況下, 要求備份實例和導入實例的大版本一致

schema：將表導入到指定 schema 中, 適用于通過 util.dumpTables 創(chuàng)建的備份

showMetadata：導入時是否打印一致性備份時的位置點信息

showProgress：是否打印進度信息

skipBinlog：是否設置 sql_log_bin=0 , 默認 false. 這一點與 mysqldump(開啟 GTID 的情況下)、mydumper 不同, 后面這兩個工具默認會禁用 Binlog

updateGtidSet：更新 GTID_PURGED. 可設置：off(不更新, 默認值), replace(替代目標實例的 GTID_PURGED), append(追加)

waitDumpTimeout：util.loadDump 可導入當前正在備份的備份集. 處理完所有文件后, 如果備份還沒有結束(具體來說, 是備份集中沒有生成 @.done.json), util.loadDump 會報錯退出, 可指定 waitDumpTimeout 等待一段時間, 單位秒

1. 表上存在主鍵或唯一索引才能進行 chunk 級別的并行備份. 字段的數(shù)據(jù)類型不限. 不像 mydumper, 分片鍵只能是整數(shù)類型

2. 對于不能進行并行備份的表, 目前會備份到一個文件中. 如果該文件過大, 不用擔心大事務的問題, util.loadDump 在導入時會自動進行切割

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

4. 默認不會備份 information_schema, mysql, ndbinfo, performance_schema, sys

5. 備份實例支持 MySQL 5.6 及以上版本, 導入實例支持 MySQL 5.7 及以上版本

6. 備份的過程中, 會將 BLOB 等非文本安全的列轉(zhuǎn)換為 Base64, 由此會導致轉(zhuǎn)換后的數(shù)據(jù)大小超過原數(shù)據(jù). 導入時, 注意 max_allowed_packet 的限制

7. 導入之前, 需將目標實例的 local_infile 設置為 ON

數(shù)據(jù)庫 73G :

mysqldump: 34 分鐘輸出10G ,

myshell dump線程4(默認): 30 分鐘 輸出 10G,

myshell dump線程8: 5 分鐘 輸出 10G,

myshell dump線程12: 5 分鐘 輸出10G,