乐趣区

关于clickhouse:clickhousemysql数据同步

jiezi

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

退出移动版