服务器环境
OS : CentOS Linux release 7.5.1804 (Core)
CPU: 4
MEM: 8g
JAVA: jdk1.8
canal 版本
canal.deployer: 1.1.4_with_updatedelete
canal.adapter: 1.1.4
离线安装
下载安装包
canal.deployer-1.1.4.tar.gz
canal.adapter-1.1.4_with_updatedelete.tar.gz
创建目录 并解压
mkdir -p /data1/canal_deployer
mkdir -p /data1/canal_adapter
tar zxvf canal.deployer-1.1.4.tar.gz -C /data1/canal_deployer
tar zxvf canal.adapter-1.1.4_with_updatedelete.tar.gz -C /data1/canal_adapte
mysql 和clickhouse间的数据同步
mysql ，clickhouse 版本
mysql: 8.0.11
clickhouse: 21.4.6
由于mysql ，clickhouse 版本 问题需要对canal jar包的更新
下载的jar包
httpclient-4.3.jar
httpcore-4.4.5.jar
clickhouse-jdbc-0.2.jar
mysql-connector-java-8.0.22.jar
更新替换adapter 的lib
放到 canal_adapter/lib 下，
chmod 777 httpclient-4.3.jar httpcore-4.4.5.jar clickhouse-jdbc-0.2.jar mysql-connector-java-8.0.22.jar
chmod 4777 httpclient-4.3.jar httpcore-4.4.5.jar clickhouse-jdbc-0.2.jar mysql-connector-java-8.0.22.jar
注：除了传统的读r、写w、执行x以外，还有Linux的文件特殊权限，他们分别是Set UID、Set GID、Sticky Bit三种，也就是多出来的那一位
Set UID，SUID
权值：4
符号：x --> s
特点：仅对可执行文件有效。
功能：可执行文件执行时，拥有文件所有者的权限。
案例：/usr/bin/passwd 权限为4755，普通用户可执行passwd命令时，对应的普通用户，随机秒变高富帅，获得了root权限，可以修改普通用户平常根本想都不敢想、无法修改的root拥有的/etc/shadow系统文件（如果/usr/bin/passwd 权限为755，则普通用户执行passwd的时候，会出现无权限修改root own的/etc/shadow文件的问题）
引用：https://www.cnblogs.com/zklidd/p/7100276.html
配置更改
上游mysql
# 在上游mysql上创建同步用户
CREATE USER canal IDENTIFIED BY 'canal';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
FLUSH PRIVILEGES;
#在上游mysql ,配置文件配置
log-bin=mysql-bin # 开启 binlog
binlog-format=ROW # 选择 ROW 模式
server_id=1 # 配置 MySQL replaction 需要定义，不要和 canal 的 slaveId 重复
中间 canal配置
修改 deployer 的配置文件(一个实例配置一个instance)
vim canal_deployer/conf/example/instance.properties === canal.instance.master.address=10.50.133.81:3306 #上游mysql ip:port canal.instance.mysql.slaveId=1234 #canal伪装成mysql从库的server_id(要唯一) canal.instance.dbUsername=canal canal.instance.dbPassword=canal canal.instance.connectionCharset = UTF-8 ===
修改 adapter 的配置文件
vim canal_adapter/conf/application.yml === #canal-server地址 canalServerHost: 127.0.0.1:11111 #mysql连接信息 url: jdbc:mysql://10.50.133.81:3306/example?useUnicode=true username: canal password: canal === vim canal_adapter/conf/application.yml === - name: rdb #rdb类型 key: mysql properties: #clickhouse数据看配置 jdbc.driverClassName: ru.yandex.clickhouse.ClickHouseDriver jdbc.url: jdbc:clickhouse://127.0.0.1:8123/example jdbc.username: default jdbc.password: === vim canal_adapter/conf/rdb/mytest_user.yml === ## Mirror schema synchronize config dataSourceKey: defaultDS destination: example groupId: g1 outerAdapterKey: mysql concurrent: true dbMapping: #mirrorDb: true 可以同步DDL mirrorDb: true database: example ===
clickhouse，mysql同步数据前准备工作
新建与mysql同名库，在clickhouse执行
create database example;
全量同步每一张要同步的表，在clickhouse执行
create table t_org engine = MergeTree order by ID as select * from MySQL('10.50.133.81:3306','example', 't_org', 'sys','1234.Com') ;
#此种方法需要clickhouse支持mysql引擎，查看方法：
select name from system.build_options
结果中包含USE_MYSQL,则支持。
一些要注意的问题
目标库特殊字符
目标库名称不支持中划线（-）
DDL同步
如果需要使用DDL同步能力，必须在rdb中配置mirroDb为true才可以。
alter 语句中不能有 NOT NULL, 主要是不能有NOT,如果存在的话会报错，
Caused by: java.lang.RuntimeException: ru.yandex.clickhouse.except.ClickHouseException: ClickHouse exception, code: 62, host: 127.0.0.1, port: 8123; Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 54 ('NOT'): NOT NULL DEFAULT ''. Expected one of: AFTER, CODEC, end of query, INTO OUTFILE, ALIAS, FIRST, TTL, Comma, SETTINGS, FORMAT, DEFAULT, MATERIALIZED, COMMENT, token (version 21.4.6.55 (official build))
github已经提了bug:
https://github.com/alibaba/canal/issues/3527
启动方式
启动 deployer
canal_deployer/bin/startup.sh
查看deployer 启动日志
root@CLICKHOUSE-TEST1:/data1/canal_deployer#tail -f logs/canal/canal.log
2021-05-13 10:14:58.893 [main] INFO com.alibaba.otter.canal.deployer.CanalLauncher - ## set default uncaught exception handler
2021-05-13 10:14:58.928 [main] INFO com.alibaba.otter.canal.deployer.CanalLauncher - ## load canal configurations
2021-05-13 10:14:58.940 [main] INFO com.alibaba.otter.canal.deployer.CanalStarter - ## start the canal server.
2021-05-13 10:14:59.000 [main] INFO com.alibaba.otter.canal.deployer.CanalController - ## start the canal server[10.50.132.48(10.50.132.48):11111]
2021-05-13 10:15:00.329 [main] INFO com.alibaba.otter.canal.deployer.CanalStarter - ## the canal server is running now ......
查看deployer instance 的日志
root@CLICKHOUSE-TEST1:/data1/canal_deployer#tail -f logs/example/example.log
2021-05-13 10:15:00.275 [main] INFO c.a.otter.canal.instance.spring.CanalInstanceWithSpring - start CannalInstance for 1-example
2021-05-13 10:15:00.287 [main] WARN c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table filter : ^.*\..*$
2021-05-13 10:15:00.287 [main] WARN c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table black filter : ^mysql\.slave_.*$
2021-05-13 10:15:00.292 [main] INFO c.a.otter.canal.instance.core.AbstractCanalInstance - start successful....
2021-05-13 10:15:00.391 [destination = example , address = /10.50.133.81:3306 , 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
2021-05-13 10:15:00.392 [destination = example , address = /10.50.133.81:3306 , EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - prepare to find start position just show master status
2021-05-13 10:15:01.674 [destination = example , address = /10.50.133.81:3306 , EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - ---> find start position successfully, EntryPosition[included=false,journalName=mysql-bin.000034,position=4,serverId=8,gtid=<null>,timestamp=1620697739000] cost : 1269ms , the next step is binlog dump
启动 adapter
canal_adapter/bin/startup.sh
查看adapter 的日志
tail -f logs/adapter/adapter.log
2021-05-13 16:24:10.541 [main] ERROR com.alibaba.druid.pool.DruidDataSource - testWhileIdle is true, validationQuery not set
2021-05-13 16:24:10.693 [main] INFO com.alibaba.druid.pool.DruidDataSource - {dataSource-2} inited
2021-05-13 16:24:10.698 [main] INFO c.a.o.canal.adapter.launcher.loader.CanalAdapterLoader - Load canal adapter: rdb succeed
2021-05-13 16:24:10.706 [main] INFO c.a.o.canal.adapter.launcher.loader.CanalAdapterLoader - Start adapter for canal instance: example succeed
2021-05-13 16:24:10.707 [main] INFO c.a.o.canal.adapter.launcher.loader.CanalAdapterService - ## the canal client adapters are running now ......
2021-05-13 16:24:10.709 [Thread-4] INFO c.a.o.canal.adapter.launcher.loader.CanalAdapterWorker - =============> Start to connect destination: example <=============
2021-05-13 16:24:10.712 [main] INFO org.apache.coyote.http11.Http11NioProtocol - Starting ProtocolHandler ["http-nio-8081"]
2021-05-13 16:24:10.716 [main] INFO org.apache.tomcat.util.net.NioSelectorPool - Using a shared selector for servlet write/read
2021-05-13 16:24:10.738 [main] INFO o.s.boot.web.embedded.tomcat.TomcatWebServer - Tomcat started on port(s): 8081 (http) with context path ''
2021-05-13 16:24:10.741 [main] INFO c.a.otter.canal.adapter.launcher.CanalAdapterApplication - Started CanalAdapterApplication in 3.753 seconds (JVM running for 4.295)
2021-05-13 16:24:10.822 [Thread-4] INFO c.a.o.canal.adapter.launcher.loader.CanalAdapterWorker - =============> Start to subscribe destination: example <=============
2021-05-13 16:24:10.878 [Thread-4] INFO c.a.o.canal.adapter.launcher.loader.CanalAdapterWorker - =============> Subscribe destination: example succeed <=============
2021-05-13 16:24:11.026 [pool-8-thread-1] INFO c.a.o.canal.client.adapter.logger.LoggerAdapterExample - DML: {"data":null,"database":"example","destination":"example","es":1620884372000,"groupId":null,"isDdl":true,"old":null,"pkNames":null,"sql":"create database example","table":"","ts":1620894250944,"type":"QUERY"}
===
testWhileIdle is true, validationQuery not set
验证数据库连接是否正常,不重要
===
