ByteHouse MaterializedMySQL 增強最佳化

更多技術交流、求職機會，歡迎關注位元組跳動資料平臺微信公眾號，回覆【1】進入官方交流群

前言

社群版 ClickHouse 推出了MaterializedMySQL資料庫引擎，用於將 MySQL 中的表對映到 ClickHouse 中。ClickHouse 服務作為 MySQL 副本，讀取 Binlog 並執行 DDL 和 DML 請求，實現了基於 MySQL Binlog 機制的業務資料庫實時同步功能。

這樣不依賴其他資料同步工具，就能將 MySQL 整庫資料實時同步到 ClickHouse，從而能基於 ClickHouse 構建實時資料倉儲。

ByteHouse 是基於 ClickHouse 增強自研的雲原生資料倉儲，在社群版 ClickHouse 的 MaterializedMySQL 之上進行了功能增強，讓資料同步更穩定，支援便捷地處理同步異常問題。

社群版 MaterializedMySQL 簡介

ClickHouse 社群版透過 DDL 語句在 ClickHouse 上建立一個 database，並將 MySQL 中的指定的一個 database 的全量資料遷移至 ClickHouse，並實時讀取 MySQL 的 binlog 日誌，將 MySQL 中的增量資料實時同步至 ClickHouse 中。

詳細介紹：[experimental] MaterializedMySQL | ClickHouse Docs

 

同步示例

同步一個 MySQL 庫至 ClickHouse 的示例建立語句如下：

CREATE DATABASE db_name ENGINE = MaterializedMySQL(...)
SETTINGS materialized_mysql_tables_list='user_table,catalog_sales'
TABLE OVERRIDE user_table(
    COLUMNS (
        userid UUID,
        category LowCardinality(String),
        timestamp DateTime CODEC(Delta, Default)
    )
    PARTITION BY toYear(timestamp)
),
TABLE OVERRIDE catalog_sales(
    COLUMNS (
        client_ip String TTL created + INTERVAL 72 HOUR
    )
    SAMPLE BY ip_hash
)

功能優勢

MaterializedMySQL 資料同步方案的優勢有：

• 簡單易用：使用一個 DDL 語句就能建立整庫同步任務，能將數百數千張表一鍵同步至 ClickHouse，操作簡單。

• 架構簡單：使用 ClickHouse 本身的計算資源進行資料增量同步，無需搭建其他的資料同步工具，資料架構簡單。

• 時效性好：支援實時同步源端資料，ClickHouse 端幾乎是毫秒和秒級延遲，時效體驗非常好。

ByteHouse 功能增強

社群版 MaterializedMySQL 很大程度瞭解決了 MySQL 庫到 ClickHouse 之間的資料實時同步問題，但也存在不少問題導致其很難應用到生產應用中，主要問題如下：

• 配置選項少

社群版 MaterializedMySQL 不支援同步到分散式表，不支援跳過不相容 DDL 等功能，缺乏這些功能很難將 MaterializedMySQL 用於實際應用中。

• 運維困難

社群版 MaterializedMySQL 不支援同步異常重新同步命令，沒有同步狀態和日誌資訊，同步任務失敗後很難短時間定位問題和恢復同步。

ByteHouse 的 MaterializedMySQL 功能針對使用過程中的問題和困難，做了多處增強，提高了易用性，降低了運維成本。

資料去重

透過 MaterializedMySQL 同步到 ByteHouse 的表預設採用 HaUniqueMergeTree 表引擎，該表引擎支援配置 UNIQUE KEY 唯一鍵，提供 upsert 更新寫語義，源端資料的更新操作在目標端可以實時去重更新。不需要依賴_version、_sign 虛擬列來標記刪除更新，簡化了業務邏輯，提高了易用性。

同步範圍

透過 SETTINGS 引數中配置 include_tables 和 exclude_tables 列表，指定該資料庫下需要同步的表清單或者不需要同步的表清單，否則同步該庫所有的表。

在實際應用中，一個資料庫通常有數百乃至數千張表，其中有些表無需同步、或者資料可能存在異常，可以將這些表加入 exclude_tables 清單，不影響其他表的資料同步。

異常處理

資料同步鏈路無法避免發生異常情況導致同步中斷，ByteHouse 提高了多個功能來簡化異常問題處理。

跳過不支援的語句

MySQL 支援的 DDL 語句非常豐富，有很多語法與 clickhouse 不相容，在 ClickHouse 端執行會報錯中斷同步任務。

可以透過設定 skip_ddl_patterns 引數，用 1 個或多個正規表示式將匹配的 DDL 語句過濾掉，從而避免了報錯和中斷同步任務。

系統日誌表

ByteHouse 提供兩個系統表：system.materialize_mysql_status，system.materialize_mysql_log，分別記錄了每個同步任務的狀態，引數設定和執行日誌。便於實時檢視同步狀態和排查異常問題。

出錯後運維

當同步任務出現了同步異常後，透過檢視執行日誌系統表定為問題。

針對性處理了異常問題後，透過 resync 命令重啟同步任務。

分散式模式

社群版 MaterializedMySQL 的每個同步任務會將源端的一個庫同步至 ClickHouse 的某個節點，不支援按分片邏輯將資料分佈到所有節點，無法利用 ClickHouse 叢集的分散式計算儲存能力；如果在叢集中每個節點都建一個同步庫，則源端一份資料會被同步一份全量至每個 ClickHouse 節點，既浪費了儲存空間，降低了查詢效能，又會對源端產生巨大的壓力。

ByteHouse 支援構建分散式模式的 MaterializedMySQL 庫，將每個表都對應同步至 ByteHouse 的一個分散式表，資料不重複儲存，能充分利用分散式叢集的計算能力，又降低了對源端的同步壓力。

視覺化運維

ByteHouse 同時提供了視覺化運維模組，能實時檢視同步狀態，暴露同步異常，支援線上修復同步異常問題和重啟同步任務。

 

 

最佳實踐

下午將演示將 MySQL 庫中的若干張表同步至 ByteHouse 的全過程。

源端配置

在 MySQL 資料庫端需要配置的引數如下。

開啟 Binlog

設定預設的認證外掛

開啟 GTID 模式

• 使用者許可權  MaterializeMySQL 表引擎使用者必須具備 MySQL 庫的 RELOAD、REPLICATION SLAVE、REPLICATION CLIENT 以及 SELECT PRIVILEGE 許可權  支援的 MySQL 版本 5.65.78.0

源端資料準備

在 MySQL 資料庫裡面建立一個 database，建立兩張表，並插入若干資料。

Show databases;
--【MySQL】Mysql中建立庫
create database db;
use db;
--【MySQL】Mysql中建立表
CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='user info';

CREATE TABLE `data` (
  `id` bigint(20) unsigned NOT NULL,
  `date_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='time';
--【MySQL】Mysql中插入資料
INSERT INTO yangxi.user(id,name) VALUES (111,'step1-1111'),(222,'step1-2222');
INSERT INTO yangxi.data(id,date_time) VALUES (111,now()),(222,now()),(333,now());

建立 MaterializeMySQL

在 ByteHouse 的控制檯資料查詢視窗，建立 MaterializeMySQL 庫。

#【worksheet】建立物化Mysql庫,
--【Clickhouse】在叢集名稱是 bytehouse的叢集上建立物化庫，叢集名稱是個變數
CREATE DATABASE shard_mode_true_mysql_sync on cluster bytehouse
ENGINE = MaterializeMySQL('10.137.xx.xx:3309', 'db', 'username', 'password')
 settings shard_mode=true,allows_query_when_mysql_lost=1，include_tables='user,data'
TABLE OVERRIDE data( PARTITION BY toYYYYMMDD(date_time)) ;
 

引數解釋：

• shard_mode：true 表示是同步至分散式表。

• allows_query_when_mysql_lost：1 表示同步中斷的時候也允許查詢資料。

• include_tables:同步源端 db 庫中 user 和 data 兩張表，其他表跳過不同步。

• OVERRIDE ：ByteHouse 中的 data 表按照 date_time 欄位分割槽。

檢視同步狀態

切換到 ByteHouse 資料管理模組，搜尋 shard_mode_true_mysql_sync 庫，並檢視庫同步狀態

 

同步任務管理

庫-停止同步/開始同步

• 建立庫後預設是同步狀態

• 可以手動停止同步

• 停止中的庫可以手動開始同步

庫-重置同步 

選擇一個同步庫，點選“重置同步”可以從頭開始重新同步整庫

表-重置同步

選擇一個同步中的表 A，點選“重置同步”按鈕，系統將執行以下行為：

1. 關閉同步任務

2. 從源端全量拉取該表的資料至臨時表(A_CHTMP,表名字尾會加上_CHTMP)

3. 刪除目標端原有表 A(如果存在)

4. 將臨時表 A_CHTMP RENAME 為 A

5. 開始增量同步

刪除庫

刪除 ByteHouse 中的庫以及同步關係。

異常處理

系統運維表

在 ByteHouse 管理控制檯，透過下列語句檢視任務同步狀態和錯誤資訊。

select * from cluster('bytehouse','system.materialize_mysql_status',(1,2)) 
select * from cluster('bytehouse','system.materialize_mysql_log',(1,2))

 

單表異常恢復

在源端執行下列 Alter table 語句以後，庫同步會失敗

--修改欄位型別
mysql> ALTER TABLE db.test ADD COLUMN h tinyint;
mysql> ALTER TABLE db.test MODIFY h int default 0;
mysql>ALTER TABLE db.test MODIFY h tinyint default 0;

恢復辦法：

在 ByteHouse 介面上進入表詳情，點選重新同步按鈕。

進入庫詳情頁面，點選開始同步按鈕，即可恢復同步。

在 ByteHouse 中執行下列語句，也可以恢復資料同步

 

--透過下述命令，或者視覺化介面，可以重啟同步
--shard_mode=true情況
:) system resync materialize mysql table on cluster bytehouse shard_mode_true_mysql_sync.test;
:) system restart sync materialize mysql on cluster bytehouse shard_mode_true_mysql_sync;

其他操作

設定跳過 DDL

:) CREATE DATABASE db_mysql_sync_skip on cluster bytehouse  
ENGINE = MaterializeMySQL('10.xx.xx.xx:3309', 'db', 'username', 'password')
SETTINGS include_tables='user,date_time',skip_ddl_patterns='.*add column.*,.*MODIFY.*' 
TABLE OVERRIDE data( PARTITION BY toYYYYMMDD(date_time)) ;

修改 include 和 exclude

透過下列語句修改 include 和 exclude 引數，來修改同步表範圍。

:) alter database shard_mode_true_mysql_sync on cluster bytehouse   modify setting include_tables='user,data,date_time,test';
:) alter database shard_mode_true_mysql_sync on cluster bytehouse 
modify  setting include_tables='',exclude_tables='test3';

異常報警

ByteHouse 提供監控報警功能，在庫同步異常停止或單表同步失敗的時候，可以向管理員傳送報警資訊。

 

點選跳轉 ByteHouse雲原生資料倉儲 瞭解更多