Canal同步MYSQL到ES
准备工作
以下测试均在LINUX环境下进行。
| 组件 | 版本 |
|---|---|
| CANAL | 1.1.5 |
| MYSQL | 5.7.24 |
| ES | 7.6.2 |
| JDK | 1.8 |
MYSQL，ES以及JDK的安装部署这里就不赘述了，需注意一点就是MYSQL必须开启binlog，且binlog_format设置为ROW。
以下是我要同步数据的表结构
CREATE TABLE `sys_log` (
`id` varchar(64) NOT NULL COMMENT '编号',
`type` char(1) DEFAULT '1' COMMENT '日志类型',
`title` varchar(255) DEFAULT '' COMMENT '日志标题',
`create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
`create_date` datetime DEFAULT NULL COMMENT '创建时间',
`remote_addr` varchar(255) DEFAULT NULL COMMENT '操作IP地址',
`user_agent` varchar(255) DEFAULT NULL COMMENT '用户代理',
`request_uri` varchar(255) DEFAULT NULL COMMENT '请求URI',
`method` varchar(5) DEFAULT NULL COMMENT '操作方式',
`params` text COMMENT '操作提交的数据',
`exception` text COMMENT '异常信息',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='日志表';
用kibana在ES建立对应的索引，kibana的安装使用请自行百度。
可以看到数据表的字段在ES中都转变成了驼峰的格式，这个在下面canal adapter 配置时会讲。
PUT sys_log
{
"mappings": {
"properties":{
"type":{
"type":"keyword"
},
"title":{
"type":"text"
},
"createBy":{
"type":"keyword"
},
"remoteAddr":{
"type": "text"
},
"userAgent":{
"type":"text"
},
"requestUri": {
"type": "text"
},
"method": {
"type": "keyword"
},
"params": {
"type": "text"
},
"exception": {
"type": "text"
},
"createDate": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
}
}
}
}
安装Canal
在Canal官网下载v1.1.5版本的deployer和adapter包，并上传到LINUX服务器目录/home/canal下，执行以下命令解压。
cd /home/canal
mkdir adapter
mkdir deployer
tar -zxvf canal.adapter-1.1.5.tar.gz -C adapter/
tar -zxvf canal.deployer-1.1.5.tar.gz -C deployer/
修改deployer配置
vim /home/canal/deployer/conf/example/instance.properties
修改数据库的地址以及用户名密码
# position info
canal.instance.master.address=127.0.0.1:3306
# username/password
canal.instance.dbUsername=root
canal.instance.dbPassword=123456
修改adapter配置
vim /home/canal/adapter/conf/application.yml
修改数据库的地址以及用户名密码，以及ES连接信息
srcDataSources:
defaultDS:
url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true
username: root
password: 123456
canalAdapters:
- instance: example # canal instance Name or mq topic name
groups:
- groupId: g1
outerAdapters:
- name: logger # 输出到日志，可以注释掉
- name: es7 # 输出到ES，如果用的是ES6.x版本就写es6
hosts: http://127.0.0.1:9200 # 127.0.0.1:9300 for transport mode
properties:
mode: rest # transport or rest
# security.auth: test:123456 # only used for rest mode
cluster.name: my-application # ES里配置的集群名
因为我用的是ES7.x版本，所以配置文件要写到es7目录下。这个目录下原本就存在三个配置文件 biz_order.yml,customer_yml,mytest_user.yml，是官方给的配置参考，可以直接删除。
建立一个sys_log.yml文件，写入以下内容。
dataSourceKey: defaultDS
destination: example
groupId: g1
esMapping:
_index: sys_log
_id: _id
# upsert: true
# pk: id
sql: "select id as _id, type, title, create_by as createBy,create_date as createDate,
remote_addr as remoteAddr,user_agent as userAgent,request_uri as requestUri,method,
params,exception from sys_log"
# objFields:
# _labels: array:;
etlCondition: "where create_date>={}"
commitBatch: 3000
这里解释了为什么ES索引中字段名是驼峰，因为sql中给字段取了别名。另外，etlCondition中官网里写的是'{0}'，可能是没有更新过来，我这里直接写{}是可以的。
启动
先启动deployer
sh /home/canal/deployer/bin/startup.sh
再启动adapter
sh /home/canal/adapter/bin/startup.sh
查看 adapter 日志
tail -f /home/canal/adapter/logs/adapter/adapter.log
全量同步
curl -X POST http://127.0.0.1:8081/etl/es7/sys_log.yml -d "params=2022-01-01 00:00:00"
这里的params参数会替换掉etlCondition中的{}，sql执行的结果集会同步到ES中去。
执行成功后请求的响应：
{"succeeded":true,"resultMessage":"导入ES 数据：687 条"}
adapter 日志中打印：
start etl to import data to index: sys_log
数据全量导入完成, 一共导入 687 条数据, 耗时: 1255
增量同步
往sys_log表里插入一条数据
INSERT INTO `sys_log` (`id`, `type`, `title`, `create_by`, `create_date`, `remote_addr`, `user_agent`, `request_uri`, `method`, `params`, `exception`) VALUES ('f4758815a07447c780f0ff5436425236', '1', '系统登录', '1', '2022-04-20 11:09:21', '127.0.0.1', 'ua', '/admin/login', 'GET', 'params', 'exception');
此时日志中会打印如下内容，并将该数据同步到ES。
2022-04-20 11:09:21.521 [pool-2-thread-1] DEBUG c.a.o.canal.client.adapter.es.core.service.ESSyncService - DML: {"data":[{"id":"f4758815a07447c780f0ff5436425236","type":"1","title":"系统登录","create_by":"1","create_date":1650419745000,"remote_addr":"127.0.0.1","user_agent":"ua","request_uri":"/admin/login","method":"GET","params":"params","exception":"exception"}],"database":"test","destination":"example","es":1650419741000,"groupId":"g1","isDdl":false,"old":null,"pkNames":["id"],"sql":"","table":"sys_log","ts":1650424161056,"type":"INSERT"}
Affected indexes: sys_log
常见问题
Illegal character in scheme name at index 0: 127.0.0.1:9200
使用rest模式配置ES连接时，hosts没有加 http:// 前缀
com.alibaba.druid.pool.DruidDataSource cannot be cast to com.alibaba.druid.pool.DruidDataSource
解决方式一：其他版本文件替换
去官网下载canal-1.1.5-alpha2版本的 adapter 包，解压出plugin包下的client-adapter.es7x-1.1.5-SNAPSHOT-jar-with-dependencies.jar替换掉服务器上的canal-1.1.5版本的adapter包下plugin里的client-adapter.es7x-1.1.5-jar-with-dependencies.jar文件。
解决方式二：修改源码打包替换
去官网下载canal-1.1.5版本的source code包，导入到本地编辑器（IDEA），找到client-adpter包下的escore模块，注释掉pom文件中druid依赖。
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
</dependency>
将整个client-adapter重新mvn install，构建过程中出现哪个模块无法依赖则去单独构建那个模块。
将打包好的client-adapter.es7x-1.1.5-jar-with-dependencies.jar替换掉服务器上adapter包下plugin里的同名文件。
日志能正常打印，但是无法同步到ES
canalAdapters:
- instance: example # canal instance Name or mq topic name
groups:
- groupId: g1
outerAdapters:
- name: logger # 输出到日志
当只用logger的时候，日志能打印出DML信息；当不使用logger，只使用es7时什么都不打印。
这种情况大概率是由于sql里的字段，和ES索引无法对应上。可能是数量未对应，可能是字段的类型未对。如果数量和类型都是一致的，那么只能把源码拉到本地来DEBUG测试了。
我遇到的情况就是数量和类型都是一致的，最后把源码拉到本地来DEBUG，最后确定是date类型的问题。下面是create_date在mysql和ES中类型。
mysql
`create_date` datetime DEFAULT NULL COMMENT '创建时间'
ES index
"createDate": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
}
跟踪源码发现在escore模块下，有个类ESSyncUtil控制着mysql类型到ES类型的转换。
/**
* 类型转换为Mapping中对应的类型
*/
public static Object typeConvert(Object val, String esType) {
// 省略部分代码
switch (esType) {
case "date":
if (val instanceof java.sql.Time) {
DateTime dateTime = new DateTime(((java.sql.Time) val).getTime());
if (dateTime.getMillisOfSecond() != 0) {
res = dateTime.toString("HH:mm:ss.SSS");
} else {
res = dateTime.toString("HH:mm:ss");
}
} else if (val instanceof java.sql.Timestamp) {
DateTime dateTime = new DateTime(((java.sql.Timestamp) val).getTime());
if (dateTime.getMillisOfSecond() != 0) {
res = dateTime.toString("yyyy-MM-dd'T'HH:mm:ss.SSS" + Util.timeZone);
} else {
res = dateTime.toString("yyyy-MM-dd'T'HH:mm:ss" + Util.timeZone);
}
} else if (val instanceof java.sql.Date || val instanceof Date) {
DateTime dateTime;
if (val instanceof java.sql.Date) {
dateTime = new DateTime(((java.sql.Date) val).getTime());
} else {
dateTime = new DateTime(((Date) val).getTime());
}
if (dateTime.getHourOfDay() == 0 && dateTime.getMinuteOfHour() == 0 && dateTime.getSecondOfMinute() == 0
&& dateTime.getMillisOfSecond() == 0) {
res = dateTime.toString("yyyy-MM-dd");
} else {
if (dateTime.getMillisOfSecond() != 0) {
res = dateTime.toString("yyyy-MM-dd'T'HH:mm:ss.SSS" + Util.timeZone);
} else {
res = dateTime.toString("yyyy-MM-dd'T'HH:mm:ss" + Util.timeZone);
}
}
} else if (val instanceof Long) {
DateTime dateTime = new DateTime(((Long) val).longValue());
if (dateTime.getHourOfDay() == 0 && dateTime.getMinuteOfHour() == 0 && dateTime.getSecondOfMinute() == 0
&& dateTime.getMillisOfSecond() == 0) {
res = dateTime.toString("yyyy-MM-dd");
} else if (dateTime.getMillisOfSecond() != 0) {
res = dateTime.toString("yyyy-MM-dd'T'HH:mm:ss.SSS" + Util.timeZone);
} else {
res = dateTime.toString("yyyy-MM-dd'T'HH:mm:ss" + Util.timeZone);
}
} else if (val instanceof String) {
String v = ((String) val).trim();
if (v.length() > 18 && v.charAt(4) == '-' && v.charAt(7) == '-' && v.charAt(10) == ' '
&& v.charAt(13) == ':' && v.charAt(16) == ':') {
String dt = v.substring(0, 10) + "T" + v.substring(11);
Date date = Util.parseDate(dt);
if (date != null) {
DateTime dateTime = new DateTime(date);
if (dateTime.getMillisOfSecond() != 0) {
res = dateTime.toString("yyyy-MM-dd'T'HH:mm:ss.SSS" + Util.timeZone);
} else {
res = dateTime.toString("yyyy-MM-dd'T'HH:mm:ss" + Util.timeZone);
}
}
} else if (v.length() == 10 && v.charAt(4) == '-' && v.charAt(7) == '-') {
Date date = Util.parseDate(v);
if (date != null) {
DateTime dateTime = new DateTime(date);
res = dateTime.toString("yyyy-MM-dd");
}
}
}
break;
}
}
可以看到带完整时间格式的，最后都转换成了"yyyy-MM-dd'T'HH:mm:ss" + Util.timeZone。这和我在ES索引中配置的yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis对应不上。
解决方式一（未尝试）：修改索引date类型的format
添加 strict_date_option_time
strict_date_option_time||yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis
或者直接删除format，当不指定format时，默认是strict_date_option_time||epoch_millis。
解决方式二：修改源码打包
将完整时间格式全部设置为yyyy-MM-dd HH:mm:ss
/**
* 类型转换为Mapping中对应的类型
*/
public static Object typeConvert(Object val, String esType) {
// 省略部分代码
switch (esType) {
case "date":
if (val instanceof java.sql.Time) {
DateTime dateTime = new DateTime(((java.sql.Time) val).getTime());
if (dateTime.getMillisOfSecond() != 0) {
res = dateTime.toString("HH:mm:ss.SSS");
} else {
res = dateTime.toString("HH:mm:ss");
}
} else if (val instanceof java.sql.Timestamp) {
DateTime dateTime = new DateTime(((java.sql.Timestamp) val).getTime());
res = dateTime.toString("yyyy-MM-dd HH:mm:ss");
} else if (val instanceof java.sql.Date || val instanceof Date) {
DateTime dateTime;
if (val instanceof java.sql.Date) {
dateTime = new DateTime(((java.sql.Date) val).getTime());
} else {
dateTime = new DateTime(((Date) val).getTime());
}
if (dateTime.getHourOfDay() == 0 && dateTime.getMinuteOfHour() == 0 && dateTime.getSecondOfMinute() == 0
&& dateTime.getMillisOfSecond() == 0) {
res = dateTime.toString("yyyy-MM-dd");
} else {
res = dateTime.toString("yyyy-MM-dd HH:mm:ss");
}
} else if (val instanceof Long) {
DateTime dateTime = new DateTime(((Long) val).longValue());
if (dateTime.getHourOfDay() == 0 && dateTime.getMinuteOfHour() == 0 && dateTime.getSecondOfMinute() == 0
&& dateTime.getMillisOfSecond() == 0) {
res = dateTime.toString("yyyy-MM-dd");
} else {
res = dateTime.toString("yyyy-MM-dd HH:mm:ss");
}
} else if (val instanceof String) {
String v = ((String) val).trim();
if (v.length() > 18 && v.charAt(4) == '-' && v.charAt(7) == '-' && v.charAt(10) == ' '
&& v.charAt(13) == ':' && v.charAt(16) == ':') {
String dt = v.substring(0, 10) + "T" + v.substring(11);
Date date = Util.parseDate(dt);
if (date != null) {
DateTime dateTime = new DateTime(date);
res = dateTime.toString("yyyy-MM-dd HH:mm:ss");
}
} else if (v.length() == 10 && v.charAt(4) == '-' && v.charAt(7) == '-') {
Date date = Util.parseDate(v);
if (date != null) {
DateTime dateTime = new DateTime(date);
res = dateTime.toString("yyyy-MM-dd");
}
}
}
break;
}
}
修改完成之后将整个client-adapter重新mvn install，构建过程中出现哪个模块无法依赖则去单独构建那个模块。
将打包好的client-adapter.es7x-1.1.5-jar-with-dependencies.jar替换掉服务器上adapter包下plugin里的同名文件。
324
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
