canal同步mysql数据至elasticSearch

数据库账号授权

因为canal是模拟mysql的slave去偷取msql的binlog操作日志达到同步的效果，所以需要给账号授权；

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;FLUSH PRIVILEGES;

用户名：root      密码: 123456     指向ip(允许访问的ip): %代表所有Ip,此处也可以输入Ip来指定Ip

开启mysql binlog模式

找到mysql配置文件，windows一般在C:\ProgramData\MySQL\MySQL Server 5.7

Linux一般在/etc/my.cnf；添加如下内容：

log-bin=mysql-bin
binlog-format=ROW
server-id=1

然后重启mysql，查看binlog是否开启：show variables like '%log_bin%'; log_bin为on即开启成功。

配置canaldePloyer

进入conf/example/instance.properties配置文件并更改如下配置

canal.instance.master.address=127.0.0.1:3306
canal.instance.dbUsername=root   （第一步所授权的账号）
canal.instance.dbPassword=123456
canal.instance.defaultDatabaseName=test
canal.instance.filter.regex=test.tzg_course_t,test.tzg_school_t （表过滤：设置要同步的表）

配置完成即可启动；

配置canalAdapter

配置application.yml

进入conf/application.yml;

将defaultDS改为自己的数据库配置

srcDataSources:
    defaultDS:
      url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true
      username: root
      password: 123456

配置自己的es

- name: es
        hosts: 127.0.0.1:9300 #transport端口
        properties:
          cluster.name: tzg  #集群名

配置数据抽取文件

上述步骤配置完成保存后回到上级目录，再进入es目录，创建一个yml配置文件类似如下：

dataSourceKey: defaultDS
destination: example
groupId: g1
esMapping:
  _index: tzg_business.tzg_course  #待同步es index，要提前建好
  _type: _doc  #文档类型
  _id: _id   #文档_id和相同
  upsert: true 
#  pk: id
  sql: "select c.id as _id,c.id as id,s.area_id as areaId,s.area as area,s.district_id as districtId,s.district as district,s.city_id as cityId,s.city      as city,c.category as category,c.category_id as categoryId,c.title as title ,c.school_id as schoolId,c.description as description,
  c.score as score,c.tags as tags,c.cover_img_id as coverImgId,c.recommended as recommended,c.promote as promote,
  c.last_modify_time as lastModifyTime,c.create_time as createTime from tzg_course_t c left join tzg_school_t s on c.school_id= s.id"
#  objFields:
#    _labels: array:;
#  etlCondition: "where a.c_time>='{0}'"
  commitBatch: 3000

配置完成启动canalAdapter即可；增加修改数据库数据看是否同步es；

deployer：

adapter：

转载于:https://my.oschina.net/u/3734816/blog/3081527