1.安装 canal 和 elasticsearch
教程很多,可自行搜索(我这里用的 canal 是 1.1.3 版本,es 则是 7.2的,mysql 用的是 5.7)
2.在 es 中创建 index,mapping,我这里有一个父子维关系,整个有点长
curl -XPUT "172.17.1.9:9200/index_user_v4" -H 'Content-Type: application/json' -d '
{
"mappings": {
"properties": {
"user_parent":{
"type": "join",
"relations": {
"user": ["schoolRel","classRel"]
}
},
"Id" : {"type" :"long"},
"avatarUrl" : {
"type" : "keyword"
},
"userId" : {
"type" : "long"
},
"tableType" : {
"type" : "keyword"
},
"birthDate" : {
"type" : "date"
},
"gender" : {
"type" : "text"
},
"identityNum" : {
"type" : "text"
},
"mobile" : {
"type" : "text"
},
"userCode" : {
"type" : "keyword"
},
"userName" : {
"type" : "text",
"fields" : {
"dim" : {
"type" : "text"
},
"exact" : {
"type" : "keyword"
}
}
},
"userType" : {
"type" : "long"
},
"areaCode" : {
"type" : "keyword"
},
"roleType" : {
"type" : "long"
},
"schoolCode" : {
"type" : "keyword"
},
"schoolId" : {
"type" : "long"
},
"schoolName" : {
"type" : "text"
},
"sno" : {
"type" : "keyword"
},
3.配置 canalserver 的 instance.properties 文件
canal.instance.gtidon=false
canal.instance.master.address=********:3306
canal.instance.master.journal.name=
canal.instance.master.position=
canal.instance.master.timestamp=
canal.instance.master.gtid=
canal.instance.rds.accesskey=
canal.instance.rds.secretkey=
canal.instance.rds.instanceId=
canal.instance.tsdb.enable=true
canal.instance.dbUsername=canal
canal.instance.dbPassword=jztestP6!4nKsM
canal.instance.connectionCharset = UTF-8
canal.instance.enableDruid=false
canal.instance.filter.regex=testcore.t_user,testcore.t_user_school_rel,testcore.t_school_data,testcore.t_user_class_rel,testcore.t_school_dept,testcore.t_dd_area,testcore.t_user_parenthood_rel
#canal.instance.filter.regex=testcore\\..*
canal.instance.filter.black.regex=
canal.mq.topic=example
#canal.mq.dynamicTopic=mytest1.user,mytest2\\..*,.*\\..*
canal.mq.partition=0
4.配置 canal-adapter 相关的配置
application.yml
server:
port: 8081
logging:
level:
org.springframework: INFO
com.alibaba.otter.canal.client.adapter.hbase: DEBUG
com.alibaba.otter.canal.client.adapter.es: INFO
com.alibaba.otter.canal.client.adapter.rdb: DEBUG
spring:
jackson:
date-format: yyyy-MM-dd HH:mm:ss
time-zone: GMT+8
default-property-inclusion: non_null
canal.conf:
mode: tcp # kafka rocketMQ
canalServerHost: 172.18.108.67:11111
batchSize: 500
syncBatchSize: 1000
retries: 0
timeout:
accessKey:
secretKey:
srcDataSources:
defaultDS:
url: jdbc:mysql://*******:3306/testcore?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8
username: canal
password: ######
canalAdapters:
- instance: testcore
groups:
- groupId: g1
outerAdapters:
- name: logger
- name: es
hosts: 127.0.0.1:9300
properties:
cluster.name: my-application
5.conf 目录下写相关的 yml 文件(格式好像挺重要的,我当时因为这个格式问题差点疯了)
t_user.yml
dataSourceKey: defaultDS
destination: testcore
upsert: true
groupId: g1
esMapping:
_index: index_user_v4
_type: _doc
_id: _id
pk: user_id
relations:
user_parent:
name: user
sql: "select concat('user',user_id) as _id,user_id as userId,'user' as tableType,user_name as userName,user_type as userType, mobile,user_code as userCode,avatar_url as avatarUrl, gender,identity_num as identityNum,birth_date as birthDate from testcore.t_user"
t_schoolrel.yml
dataSourceKey: defaultDS
destination: testcore
upsert: true
esMapping:
_index: index_user_v4
_type: _doc
_id: _id
pk: sr.id
relations:
user_parent:
name: schoolRel
parent: sparent
sql: "select concat('school',sr.id) as _id,sr.id as Id,concat('user',sr.user_id) as sparent,sr.user_id as userId,'schoolRel' as tableType,sr.user_id as sk,sr.user_id as parent,sr.school_id as schoolId,sd.school_name as schoolName,sr.role_type as roleType,sr.sno as sno,sd.area_code as areaCode,sd.school_code as schoolCode from t_user_school_rel sr LEFT JOIN t_school_data sd on sr.school_id=sd.school_id "
skips:
- sparent
刚好这两个是父子维关系
配通以后查看日志,大概的就是这样子的图