mysql多執行緒備份與還原工具mydumper
- 2021 年 5 月 23 日
- 筆記
- ----260 MySQL備份與恢復
（一）mydumper介紹
之前我們已經學過如何使用mysqldump備份恢復資料庫：《mysql邏輯備份與還原工具mysqldump》，就目前來說，mysqldump是使用最廣泛的MySQL備份工具。但經過個人長期使用下來，發現存在幾個問題：
- 不支援多執行緒備份，也不支援多執行緒恢復。如果資料庫較大，帶來的問題就是備份恢復時間長。
- 備份出來的文件為一個整體，當需要某個表的數據時，無法從一個整體中取出。
這個時候我們可以考慮使用mydumper來備份資料庫，相對於mysqldump，mydumper有什麼特點呢？
- 並行性。並行備份，並行恢復，可以提升備份恢復速度
- 備份文件易於管理。備份出來的文件基於表為單位，一個sql文件記錄一個表的資訊。
- 一致性。維護所有執行緒的快照，提供準確的主日誌和從日誌位置
- 可管理性。支援PCRE(perl語音的正則表達式)，用於指定資料庫和表的包含/排除
（二）mydumper/myloader安裝
建立mydumper的依賴項
# 安裝開發工具： yum install -y cmake gcc gcc-c++ git make # 安裝GLib，ZLib和PCRE的開發版本： yum install -y glib2-devel mysql-devel openssl-devel pcre-devel zlib-devel yum install -y mysql-devel yum install -y Percona-Server-devel-57 yum install -y mariadb-devel
安裝mydumper/myloader
yum install https://github.com/maxbube/mydumper/releases/download/v0.10.5/mydumper-0.10.5-1.el7.x86_64.rpm
查看安裝情況：
[root@hosta ~]# which mydumper /usr/bin/mydumper [root@hosta ~]# which myloader /usr/bin/myloader
（三）mydumper/myloader參數介紹
（3.1）mydumper常用參數
可以使用 mydumper –help 來查看相關使用參數。這裡列出一些常用的參數：
[連接資料庫參數]
–host , -h ：待備份資料庫主機名或者IP
–user , -u ：待備份資料庫用戶名
–password , -p ：資料庫密碼，特別注意，mydumper參數與值需要空格分開
–port , -P ：資料庫埠
–socket , -S ：socket file文件
[備份範圍參數]
–database , -B ：dump的資料庫
–tables-list , -T ：要備份的表，多個表使用逗號分隔
–regex , -x ：使用正則表達式去匹配符合條件的資料庫和表
–build-empty-files , -e ：如果表沒有數據，則創建空文件
–insert-ignore , -N ：dump行數據通過INSERT IGNORE INTO而不是INSERT INTO
–no-schemas , -m ：不dump表的schema數據，即表的元數據
–no-data , -d ：不dump表的行數據
–triggers , -G ：dump觸發器
–events , -E ：dump EVENTS(定時任務)
–routines ，-R ：dump存儲過程和函數
–no-views ，-W ：不dump視圖
[長時間的查詢]
—long-query-retries ：嘗試檢查長時間的查詢，默認0，不重試
—long-query-retry-interval ：檢查長時間查詢的語句的間隔，默認60s
—long-query-guard , -l ：長時間執行超時秒數，默認60s
—kill–long-query , -K ：殺掉長時間的查詢而不是終止dump操作
[鎖]
–no-locks , -k ：不使用臨時共享讀鎖，會造成不一致性備份
–no-backup-locks ：不使用Percona Backup Locks
—less-locking ：使用較少的鎖來實現備份。具體見：//www.percona.com/blog/2014/06/13/mydumper-less-locking/
[其它]
–threads , -t ：dump執行緒數量，默認是4
–outputdir , -o ：dump文件輸出路徑，默認export-YYYYMMDD-HHMMSS
–rows , -r ：將表拆分為N行的塊，默認無限制(不拆分)
–compress , -c ：壓縮輸出文件
–conpress-input , -C ：使用客戶端協議連接到MySQL server壓縮，個人理解為dump的過程就開始進行壓縮，以便於佔用較少的頻寬
–binlogs , -b ：從server以及dump file得到binlog日誌
–logfile , -L ：mydumper操作的日誌記錄文件名
–[skip-]tz-utc ：在dump文件頭部設SET TIME_ZONE=’+00:00’去允許，默認使用–skip-tz-utc伺服器有不同的時區或者數據被移動到不同的時區，默認使用–skip-tz-utc
–chunk-filesize , -F ：當數據文件大於多少MB時，進行文件分割
–complete-insert ：使用包含列名的完整INSERT語句
（3.2）myloader常用參數
–threads , -t ：用於還原數據的執行緒數，默認為4
–directory , -d ：要還原的mydumper備份目錄
–database , -B ：要還原到哪個資料庫
–queries-per-transaction , -q ：恢復時多少行提交一次，默認1000行
–overwrite-tables , -o ：在恢復時，如果表存在，則先刪除
–enable-binlog , -e ：啟用binlog，這個參數非常重要，如果在主節點進行數據導入，同時同步到從節點，需要開啟該參數，默認關閉
（四）使用mydumper/myloader備份還原資料庫
（4.1）使用mydumper備份資料庫
（4.1.1）備份資料庫（全部、單個、多個）
-- 導出所有資料庫，不包含mysql|test|information_schema|performance_schema|sys。且對trigger(G)、routines(R)、events(E)也導出，進行數據壓縮(c)，且8執行緒(t)導出 mydumper -u root -p 123456 -P 3306 -h 192.168.10.11 --regex '^(?!(mysql|test|information_schema|performance_schema|sys))' -G -R -E -c -t 8 -o /root/backup -- 備份單個資料庫 mydumper -u root -p 123456 -P 3306 -h 192.168.10.11 --database lijiamandb -G -R -E -c -t 8 -o /root/backup -- 備份lijiamandb和db1資料庫，且對trigger(G)、routines(R)、events(E)也導出，且8執行緒(t)導出 mydumper -u root -p 123456 -P 3306 -h 192.168.10.11 --regex 'lijiamandb|db1' -G -R -E -t 8 -o /root/backup
* 需要注意的是，備份單個資料庫可以使用database參數進行，但是備份多個資料庫該參數就不好用了，需要使用上面的則表達式
（4.1.2）備份表
-- 備份多個表 mydumper -u root -p 123456 -P 3306 -h 192.168.10.11 --database lijiamandb --tables-list test01,test02 -G -R -E -o /root/backup
備份所有資料庫，備份的結果如下：
我們可以仔細觀察這些文件，主要的文件類型有：
- metadata ：當前備份到各個主從節點的位點(log、pos)，這對於我們使用基於位點的同步是必要的
- {db_name}-schema-create.sql.gz ：創建資料庫的SQL
- {db_name}-schema-post.sql.gz ：該資料庫trigger(G)、routines(R)、events(E)的資訊
- {db_name}.{table_name}-schema.sql.gz ：表創建SQL
- {db_name}.{table_name}.sql.gz ：表數據SQL
詳細看一下其中的內容：
（Ⅰ）metadata：記錄了主庫機器從庫的位點資訊
Started dump at: 2021-05-23 00:10:15 SHOW MASTER STATUS: Log: master-bin.000065 Pos: 194 GTID:9d62e676-723d-11ea-83cf-000c29923d50:1-2, 9d6a0a08-723d-11ea-83a1-000c29fb6200:1-920094 SHOW SLAVE STATUS: Host: 192.168.10.12 Log: master-bin.000014 Pos: 194 GTID:9d62e676-723d-11ea-83cf-000c29923d50:1-2, 9d6a0a08-723d-11ea-83a1-000c29fb6200:1-920094 Finished dump at: 2021-05-23 00:11:08
（Ⅱ）lijiamandb-schema-create.sql ：記錄了創建lijiamandb資料庫的SQL
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `lijiamandb` /*!40100 DEFAULT CHARACTER SET utf8 */;
（Ⅲ）lijiamandb-schema-post.sql ：記錄了函數、過程、EVENT、Trigger等的創建SQL
SET @PREV_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT; SET @PREV_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS; SET @PREV_COLLATION_CONNECTION=@@COLLATION_CONNECTION; SET character_set_client = utf8mb4; SET character_set_results = utf8mb4; SET collation_connection = utf8mb4_general_ci; DROP PROCEDURE IF EXISTS `p_insert`; CREATE DEFINER=`root`@`%` PROCEDURE `p_insert`() BEGIN #Routine body goes here... DECLARE str1 varchar(30); DECLARE str2 varchar(30); DECLARE i int; set i = 0; while i < 100000 do set str1 = substring(md5(rand()),1,25); insert into test01(name) values(str1); set str2 = substring(md5(rand()),1,25); insert into test02(name) values(str2); set i = i + 1; end while; END; SET character_set_client = @PREV_CHARACTER_SET_CLIENT; SET character_set_results = @PREV_CHARACTER_SET_RESULTS; SET collation_connection = @PREV_COLLATION_CONNECTION; SET @PREV_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT; SET @PREV_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS; SET @PREV_COLLATION_CONNECTION=@@COLLATION_CONNECTION; SET character_set_client = utf8mb4; SET character_set_results = utf8mb4; SET collation_connection = utf8mb4_general_ci; DROP EVENT IF EXISTS `e_insert`; CREATE DEFINER=`root`@`%` EVENT `e_insert` ON SCHEDULE EVERY 90 SECOND STARTS '2020-04-23 10:43:47' ON COMPLETION PRESERVE ENABLE DO call p_insert(); SET character_set_client = @PREV_CHARACTER_SET_CLIENT; SET character_set_results = @PREV_CHARACTER_SET_RESULTS; SET collation_connection = @PREV_COLLATION_CONNECTION;
View Code
（Ⅳ）lijiamandb.t1-schema.sql：記錄了表結構創建的SQL
/*!40101 SET NAMES binary*/; /*!40014 SET FOREIGN_KEY_CHECKS=0*/; /*!40103 SET TIME_ZONE='+00:00' */; CREATE TABLE `t1` ( `c1` char(1) NOT NULL, `c2` char(1) NOT NULL, `c3` char(1) NOT NULL, `c4` char(1) NOT NULL, `c5` char(1) NOT NULL, KEY `idx_c1234` (`c1`,`c2`,`c3`,`c4`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
（Ⅴ） lijiamandb.t1.sql：記錄了t1的行數據
/*!40101 SET NAMES binary*/; /*!40014 SET FOREIGN_KEY_CHECKS=0*/; /*!40103 SET TIME_ZONE='+00:00' */; INSERT INTO `t1` VALUES ("1","1","1","1","1"), ("2","2","2","2","2"), ("3","3","3","3","3"), ("4","4","4","4","4"), ("5","5","5","5","5");
（4.2）使用myloader還原資料庫
myloader導入數據
myloader -u root -p 123456 -P 3306 -h 192.168.10.11 -e -d /root/backup/ -t 8
（五）總結
本篇文章大致學習了mydumper的用法，發現有較多的優異特性。可以實現多執行緒備份恢復，備份文件基於表保存，便於後續使用。那麼mydumper有沒有缺點呢？我認為mydumper沒有數據衝突的處理方案，mysqldump提供了insert-ingore來處理數據導入衝突，但是mydumper沒有。如果要在已存在數據的表上進行增量導入，為了解決數據衝突，還是得使用mysqldump。
【完】
