mysql多執行緒備份與還原工具mydumper

gegeman發表於2021-05-23
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      ：使用較少的鎖來實現備份。具體見：https://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

mysql多執行緒備份與還原工具mydumper 
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。

 

 

【完】

相關文章