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

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

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

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

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

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

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

2.檢查服務(wù)器上的二進(jìn)制日志:

在服務(wù)器上找到第一個(gè)可用的二進(jìn)制日志,可以從這里開始備份. 例如 binlog.000005

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

4.驗(yàn)證是否正在備份二進(jìn)制日志

MySQL Shell 介紹

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

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

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

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

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

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

3. 壓縮 Compression

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

4. 輸出格式 Output format

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

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

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

通過 exportTable 生成的備份只能通過 importTable 來恢復(fù)

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

MySQL Shell 的安裝

1. 備份例子

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

@.done.json：會記錄備份的結(jié)束時(shí)間, 備份集的大小. 備份結(jié)束時(shí)生成

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

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

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

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

TEST@a_news.sql：建表語句

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

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

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

3. 恢復(fù)例子

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

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

1. 備份命令

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

util.dumpInstance(outputUrl[, options])

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

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

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

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

2. 恢復(fù)命令

util.loadDump(url[, options])

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

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

1. util.dumpInstance 的關(guān)鍵特性

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

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

默認(rèn)是壓縮備份

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

util.loadDump 的關(guān)鍵特性

2. util.loadDump 的關(guān)鍵特性如下：

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

支持?jǐn)帱c(diǎn)續(xù)傳功能

在導(dǎo)入的過程中, 會在備份目錄生成一個(gè)進(jìn)度文件, 用于記錄導(dǎo)入過程中的進(jìn)度信息

文件名由 progressFile 指定, 默認(rèn)是 load-progress..progress

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

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

支持邊備份, 邊導(dǎo)入

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

如果單個(gè)文件過大, util.loadDump 在導(dǎo)入時(shí)會自動進(jìn)行切割, 以避免產(chǎn)生大事務(wù)

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

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

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

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

過濾相關(guān)的選項(xiàng):

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

includeSchemas：指定某些庫的備份

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

includeTables：指定某些表的備份

events：是否備份定時(shí)器, 默認(rèn)為 true

excludeEvents：忽略某些定時(shí)器的備份

includeEvents：指定某些定時(shí)器的備份

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

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

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

users：是否備份賬號信息, 默認(rèn)為 true

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

includeUsers：指定某些賬號的備份, 可指定多個(gè)賬號

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

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

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

ddlOnly：是否只備份表結(jié)構(gòu), 默認(rèn)為 false

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

并行備份相關(guān)的選項(xiàng)

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

bytesPerChunk：每個(gè) chunk 文件的大小, 默認(rèn) 64M

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

OCI(甲骨文云)相關(guān):

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

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

1) create_invisible_pks：對于沒有主鍵的表, 會創(chuàng)建一個(gè)隱藏主鍵：my_row_id BIGINT UNSIGNED AUTO_INCREMENT INVISIBLE PRIMARY KEY. 隱藏列是 MySQL 8.0.23 引入的

2) force_innodb：將表的引擎強(qiáng)制設(shè)置為 InnoDB

3) ignore_missing_pks：忽略主鍵缺失導(dǎo)致的錯(cuò)誤, 與 create_invisible_pks 互斥, 不能同時(shí)指定

4) skip_invalid_accounts：忽略沒有密碼, 或者使用了 MDS 不支持的認(rèn)證插件的賬號

5) strip_definers：去掉視圖、存儲過程、函數(shù)、定時(shí)器、觸發(fā)器中的 DEFINER=account 子句

6) strip_restricted_grants：去掉 MDS 中不允許 GRANT 的權(quán)限

7) strip_tablespaces：去掉建表語句中的 TABLESPACE=xxx 子句

osBucketName, osNamespace, ociConfigFile, ociProfile, ociParManifest, ociParExpireTime：OCI 對象存儲相關(guān)

其它選項(xiàng):

tzUtc：是否設(shè)置 TIME_ZONE = '+00:00', 默認(rèn)為 true

consistent：是否開啟一致性備份, 默認(rèn)為 true. 若設(shè)置為 false, 則不會加全局讀鎖, 也不會開啟事務(wù)的一致性快照

dryRun：試運(yùn)行. 此時(shí)只會打印備份信息, 不會執(zhí)行備份操作

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

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

defaultCharacterSet：字符集, 默認(rèn)為 utf8mb4

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

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

過濾相關(guān):

excludeEvents：忽略某些定時(shí)器的導(dǎo)入

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

excludeSchemas：忽略某些庫的導(dǎo)入

excludeTables：忽略某些表的導(dǎo)入

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

excludeUsers：忽略某些賬號的導(dǎo)入

includeEvents：導(dǎo)入指定定時(shí)器

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

includeSchemas：導(dǎo)入指定庫

includeTables：導(dǎo)入指定表

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

includeUsers：導(dǎo)入指定賬號

loadData：是否導(dǎo)入數(shù)據(jù), 默認(rèn)為 true

loadDdl：是否導(dǎo)入 DDL 語句, 默認(rèn)為 true

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

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

并行導(dǎo)入相關(guān):

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

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

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

斷點(diǎn)續(xù)傳相關(guān):

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

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

OCI 相關(guān):

osBucketName, osNamespace, ociConfigFile, ociProfile

二級索引相關(guān):

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

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

其它選項(xiàng):

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

characterSet：字符集, 無需顯式設(shè)置, 默認(rèn)會從備份集中獲取

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

dryRun：試運(yùn)行

ignoreVersion：忽略 MySQL 的版本檢測. 默認(rèn)情況下, 要求備份實(shí)例和導(dǎo)入實(shí)例的大版本一致

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

showMetadata：導(dǎo)入時(shí)是否打印一致性備份時(shí)的位置點(diǎn)信息

showProgress：是否打印進(jìn)度信息

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

updateGtidSet：更新 GTID_PURGED. 可設(shè)置：off(不更新, 默認(rèn)值), replace(替代目標(biāo)實(shí)例的 GTID_PURGED), append(追加)

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

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

2. 對于不能進(jìn)行并行備份的表, 目前會備份到一個(gè)文件中. 如果該文件過大, 不用擔(dān)心大事務(wù)的問題, util.loadDump 在導(dǎo)入時(shí)會自動進(jìn)行切割

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

4. 默認(rèn)不會備份 information_schema, mysql, ndbinfo, performance_schema, sys

5. 備份實(shí)例支持 MySQL 5.6 及以上版本, 導(dǎo)入實(shí)例支持 MySQL 5.7 及以上版本

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

7. 導(dǎo)入之前, 需將目標(biāo)實(shí)例的 local_infile 設(shè)置為 ON

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

mysqldump: 34 分鐘輸出10G ,

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

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

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