æ´å¤ææ¯äº¤æµãæ±èæºä¼ï¼æ¬¢è¿å ³æ³¨åèè·³å¨æ°æ®å¹³å°å¾®ä¿¡å ¬ä¼å·ï¼åå¤ã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ï¼ç¹å»âéç½®åæ¥âæé®ï¼ç³»ç»å°æ§è¡ä»¥ä¸è¡ä¸ºï¼
- å ³éåæ¥ä»»å¡
- ä»æºç«¯å ¨éæå该表çæ°æ®è³ä¸´æ¶è¡¨(A_CHTMP,表ååç¼ä¼å ä¸_CHTMP)
- å é¤ç®æ 端åæ表 A(å¦æåå¨)
- å°ä¸´æ¶è¡¨ A_CHTMP RENAME 为 A
- å¼å§å¢éåæ¥
å é¤åº
å é¤ 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 æä¾çæ§æ¥è¦åè½ï¼å¨åºåæ¥å¼å¸¸åæ¢æå表åæ¥å¤±è´¥çæ¶åï¼å¯ä»¥å管çååéæ¥è¦ä¿¡æ¯ã
ç¹å»è·³è½¬ByteDance Cloud | ByteHouseäºè§£æ´å¤