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

方案说明 canal是阿里云开源的解析binlog组件，同步到ES需要deployer和adapter两部分：
deployer解析MySQL binlog，adapter将数据写入到ES
优点

开源成熟稳定简单易用

缺点

不支持全量数据初始化

部署 【canal实时同步mysql表到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_nullcanal.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" } } } }