关于数据库:canal实时同步mysql表到es

计划阐明

canal 是阿里云开源的解析 binlog 组件，同步到 ES 须要 deployer 和 adapter 两局部：
deployer 解析 MySQL binlog，adapter 将数据写入到 ES

长处

 开源成熟稳固
简略易用

毛病

 不反对全量数据初始化

部署

logstash 进行全量
/etc/hosts 配置 es7.test.com 域名解析

deployer

wget https://github.com/alibaba/canal/releases/download/canal-1.1.5/canal.deployer-1.1.5.tar.gz
tar zxf canal.deployer-1.1.5.tar.gz -C deployer
cd deployer
vim conf/shop/instance.properties
#################################################
## mysql serverId , v1.0.26+ will autoGen
# canal.instance.mysql.slaveId=0

# enable gtid use true/false
canal.instance.gtidon=false

# position info
canal.instance.master.address=127.0.0.1:3306
canal.instance.master.journal.name=
canal.instance.master.position=
canal.instance.master.timestamp=
canal.instance.master.gtid=

# table meta tsdb info
canal.instance.tsdb.enable=true

# username/password
canal.instance.dbUsername=root 
canal.instance.dbPassword=123456
canal.instance.connectionCharset = UTF-8
canal.instance.enableDruid=false

# table regex
canal.instance.filter.regex=test.shop
#################################################

# 启动
sh bin/startup.sh
# 敞开
sh bin/stop.sh

adapter

wget https://github.com/alibaba/canal/releases/download/canal-1.1.5/canal.adapter-1.1.5.tar.gz
tar zxf canal.adapter-1.1.5.tar.gz -C adapter
cd 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
  syncBatchSize: 2
  retries: 0
  timeout:
  accessKey:
  secretKey:
  consumerProperties:
    # canal tcp consumer
    canal.tcp.server.host: 127.0.0.1:11111
    canal.tcp.zookeeper.hosts:
    canal.tcp.batch.size: 500
    canal.tcp.username:
    canal.tcp.password:

  srcDataSources:
    defaultDS:
      url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true
      username: root
      password: 123456
  canalAdapters:
  - instance: shop
    groups:
    - groupId: g-shop
      outerAdapters:
      - name: logger
      - name: es7
        hosts: es7.test.com:9200
        properties:
          mode: rest
          security.auth: elastic:123456
          cluster.name: testes

### 表映射
vim conf/es7/shop.yml
dataSourceKey: defaultDS
destination: shop
groupId: g-shop
esMapping:
  _index: canal_shop
  _type: _doc
  _id: _id
  upsert: true
  sql: "select id _id,id,cid,name,remartk from shop"
  commitBatch: 1000

# 启动
sh bin/startup.sh
# 敞开
sh bin/stop.sh

ES 创立索引

PUT /canal_shop
{
    "settings": {
        "number_of_replicas": 0,
        "number_of_shards": 1,
        "index.codec": "best_compression"
    },
    "mappings": {
        "properties" : {
            "id":{"type":"integer"},
            "cid":{"type":"integer"},
            "name":{"type":"text"},
            "remark":{"type":"text"}
            }
    }
}