1 安装docker及PolarDB-X（略）
2 安装canal server
下载安装脚本
[root@iZbp13eg5pfeabf4cmf92eZ ~]# wget https://raw.githubusercontent.com/alibaba/canal/master/docker/run.sh
--2022-06-16 09:05:59-- https://raw.githubusercontent.com/alibaba/canal/master/docker/run.sh
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 185.199.110.133, 185.199.109.133, 185.199.108.133, ...
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|185.199.110.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 2551 (2.5K) [text/plain]
Saving to: ‘run.sh’
run.sh 100%[==============================================>] 2.49K --.-KB/s in 0s
2022-06-16 09:05:59 (50.7 MB/s) - ‘run.sh’ saved [2551/2551]
运行安装脚本，实例的master地址为源端数据库的地址和端口，这里填入申请到的ECS的弹性地址这个脚本会自动下载相应的docker映像，并启动容器
[root@iZbp13eg5pfeabf4cmf92eZ ~]# sh run.sh -e canal.auto.scan=false \
> -e canal.destinations=test \
> -e canal.instance.master.address=114.55.7.239:8527 \
> -e canal.instance.dbUsername=polardbx_root \
> -e canal.instance.dbPassword=123456 \
> -e canal.instance.connectionCharset=UTF-8 \
> -e canal.instance.tsdb.enable=true \
> -e canal.instance.gtidon=false
docker run -d -it -h 0 -e canal.auto.scan=false -e canal.destinations=test -e canal.instance.master.address=114.55.7.239:8527 -e canal.instance.dbUsername=polardbx_root -e canal.instance.dbPassword=123456 -e canal.instance.connectionCharset=UTF-8 -e canal.instance.tsdb.enable=true -e canal.instance.gtidon=false --name=canal-server --net=host -m 4096m canal/canal-server
Unable to find image 'canal/canal-server:latest' locally
latest: Pulling from canal/canal-server
1c8f9aa56c90: Pull complete
c5e21c824d1c: Pull complete
4ba7edb60123: Pull complete
80d8e8fac1be: Pull complete
bce514860fc9: Pull complete
0b8a43c81049: Pull complete
a81188309a68: Pull complete
4f4fb700ef54: Pull complete
Digest: sha256:a5e93c0a1e452cdf17f4278ba0f5e7c902ee561385c264826ccd79797f2f872f
Status: Downloaded newer image for canal/canal-server:latest
c8c8c86b3653c6be0475f94a8179ff27df2cda2d221ac0ceaa471fc849bfdf7b
3 安装clickhouse
clickhouse以docker容器方式运行
[root@iZbp13eg5pfeabf4cmf92eZ ~]# docker run -d --name some-clickhouse-server --ulimit nofile=262144:262144 -p 8123:8123 yandex/clickhouse-server
Unable to find image 'yandex/clickhouse-server:latest' locally
latest: Pulling from yandex/clickhouse-server
ea362f368469: Pull complete
38ba82a23e2b: Pull complete
9b17d04b6c62: Pull complete
5658714e4e8b: Pull complete
6bde977a0bf8: Pull complete
39053b27290b: Pull complete
762d3d237065: Pull complete
Digest: sha256:1cbf75aabe1e2cc9f62d1d9929c318a59ae552e2700e201db985b92a9bcabc6e
Status: Downloaded newer image for yandex/clickhouse-server:latest
4116fb78b90469b1c08675dc62b2b24337ecf0d47dde313d76db0a0e850f8c80
4 在PolarDB-X和clickhouse中创建数据库和表
在mysql中创建表
[root@iZbp13eg5pfeabf4cmf92eZ ~]# mysql -h127.0.0.1 -P8527 -upolardbx_root -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.55 sec)
mysql> use testdb;
Database changed
mysql> CREATE TABLE test(
-> id INT(11) AUTO_INCREMENT PRIMARY KEY,
-> name CHAR(20) not null );
Query OK, 0 rows affected (0.97 sec)
mysql> exit
Bye
在clickhouse中创建表，先启动一个clickhouse客户端容器连接至clickhouse数据库
[root@iZbp13eg5pfeabf4cmf92eZ ~]# docker run -it --rm --link some-clickhouse-server:clickhouse-server yandex/clickhouse-client --host clickhouse-server
Unable to find image 'yandex/clickhouse-client:latest' locally
latest: Pulling from yandex/clickhouse-client
2f94e549220a: Pull complete
a72d8599d7c2: Pull complete
e9232762ed9d: Pull complete
29c8f4b1e77e: Pull complete
Digest: sha256:9ae2ee421c9c9f00406a39a1174276aa23abb7fceac13b40578b18eeaa9bc4d1
Status: Downloaded newer image for yandex/clickhouse-client:latest
ClickHouse client version 22.1.3.7 (official build).
Connecting to clickhouse-server:9000 as user default.
Connected to ClickHouse server version 22.1.3 revision 54455.
4116fb78b904 :) CREATE DATABASE testdb;
CREATE DATABASE testdb
Query id: ad9ff6a8-59ce-4d9c-a537-70ad040fa3ca
Ok.
0 rows in set. Elapsed: 0.003 sec.
4116fb78b904 :) USE testdb;
USE testdb
Query id: 4fa7e43c-5718-4444-8deb-2768096505cd
Ok.
0 rows in set. Elapsed: 0.001 sec.
4116fb78b904 :) Create Table test(id INT(32),name CHAR(20)) Engine = MergeTree() Order By id;
CREATE TABLE test
(
`id` INT(32),
`name` CHAR(20)
)
ENGINE = MergeTree
ORDER BY id
Query id: d1e6269e-8db5-4cfa-bb0c-ae281aa24431
Ok.
0 rows in set. Elapsed: 0.010 sec.
4116fb78b904 :) exit
Bye.
5 运行Canal Client消费并投递增量变更
安装java-1.8.0
[root@iZbp13eg5pfeabf4cmf92eZ ~]# yum -y install java-1.8.0-openjdk*
Complete!
下载Canal polardb-x-clickhouse客户端
[root@iZbp13eg5pfeabf4cmf92eZ ~]# wget https://labfileapp.oss-cn-hangzhou.aliyuncs.com/polardb-x-to-clickhouse-canal-client.jar
--2022-06-16 09:21:27-- https://labfileapp.oss-cn-hangzhou.aliyuncs.com/polardb-x-to-clickhouse-canal-client.jar
Resolving labfileapp.oss-cn-hangzhou.aliyuncs.com (labfileapp.oss-cn-hangzhou.aliyuncs.com)... 118.31.219.220
Connecting to labfileapp.oss-cn-hangzhou.aliyuncs.com (labfileapp.oss-cn-hangzhou.aliyuncs.com)|118.31.219.220|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 43089777 (41M) [application/java-archive]
Saving to: ‘polardb-x-to-clickhouse-canal-client.jar’
polardb-x-to-clickhouse-cana 100%[==============================================>] 41.09M 9.15MB/s in 4.5s
2022-06-16 09:21:32 (9.15 MB/s) - ‘polardb-x-to-clickhouse-canal-client.jar’ saved [43089777/43089777]
运行客户端，客户端不能关闭
[root@iZbp13eg5pfeabf4cmf92eZ ~]# java -jar polardb-x-to-clickhouse-canal-client.jar
09:22:29.751 [main] DEBUG com.clickhouse.jdbc.ClickHouseDriver - ClickHouse Driver 0.0.0.0(JDBC: 0.0.0.0) registered
09:22:29.773 [main] DEBUG c.c.j.i.ClickHouseConnectionImpl - Creating a new connection to jdbc:clickhouse:http://localhost:8123/testdb
empty count : 1
6 检查数据投递效果
连接至polardbx，插入几行数据
[root@iZbp13eg5pfeabf4cmf92eZ ~]# mysql -h127.0.0.1 -P8527 -upolardbx_root -p123456
mysql> use testdb;
插入三行数据
mysql> INSERT INTO test(name) values("polardb-x"), ("is"), ("awsome");
Query OK, 3 rows affected (0.03 sec)
连接至clickhouse查看数据
[root@iZbp13eg5pfeabf4cmf92eZ ~]# docker run -it --rm --link some-clickhouse-server:clickhouse-server yandex/clickhouse-client --host clickhouse-server
4116fb78b904 :) use testdb;
4116fb78b904 :) SELECT * FROM test;
SELECT *
FROM test
Query id: dc1c99e8-8bc7-4eee-91b8-d45103fc898a
┌─id─┬─name──────┐
│ 1 │ polardb-x │
└────┴───────────┘
┌─id─┬─name─┐
│ 2 │ is │
└────┴──────┘
┌─id─┬─name───┐
│ 3 │ awsome │
└────┴────────┘
可以看到数据已经投递到clickhouse中
7 canalserver状态检查
通过检查日志来检查calnalserver和实例的状态，日志的路径位于
/home/admin/canal-server/logs/
检查canal目录下canal.log文件可以查看canal-server的状态
[root@0 canal]# cat canal.log
2022-06-16 09:08:48.728 [main] INFO com.alibaba.otter.canal.deployer.CanalLauncher - ## set default uncaught exception handler
2022-06-16 09:08:48.746 [main] INFO com.alibaba.otter.canal.deployer.CanalLauncher - ## load canal configurations
2022-06-16 09:08:48.758 [main] INFO com.alibaba.otter.canal.deployer.CanalStarter - ## start the canal server.
2022-06-16 09:08:48.812 [main] INFO com.alibaba.otter.canal.deployer.CanalController - ## start the canal server[172.17.0.1(172.17.0.1):11111]
2022-06-16 09:08:50.137 [main] INFO com.alibaba.otter.canal.deployer.CanalStarter - ## the canal server is running now ......
检查test目录下test.log可以查看实例的状态，这里test时实例名
[root@0 test]# cat test.log
2022-06-16 09:08:50.064 [main] INFO c.a.otter.canal.instance.spring.CanalInstanceWithSpring - start CannalInstance for 1-test
2022-06-16 09:08:50.083 [main] WARN c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table filter : ^.*\..*$
2022-06-16 09:08:50.083 [main] WARN c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table black filter : ^mysql\.slave_.*$
2022-06-16 09:08:50.089 [main] INFO c.a.otter.canal.instance.core.AbstractCanalInstance - start successful....
2022-06-16 09:08:50.285 [destination = test , address = /114.55.7.239:8527 , EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - ---> begin to find start position, it will be long time for reset or first position
2022-06-16 09:08:50.285 [destination = test , address = /114.55.7.239:8527 , EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - prepare to find start position just show master status
2022-06-16 09:08:52.780 [destination = test , address = /114.55.7.239:8527 , EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - ---> find start position successfully, EntryPosition[included=false,journalName=binlog.000001,position=4,serverId=193317851,gtid=<null>,timestamp=1655341507000] cost : 2485ms , the next step is binlog dump
2022-06-16 09:22:29.597 [New I/O server worker #1-1] INFO c.a.otter.canal.instance.core.AbstractCanalInstance - subscribe filter change to .*\..*
2022-06-16 09:22:29.597 [New I/O server worker #1-1] WARN c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table filter : ^.*\..*$
8 通过canal client的控制台输出查看投递到clickhouse的数据
09:24:35.559 [main] DEBUG c.c.c.data.ClickHouseStreamResponse - 0 bytes skipped before closing input stream
================> binlog[binlog.000001:16765] , name[,] , eventType : QUERY
================> binlog[binlog.000001:16883] , name[testdb,test] , eventType : INSERT
id : 1 update=true
name : polardb-x update=true
09:24:35.590 [main] DEBUG com.clickhouse.client.AbstractClient - Connecting to: ClickHouseNode(addr=http:localhost:8123, db=testdb)@1339874339
09:24:35.590 [main] DEBUG com.clickhouse.client.AbstractClient - Connection established: com.clickhouse.client.http.HttpUrlConnectionImpl@3b084709
09:24:35.590 [main] DEBUG c.c.client.http.ClickHouseHttpClient - Query: INSERT INTO test(id, name) values(1,'polardb-x')
09:24:35.611 [main] DEBUG c.c.c.data.ClickHouseStreamResponse - 0 bytes skipped before closing input stream
SQL done: INSERT INTO test(id, name) values(1,'polardb-x')
id : 2 update=true
name : is update=true
09:24:35.612 [main] DEBUG com.clickhouse.client.AbstractClient - Connecting to: ClickHouseNode(addr=http:localhost:8123, db=testdb)@1339874339
09:24:35.612 [main] DEBUG com.clickhouse.client.AbstractClient - Connection established: com.clickhouse.client.http.HttpUrlConnectionImpl@59e5ddf
09:24:35.612 [main] DEBUG c.c.client.http.ClickHouseHttpClient - Query: INSERT INTO test(id, name) values(2,'is')
09:24:35.616 [main] DEBUG c.c.c.data.ClickHouseStreamResponse - 0 bytes skipped before closing input stream
SQL done: INSERT INTO test(id, name) values(2,'is')
id : 3 update=true
name : awsome update=true
09:24:35.617 [main] DEBUG com.clickhouse.client.AbstractClient - Connecting to: ClickHouseNode(addr=http:localhost:8123, db=testdb)@1339874339
09:24:35.618 [main] DEBUG com.clickhouse.client.AbstractClient - Connection established: com.clickhouse.client.http.HttpUrlConnectionImpl@536aaa8d
09:24:35.618 [main] DEBUG c.c.client.http.ClickHouseHttpClient - Query: INSERT INTO test(id, name) values(3,'awsome')
09:24:35.625 [main] DEBUG c.c.c.data.ClickHouseStreamResponse - 0 bytes skipped before closing input stream
在canal client的控制台上可以看到投递到clickhouse的sql语句，从上面标红的部分可以看到三条记录分别执行了三条insert语句。
