clickhouse-mysql資料同步

大明發表於2022-01-28
MySql

clickhouse版本：22.1.2.2

1.Mysql引擎（不推薦）

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

引擎引數

  • host:port — MySQL服務地址
  • database — MySQL資料庫名稱
  • user — MySQL使用者名稱
  • password — MySQL使用者密碼

這個引擎非常不推薦使用，MySQL引擎將遠端的MySQL伺服器中的表對映到ClickHouse中，MySQL資料庫引擎會將對其的查詢轉換為MySQL語法併傳送到MySQL伺服器中（吐槽：這跟直接用mysql有什麼區別，無語）

2.MaterializedMySQL（不推薦）

這個引擎建立ClickHouse資料庫，包含MySQL中所有的表，以及這些表中的所有資料。ClickHouse伺服器作為MySQL副本工作。它讀取binlog並執行DDL和DML查詢。

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializeMySQL('host:port', ['database' | database], 'user', 'password') [SETTINGS ...]

引擎引數

  • host:port — MySQL服務地址
  • database — MySQL資料庫名稱
  • user — MySQL使用者名稱
  • password — MySQL使用者密碼

引擎配置

  • max_rows_in_buffer — 允許資料快取到記憶體中的最大行數(對於單個表和無法查詢的快取資料)。當超過行數時，資料將被物化。預設值: 65505。
  • max_bytes_in_buffer — 允許在記憶體中快取資料的最大位元組數(對於單個表和無法查詢的快取資料)。當超過行數時，資料將被物化。預設值: 1048576.
  • max_rows_in_buffers — 允許資料快取到記憶體中的最大行數(對於資料庫和無法查詢的快取資料)。當超過行數時，資料將被物化。預設值: 65505.
  • max_bytes_in_buffers — 允許在記憶體中快取資料的最大位元組數(對於資料庫和無法查詢的快取資料)。當超過行數時，資料將被物化。預設值: 1048576.
  • max_flush_data_time — 允許資料在記憶體中快取的最大毫秒數(對於資料庫和無法查詢的快取資料)。當超過這個時間時，資料將被物化。預設值: 1000.
  • max_wait_time_when_mysql_unavailable — 當MySQL不可用時重試間隔(毫秒)。負值禁止重試。預設值: 1000.
  • allows_query_when_mysql_lost — 當mysql丟失時，允許查詢物化表。預設值: 0 (false).

eg：

CREATE DATABASE mysql ENGINE = MaterializeMySQL('localhost:3306', 'db', 'user', '***') 
     SETTINGS 
        allows_query_when_mysql_lost=true,
        max_wait_time_when_mysql_unavailable=10000;

使用步驟：

1.修改mysql的配置，重啟

gtid_mode=ON
enforce_gtid_consistency=1
binlog_format=ROW
log_bin=mysql-bin
expire_logs_days=7
max_binlog_size = 256m
binlog_cache_size = 4m
max_binlog_cache_size = 512m
server-id=1

2.使用以下命令即可

set allow_experimental_database_materialized_mysql = 1;
CREATE DATABASE test ENGINE = MaterializeMySQL('ip:埠', 'mysql的庫', 'mysql賬戶', 'mysql密碼');

問題：

1.此引擎大大方便了mysql匯入資料到clickhouse，但是官方提示還在實驗中，不要用在生產環境

2.本人測試過，大資料量情況下，效率仍然不高，比mysql好一些罷了

3.使用叢集會有很多的侷限

3.mysql表函式

clickhouse允許對儲存在遠端MySQL伺服器上的資料執行SELECT和INSERT查詢

mysql('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

引數

  • host:port — MySQL伺服器地址.
  • database — 遠端資料庫名稱.
  • table — 遠端表名稱.
  • user — MySQL使用者.
  • password — 使用者密碼.
  • replace_query — 將INSERT INTO查詢轉換為REPLACE INTO的標誌。如果replace_query=1`，查詢被替換。
  • on_duplicate_clause — 新增 ON DUPLICATE KEY on_duplicate_clause 表示式到 INSERT 查詢。明確規定只能使用 replace_query = 0 ，如果你同時設定replace_query = 1和on_duplicate_clause`，ClickHouse將產生異常

將mysql的全部資料匯入clickhouse

1.建立clickhouse的表

2.執行命令

INSERT INTO table SELECT * FROM mysql('ip:port', 'database', 'table', 'user', 'password') [where]

注意：1.如果這裡直接使用*，clickhouse欄位順序要和mysql一致，不然會有問題，如果只需要部分欄位可以手動指定

​ 2.這裡可以自己帶查詢條件匯入

​ 3.clickhouse是會出現資料重複的情況的，就算是ReplacingMergeTree也是可能會出現重複資料的

4.canal同步

1.準備好jar包（避免踩坑）：https://mvnrepository.com/

clickhouse-jdbc-0.2.6.jar
httpclient-4.5.13.jar
httpcore-4.4.13.jar
lz4-1.5.0.jar
lz4-java-1.8.0.jar
druid-1.2.8.jar(這個版本不能太低，否則druid連線池不支援clickhouse)

2.docker安裝Canal-Server

docker run --name canal-server -p 11111:11111 -d canal/canal-server:latest

3.進入容器

複製/home/admin/canal-server/conf/example為你要建立的新資料庫例項名稱，這裡使用example2

4.修改example2下的instance.properties配置

#MySQL地址
canal.instance.master.address=127.0.0.1:3306
#mysql賬戶
canal.instance.dbUsername=root
canal.instance.dbPassword=root
#過濾規則
canal.instance.filter.regex=example2\\*

5.修改/home/admin/canal-server/conf/canal.properties配置

canal.serverMode = tcp
canal.destinations = example2

6.安裝canal-adapter（你也可以用kafka）

docker run --name canal-adapter -p 8081:8081 -d slpcat/canal-adapter:v1.1.5

7.修改canal-adapter的/opt/canal-adapter/conf/application.yml配置檔案（容器內）

canal.conf:
# 此處注意模式
  mode: tcp 
  flatMessage: true
  zookeeperHosts:
  syncBatchSize: 1000
  retries: 0
  timeout:
  accessKey:
  secretKey:
  consumerProperties:
    # 此處注意canal服務的地址
    canal.tcp.server.host: 127.0.0.1:11111
    canal.tcp.zookeeper.hosts:
    canal.tcp.batch.size: 500
    canal.tcp.username:
    canal.tcp.password:
    

  srcDataSources:
  # 此處注意mysql連線
    defaultDS:
      url: jdbc:mysql://127.0.0.1:3306/example2?useUnicode=true
      username: root
      password: root
  canalAdapters:
  - instance: example2
    groups:
    - groupId: g1
      outerAdapters:
      - name: logger
	  - name: rdb
         key: mysql1
         # clickhouse的配置
         properties:
           jdbc.driverClassName: ru.yandex.clickhouse.ClickHouseDriver
           jdbc.url: jdbc:clickhouse://127.0.0.1:8123
           jdbc.username: default
           jdbc.password: default

8.修改/opt/canal-adapter/conf/rdb下的mytest_user.yml

dataSourceKey: defaultDS  
destination: example2  
groupId: g1  
outerAdapterKey: mysql  
concurrent: true  
dbMapping:  
  mirrorDb: true  
  database: example2

9.重啟server和adapter

往mysql裡插入資料檢視是否有資料同步過來，如果沒有檢視日誌排查問題

注意點

1.mysql要開啟配置
gtid_mode=ON
enforce_gtid_consistency=1
binlog_format=ROW
log_bin=mysql-bin
expire_logs_days=7
max_binlog_size = 256m
binlog_cache_size = 4m
max_binlog_cache_size = 512m
server-id=1
2.mysql和clickhouse庫和表要提前建立好
3.docker配置用127.0.0.1時要注意使用主機模式，否則容器之間時無法通訊的
4.如果mysql或者clickhouse無法連線成功，檢查一下驅動的版本和jdbc連線寫得對不對

5.其他的方案：紅帽的debezium

相關文章