elasticsearch 搭配 canal 构建主从复制架构整合canal实战(三)

最新推荐文章于 2023-07-26 09:46:15 发布

置顶今天想吃柠檬

最新推荐文章于 2023-07-26 09:46:15 发布

阅读量4.4k

点赞数 1

分类专栏： elasticsearch java 文章标签： elasticsearch 架构分布式

本文链接：https://blog.csdn.net/m0_37723564/article/details/122493129

版权

java 同时被 2 个专栏收录

17 篇文章 0 订阅

订阅专栏

elasticsearch

4 篇文章 0 订阅

订阅专栏

前言

搭建canal

前言

之前我们已经搭建了elasticsearch和elasticsearch-head，整合了spring data elasticsearch，下面就是需要使用canal来将mysql和elasticsearch串联起来。

搭建canal

首先我们到GitHub找到canal的仓库，可以看到canal的工作原理

简单的说，mysql的主从复制是从数据库通过bin log把mysql的操作日志按顺序执行。canal把自己模拟成mysql的slave从数据库实现主从复制。

首先我们需要下载两个东西canal.adapter和canal.deployer，解压到服务器上。

然后开启我们mysql的binlog功能

[mysqld]
log-bin=mysql-bin # 开启 binlog
binlog-format=ROW # 选择 ROW 模式
server_id=1 # 配置 MySQL replaction 需要定义，不要和 canal 的 slaveId 重复

由于我们的canal需要连接到我们的mysql，所以需要分配一个账号给我们的canal

CREATE USER canal IDENTIFIED BY 'canal';  
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
-- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;
FLUSH PRIVILEGES;

然后进入到我们canal.deployer的文件目录下打开conf/example/instance.properties

填写数据库的地址和账号密码，退出保存。

然后进入bin 目录执行startup.sh脚本

执行完后可以查看日志

2021-09-04 16:43:38.523 [main] INFO  com.alibaba.otter.canal.deployer.CanalLauncher - ## set default uncaught exception handler
2021-09-04 16:43:38.558 [main] INFO  com.alibaba.otter.canal.deployer.CanalLauncher - ## load canal configurations
2021-09-04 16:43:38.568 [main] INFO  com.alibaba.otter.canal.deployer.CanalStarter - ## start the canal server.
2021-09-04 16:43:38.606 [main] INFO  com.alibaba.otter.canal.deployer.CanalController - ## start the canal server[172.16.51.49(172.16.51.49):11111]
2021-09-04 16:43:39.812 [main] INFO  com.alibaba.otter.canal.deployer.CanalStarter - ## the canal server is running now ......

出现上述的日志就是启动成功了

下面开始配置canal.adapter，首先进入conf目录，打开application.yml文件，在文件中配置下面这些信息

第一配置连接的数据库，第二配置elasticsearch的地址和账号密码，这样mysql,canal,elasticsearch三者就可以串联起来了。

下面需要配置同步时对应索引的文件，这个也是最关键的一步。首先我们使用的是elasticsearch7.10.0所以我们在config目录下找到es7在里面创建文件。下面开始举例，按照上一篇的条件，我们之前在spring boot elasticsearch中定义了一个父子文档。那么在canal中至少要创建两个文件来实现同步。

dataSourceKey: defaultDS
destination: example
groupId: g1
esMapping:
  _index: forum_post
  _id: _id
  relations:
    post_join:
      name: forum_post
#  upsert: true
#  pk: id
  sql: "select fp.post_id                                       as _id,
               fp.post_id, 
               fp.title,
               fp.title as title_keyword,
               fp.content,
               fp.content as content_keyword,
               fp.content_type,
               fp.plate_id,
               fpl.plate_name_en,
               fpl.plate_name_cn,
               date_format(fp.gmt_created, '%Y-%m-%d %H:%i:%s') as gmt_created,
               fp.created_by,
               fu.username,
               fu.username as username_keyword,
               fu.head_icon,
               fp.read_count,
               fp.reply_count,
               fp.like_count,
               fp.language_type,
               fp.is_top,
               fp.is_archive,
               date_format(fp.gmt_top, '%Y-%m-%d %H:%i:%s')     as gmt_top,
               date_format(fp.gmt_reply, '%Y-%m-%d %H:%i:%s')   as gmt_reply,
               1 as search_type
        from forum_post fp
               left join forum_plate fpl on fp.plate_id = fpl.plate_id
               left join forum_user fu on fu.user_account = fp.created_by"
#  objFields:
#    _labels: array:;
  etlCondition: "where fp.gmt_modified>={}"
  commitBatch: 3000

首先通过_index定义映射的索引名称，通过_id定义映射主键，通过relations定义父子文档，里面的name的值决定是父文档还是子文档。下面的sql返回的字段就是所有之前elasticsearch中所定义的字段。这里要注意的是我们映射的sql虽然支持多表但是需要满足下列的限制

下面展示子文档的例子

dataSourceKey: defaultDS
destination: example
groupId: g1
esMapping:
  _index: forum_post
  _id: _id
  relations:
    post_join:
      name: forum_reply
      parent: post_id
#  upsert: true
#  pk: id
  sql: "select  fr.es_id                                      as _id,
                fr.reply_id,
                fr.content,
                fr.content as content_keyword,
                fr.content_type,
                fr.like_count,
                date_format(fr.gmt_created, '%Y-%m-%d %H:%i:%s') as gmt_created,
                fr.created_by,
                fu.username,
                fu.username as username_keyword,
                fu.head_icon,
                fr.post_id,
                2 as search_type
        from forum_reply fr left join forum_user fu on fu.user_account = fr.created_by"
#  objFields:
#    _labels: array:;
  etlCondition: "where fr.gmt_modified>={}"
  commitBatch: 3000

它的索引和父文档是一致的，在join字段上name 的值是子文档的，同时声明parent，这个是标记通过哪个字段来关联父子文档。

保存后退出，进入bin目录

执行startup.sh

可以在logs/adapter目录下监听日志。

2021-10-28 13:50:44.440 [main] INFO  c.a.o.canal.adapter.launcher.loader.CanalAdapterService - ## syncSwitch refreshed.
2021-10-28 13:50:44.440 [main] INFO  c.a.o.canal.adapter.launcher.loader.CanalAdapterService - ## start the canal client adapters.
2021-10-28 13:50:44.444 [main] INFO  c.a.otter.canal.client.adapter.support.ExtensionLoader - extension classpath dir: /usr/local/src/canal.adapter-1.1.5/plugin
2021-10-28 13:50:44.683 [main] INFO  c.a.o.canal.adapter.launcher.loader.CanalAdapterLoader - Load canal adapter: logger succeed
2021-10-28 13:50:45.196 [main] INFO  c.a.o.c.client.adapter.es.core.config.ESSyncConfigLoader - ## Start loading es mapping config ...
2021-10-28 13:50:45.255 [main] INFO  c.a.o.c.client.adapter.es.core.config.ESSyncConfigLoader - ## ES mapping config loaded
2021-10-28 13:50:45.656 [main] INFO  c.a.o.canal.adapter.launcher.loader.CanalAdapterLoader - Load canal adapter: es7 succeed
2021-10-28 13:50:45.662 [main] INFO  c.alibaba.otter.canal.connector.core.spi.ExtensionLoader - extension classpath dir: /usr/local/src/canal.adapter-1.1.5/plugin
2021-10-28 13:50:45.686 [main] INFO  c.a.o.canal.adapter.launcher.loader.CanalAdapterLoader - Start adapter for canal-client mq topic: example-g1 succeed
2021-10-28 13:50:45.687 [main] INFO  c.a.o.canal.adapter.launcher.loader.CanalAdapterService - ## the canal client adapters are running now ......
2021-10-28 13:50:45.693 [main] INFO  org.apache.coyote.http11.Http11NioProtocol - Starting ProtocolHandler ["http-nio-8081"]
2021-10-28 13:50:45.695 [main] INFO  org.apache.tomcat.util.net.NioSelectorPool - Using a shared selector for servlet write/read
2021-10-28 13:50:45.698 [Thread-4] INFO  c.a.otter.canal.adapter.launcher.loader.AdapterProcessor - =============> Start to connect destination: example <=============
2021-10-28 13:50:45.806 [main] INFO  o.s.boot.web.embedded.tomcat.TomcatWebServer - Tomcat started on port(s): 8081 (http) with context path ''
2021-10-28 13:50:45.809 [main] INFO  c.a.otter.canal.adapter.launcher.CanalAdapterApplication - Started CanalAdapterApplication in 4.909 seconds (JVM running for 5.689)

出现上面的日志说明启动成功。

在spring boot项目中对数据库操作暴露一些curd的http接口，通过请求接口，mysql数据库中的数据发生变化。

可以看到如下的日志输出

2021-10-28 13:50:47.259 [pool-3-thread-1] DEBUG c.a.o.canal.client.adapter.es.core.service.ESSyncService - DML: {"data":[{"user_id":28,"user_account":"015","mobile":"","username":"杨幂杨幂","post_count":18,"freeze_type":0,"gmt_freeze":1631950395991,"gmt_created":1630940953000,"gmt_modified":1634650702000,"created_by":"admin","modified_by":"015","head_icon":"http://121.199.0.246/images/20181231010435_scjtm.png","gmt_last_login":1634650702000}],"database":"rokid_forum","destination":"example","es":1634650702000,"groupId":"g1","isDdl":false,"old":[{"gmt_modified":1634264602000,"gmt_last_login":1632987270000}],"pkNames":["user_id"],"sql":"","table":"forum_user","ts":1635400246555,"type":"UPDATE"}
Affected indexes: forum_post forum_reply forum_post forum_post forum_reply forum_post
2021-10-28 13:50:47.273 [pool-3-thread-1] DEBUG c.a.o.canal.client.adapter.es.core.service.ESSyncService - DML: {"data":[{"post_id":148,"plate_id":13,"title":"迁移发布","language_type":1,"content_type":1,"like_count":0,"reply_count":0,"read_count":0,"content":"<p>迁移
发布</p>","is_top":0,"is_archive":0,"gmt_reply":null,"gmt_top":null,"gmt_created":1634650722010,"gmt_modified":1634650722010,"created_by":"015","modified_by":"015"}],"database":"rokid_forum","destination":"example","es":1634650722000,"groupId":"g1","isDdl":false,"old":null,"pkNames":["post_id"],"sql":"","table":"forum_post","ts":1635400246556,"type":"INSERT"}
Affected indexes: forum_post
2021-10-28 13:50:47.331 [pool-3-thread-1] DEBUG c.a.o.canal.client.adapter.es.core.service.ESSyncService - DML: {"data":[{"user_id":28,"user_account":"015","mobile":"","username":"杨幂杨幂","post_count":19,"freeze_type":0,"gmt_freeze":1631950395991,"gmt_created":1630940953000,"gmt_modified":1634650722000,"created_by":"admin","modified_by":"015","head_icon":"http://121.199.0.246/images/20181231010435_scjtm.png","gmt_last_login":1634650702000}],"database":"rokid_forum","destination":"example","es":1634650722000,"groupId":"g1","isDdl":false,"old":[{"post_count":18,"gmt_modified":1634650702000}],"pkNames":["user_id"],"sql":"","table":"forum_user","ts":1635400246557,"type":"UPDATE"}
Affected indexes: forum_post forum_reply forum_post forum_post forum_reply forum_post
2021-10-28 13:50:47.331 [pool-3-thread-1] DEBUG c.a.o.canal.client.adapter.es.core.service.ESSyncService - DML: {"data":[{"post_id":148,"plate_id":13,"title":"迁移发布","language_type":1,"content_type":1,"like_count":0,"reply_count":0,"read_count":1,"content":"<p>迁移
发布</p>","is_top":0,"is_archive":0,"gmt_reply":null,"gmt_top":null,"gmt_created":1634650722010,"gmt_modified":1634650801931,"created_by":"015","modified_by":"015"}],"database":"rokid_forum","destination":"example","es":1634650801000,"groupId":"g1","isDdl":false,"old":[{"read_count":0,"gmt_modified":1634650722010}],"pkNames":["post_id"],"sql":"","table":"forum_post","ts":1635400246567,"type":"UPDATE"}
Affected indexes: forum_post

可以看到执行的sql影响的index，sql的类型，然后打开elasticsearch-head可以看到elasticsearch的数据已经发生了变化。