mysql选择表同步clickhouse

由于mysql表的字段存在json格式，但是clickhouse不支持此格式，导致同步失败，造成查询失败问题。
所以需要过滤掉此表，在github里提问了此问题。回答是没有排除的设置，只有选择同步的表。
所以我们需要把同步的表填上。下面具体操作方式

CREATE DATABASE IF NOT EXISTS test
ENGINE = MaterializedMySQL('localhost:3306', 'test', 'root', 'root')
SETTINGS materialized_mysql_tables_list = 'user, orders'

Query id: 87700832-b5cc-4c44-aa2f-d9ebce5ebb32


0 rows in set. Elapsed: 0.001 sec.

Received exception from server (version 21.12.3):
Code: 501. DB::Exception: Received from localhost:9000. DB::Exception: Cannot create MySQL database, because Code: 115. DB::Exception: Unknown setting materialized_mysql_tables_list: for database MaterializedMySQL. (UNKNOWN_SETTING),. (CANNOT_CREATE_DATABASE)

上面命令错误，表示不支持materialized_mysql_tables_list此设置。应该是版本不支持。所以需要升级版本。
clickhouse-server使用docker安装。最新的版本是21.*, 官网上的版本已经到22.*，所以弃用docker,选择最新版本。

安装操作

根据当前的系统，参考官网来安装。

安装完成后，并启动。

# 连接server
[root@iZbp12r3x27gdoyse3qgpwZ clickhouse-server]# clickhouse-client
ClickHouse client version 22.7.1.2484 (official build).
Connecting to localhost:9000 as user default.
Code: 210. DB::NetException: Net Exception: Connection reset by peer (localhost:9000). (NETWORK_ERROR)

连接报错，原因是由于当前的9000端口被占用，导致服务未启动。这里需要修改下端口号

# 修改配置文件的监听端口号
[root@iZbp12r3x27gdoyse3qgpwZ clickhouse-server]# sed -i 's/9000/9900/g' config.xml
[root@iZbp12r3x27gdoyse3qgpwZ clickhouse-server]# grep '9900' ./ -r
./config.xml:    <tcp_port>9900</tcp_port>
./config.xml:                    <port>9900</port>
./config.xml:                    <port>9900</port>
./config.xml:                    <port>9900</port>
./config.xml:                    <port>9900</port>
./config.xml:                    <port>9900</port>
./config.xml:                    <port>9900</port>
./config.xml:                    <port>9900</port>
./config.xml:                     <port>9900</port>
./config.xml:                     <port>9900</port>
./config.xml:                    <port>9900</port>
./config.xml:                    <port>9900</port>
./config.xml:                    <port>9900</port>
./config.xml:                    <port>9900</port>
./config.xml:            <port>9900</port>

然后指定端口号连接

oot@iZbp12r3x27gdoyse3qgpwZ clickhouse-server]# clickhouse-client  --host 127.0.0.1 --port 9900
ClickHouse client version 22.7.1.2484 (official build).
Connecting to 127.0.0.1:9900 as user default.
Connected to ClickHouse server version 22.7.1 revision 54457.

# 开启同步
ca766e6b6e77 :) set allow_experimental_database_materialized_mysql=1;

SET allow_experimental_database_materialized_mysql = 1

Query id: 0e357a76-152d-4a0a-956b-ddb6abd7c7d8

Ok.

0 rows in set. Elapsed: 0.001 sec.

# 选择同步的表
CREATE DATABASE IF NOT EXISTS test
ENGINE = MaterializedMySQL('localhost:3306', 'test', 'root', 'root')
SETTINGS materialized_mysql_tables_list = 'user, orders'

Query id: 473647be-c9c8-4f4a-8066-9b708b447296

Ok.

0 rows in set. Elapsed: 0.040 sec.

# 查询表
iZbp12r3x27gdoyse3qgpwZ :) show tables;

SHOW TABLES

Query id: d7af00f1-9e9e-4b00-acb2-1f519d4b7527

┌─name─────────┐
│ orders       │
│ user         │
└──────────────┘

5 rows in set. Elapsed: 0.003 sec.

本作品采用《CC 协议》，转载必须注明作者和本文链接