canal同步mysql至elasticsearch中

环境准备

1. zookeeper集群
2. elasticsearch-6.8.6
3. canal admin-1.1.4
4. canal deployer-1.1.4
5. canal adapter-1.1.4

关于elasticsearch版本要求具体可自行百度，另需准备postman创建es索引，获取canal网址:https://github.com/alibaba/canal/

搭建zookeeper集群

在这里直接使用docker-compose创建

version: '3.1'

services:
  zoo1:
    image: zookeeper
    restart: always
    hostname: zoo1
    ports:
      - 2181:2181
    environment:
      ZOO_MY_ID: 1
      ZOO_SERVERS: server.1=0.0.0.0:2888:3888;2181 server.2=zoo2:2888:3888;2181 server.3=zoo3:2888:3888;2181
    volumes:
      - /:/opt/compose-volumes/zk-volumes/zoo1
  zoo2:
    image: zookeeper
    restart: always
    hostname: zoo2
    ports:
      - 2182:2181
    environment:
      ZOO_MY_ID: 2
      ZOO_SERVERS: server.1=zoo1:2888:3888;2181 server.2=0.0.0.0:2888:3888;2181 server.3=zoo3:2888:3888;2181
    volumes:
      - /:/opt/compose-volumes/zk-volumes/zoo2
  zoo3:
    image: zookeeper
    restart: always
    hostname: zoo3
    ports:
      - 2183:2181
    environment:
      ZOO_MY_ID: 3
      ZOO_SERVERS: server.1=zoo1:2888:3888;2181 server.2=zoo2:2888:3888;2181 server.3=0.0.0.0:2888:3888;2181
    volumes:
      - /:/opt/compose-volumes/zk-volumes/zoo3

注意创建好挂载所需目录

elasticsearch搭建过程就不介绍了，自行百度即可，搭建简单

创建测试数据库

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `address` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

创建es test索引

{
    "mappings":{
        "_doc":{
            "properties":{
                "name":{
                    "type":"text"
                },
                "address":{
                    "type":"text"
                }
            }
        }
    }
}

搭建canal服务

创建目录

mkdir /opt/software/canal/{admin,adapter,deployer} -p

搭建canal-admin

修改配置文件

cd /opt/software/canal/admin/
vim conf/application.yml

server:
  port: 8089
spring:
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8

spring.datasource:
  address: x.x.x.x:3306  ##数据库地址
  database: canal_manager       ##数据库名称
  username: root                ##登录数据库用户
  password: 123456         ##用户密码
  driver-class-name: com.mysql.jdbc.Driver
  url: jdbc:mysql://${spring.datasource.address}/${spring.datasource.database}?useUnicode=true&characterEncoding=UTF-8&useSSL=false
  hikari:
    maximum-pool-size: 30
    minimum-idle: 1

canal:
  adminUser: admin
  adminPasswd: admin

初始化数据库

mysql -uroot -p 
mysql > source conf/canal_manager.sql

启动canal-admin

sh bin/startup.sh

访问ip+8089,账号:admin，密码:123456

搭建canal-deployer

修改canal_local.properties文件

cd /opt/software/canal/deployer/
vim conf/canal_local.properties

# register ip
canal.register.ip = 10.0.0.200

# canal admin config
canal.admin.manager = 10.0.0.200:8089  #canal-admin地址
canal.admin.port = 11110
canal.admin.user = admin
canal.admin.passwd = 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9  #canal-adminUI的加密登录密码
# admin auto register
canal.admin.register.auto = true
canal.admin.register.cluster =  #集群名字

• 查看canal-admin登录的加密密码方式

> select password(123456);

修改instance.properties文件

vim conf/example/instance.properties

canal.instance.master.address=       #数据库地址
canal.instance.dbUsername=root
canal.instance.dbPassword=123456

启动canal-deployer

sh bin/startup.sh

查看日志

tail -f logs/canl/canal.log
2020-02-03 16:25:46.838 [main] INFO  com.alibaba.otter.canal.deployer.CanalLauncher - ## set default uncaught exception handler
2020-02-03 16:25:47.192 [main] INFO  com.alibaba.otter.canal.deployer.CanalLauncher - ## load canal configurations
2020-02-03 16:25:47.398 [main] INFO  com.alibaba.otter.canal.deployer.CanalStarter - ## start the canal server.
2020-02-03 16:25:49.941 [main] INFO  com.alibaba.otter.canal.deployer.CanalController - ## start the canal server[172.17.0.1(10.0.0.200):11111]
2020-02-03 16:25:58.712 [main] INFO  com.alibaba.otter.canal.deployer.CanalStarter - ## the canal server is running now ......

搭建adapter

修改 application.yml

cd /opt/software/canal/adapter/
vim conf/application.yml

server:
  port: 8081
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: 10.0.0.200:11111
  zookeeperHosts: 10.0.0.200:2181,10.0.0.200:2182,10.0.0.200:2183
#  mqServers: 127.0.0.1:9092 #or rocketmq
#  flatMessage: true
  batchSize: 500
  syncBatchSize: 1000
  retries: 0
  timeout:
  accessKey:
  secretKey:
  srcDataSources:
    defaultDS:
      url: jdbc:mysql://10.0.0.200:3307/test?useUnicode=true
      username: root
      password: 123456
  canalAdapters:
  - instance: example # canal instance Name or mq topic name
    groups:
    - groupId: g1
      outerAdapters:
      - name: logger
      - name: es
        hosts: 10.0.0.200:9300 # 127.0.0.1:9200 for rest mode
        properties:
          cluster.name: pro-logs

关于zookeeper用处是搭建canal-adapter高可用集群，注意cluster.naem需要与es中的集群名称一致

创建es查询条件

vim conf/es/test.yml

dataSourceKey: defaultDS
destination: example
groupId:
esMapping:
  _index: test
  _type: _doc
  _id: _id
  upsert: true
  sql: "select a.id as _id,a.name,a.address from test a"
  commitBatch: 3000

启动adapter

sh bin/startup.sh

测试

在数据库中插入数据，查看es-head即可