clickhouse-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服务器上的数据执行SELECTINSERT查询
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 = 1on_duplicate_clause`,ClickHouse将产生异常
将mysql的全部数据导入clickhouse 1.创建clickhouse的表
【clickhouse-mysql数据同步】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

    推荐阅读