canal同步mysql数据到es

悠悠 2023-02-17 01:15 59阅读 0赞

## canal同步mysql数据到es ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70]  
更多请看：https://github.com/alibaba/canal/wiki

#### 1.环境 ####

centos7  
jdk1.8  
ElasticSearch：6.x(我的是6.7.1)  
Kibana：6.x  
canal.deployer：1.1.4（不支持es 7版）  
canal.adapter：1.1.4

下载地址： https://github.com/alibaba/canal/releases  
说明：这里需要下载的是：  
1、canal.deployer1.1.4版本- - - 可以理解为相当于canal的服务端  
2、canal.adapter1.1.4版本- - - 可以理解为相当于canal的插件  
3、最新的是1.1.5版本，但是是快照版，我们这里还是选择稳定版本  
es和kibana的安装:https://blog.csdn.net/qq\_40198004/article/details/89194129

## mysql的配置 ##

开启MySQL的 binlog 写入功能，配置 binlog-format 为 ROW 模式  
找到my.cnf文件，我的是/etc/my.cnf，添加以下配置：

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

![在这里插入图片描述][20200610181603977.png]  
重启mysql：service mysql restart  
查看是否开启 binlog： `show variables like '%log_bin%';`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70 1]  
**准备：es\_db数据库**

CREATE DATABASE es_db;

**创建tb\_item表**

CREATE TABLE `tb_item` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '商品id，同时也是商品编号',
      `title` varchar(100) NOT NULL COMMENT '商品标题',
      `sell_point` varchar(500) DEFAULT NULL COMMENT '商品卖点',
      `price` decimal(20,2) NOT NULL COMMENT '商品价格，单位为：分',
      `num` int(10) NOT NULL COMMENT '库存数量',
      `brand` varchar(255) DEFAULT NULL COMMENT '品牌',
      `barcode` varchar(30) DEFAULT NULL COMMENT '商品条形码',
      `image` varchar(500) DEFAULT NULL COMMENT '商品图片',
      `cid` bigint(10) NOT NULL COMMENT '所属类目，叶子类目',
      `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '商品状态，1-正常，2-下架，3-删除',
      `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `updated` timestamp NULL DEFAULT NULL COMMENT '更新时间',
      PRIMARY KEY (`id`),
      KEY `cid` (`cid`),
      KEY `status` (`status`),
      KEY `updated` (`updated`),
      KEY `title` (`title`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='商品表';

### canal.deployer修改配置 ###

打开canal.deployer-1.1.4/conf/example/instance.properties修改

## mysql serverId , v1.0.26+ will autoGen
    # 这个东西可以不设置，如果要设置别和上面mysql配置文件中的值重复就行
    # canal.instance.mysql.slaveId=0
    
    # enable gtid use true/false
    canal.instance.gtidon=false
    
    # position info
    #mysql启动的ip:端口 #例如：192.168.34.66:3306
    canal.instance.master.address=192.168.221.132:3306
    
    ## 这个可以根据需要修改过滤，默认是直接监听所有
    canal.instance.filter.regex=.*\\..*
    
    # username/password 自己的用户名和密码(也可以是root)
    canal.instance.dbUsername=canal
    canal.instance.dbPassword=canal
    canal.instance.connectionCharset = UTF-8

启动`./bin/startup.sh`  
查看日志：cat logs/canal/canal.log

### canal.adapter修改配置 ###

**1.修改adapter-1.1.4/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
    ## 修改成自己canal-deployer启动的ip和端口 默认端口是：11111
      canalServerHost: 192.168.221.132:11111
    # zookeeperHosts: slave1:2181
    # mqServers: 127.0.0.1:9092 #or rocketmq
    # flatMessage: true
      batchSize: 500
      syncBatchSize: 1000
      retries: 0
      timeout:
      accessKey:
      secretKey:
      srcDataSources:
        defaultDS:
          url: jdbc:mysql://192.168.221.132:3306/es_db?useUnicode=true # mysql的地址和端口
          username: canal
          password: canal
      canalAdapters:
      - instance: example # canal instance Name or mq topic name
        groups:
        - groupId: g1
          outerAdapters:
          - name: logger
    # - name: rdb
    # key: mysql1
    # properties:
    # jdbc.driverClassName: com.mysql.jdbc.Driver
    # jdbc.url: jdbc:mysql://127.0.0.1:3306/mytest2?useUnicode=true
    # jdbc.username: root
    # jdbc.password: 121212
    # - name: rdb
    # key: oracle1
    # properties:
    # jdbc.driverClassName: oracle.jdbc.OracleDriver
    # jdbc.url: jdbc:oracle:thin:@localhost:49161:XE
    # jdbc.username: mytest
    # jdbc.password: m121212
    # - name: rdb
    # key: postgres1
    # properties:
    # jdbc.driverClassName: org.postgresql.Driver
    # jdbc.url: jdbc:postgresql://localhost:5432/postgres
    # jdbc.username: postgres
    # jdbc.password: 121212
    # threads: 1
    # commitSize: 3000
    # - name: hbase
    # properties:
    # hbase.zookeeper.quorum: 127.0.0.1
    # hbase.zookeeper.property.clientPort: 2181
    # zookeeper.znode.parent: /hbase
          - name: es
            hosts: 192.168.221.132:9300 # 127.0.0.1:9200 for rest mode
            properties:
    # mode: transport # or rest
    # # security.auth: test:123456 # only used for rest mode
              cluster.name: my-application

**注意和es中配置对应**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70 2]  
**2.进入到adapter-1.1.4/conf/es文件中**  
cp customer.yml canal.yml  
![在这里插入图片描述][2020061017092083.png]

dataSourceKey: defaultDS
    destination: example
    groupId: g1
    esMapping:
      _index: item
      _type: _doc
      _id: id
      relations:
        customer_order:
          name: customer
      sql: "select t.id,t.title,t.sell_point,t.price,t.num,t.brand,t.barcode,t.image,t.cid,t.status,t.created,t.updated from tb_item t"
      etlCondition: "where t.c_time>={}"
      commitBatch: 3000

mappings不要配  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70 3]  
**3.然后在kibana创建索引和映射**  
**注意**  
6.x是有type的且必须都对应

PUT /item
    { 
        "mappings": { 
            "_doc": { 
                "properties": { 
                    "id": { 
                        "type": "long"
                    },
                    "title": { 
                        "type": "text"
                    },
                    "sell_point": { 
                        "type": "text"
                    },
                    "price": { 
                        "type": "text"
                    },
                    "num": { 
                        "type": "integer"
                    },
                    "brand": { 
                        "type": "text"
                    },
                    "barcode": { 
                        "type": "text"
                    },
    				"image": { 
                        "type": "text"
                    },
    				"cid": { 
                        "type": "long"
                    },
    				"status": { 
                        "type": "short"
                    },
    				"created": { 
                        "type": "date"
                    },
    				"updated": { 
                        "type": "date"
                    }
                }
            }
        }
    }

4.启动：./bin/startup.sh  
查看日志：cat logs/adapter/adapter.log  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70 4]  
adapter-1.1.4/conf/es的yml  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70 5]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70 6]  
4.插入es\_db数据库的tb\_item表

INSERT INTO `tb_item` VALUES ('3', '三星S10', '下单即送10400毫安移动电源！再赠手机魔法盒！', '7000.00', '10', '三星', null, 'http://image.jt.com/jd/c1775819c7e44b1c903f27514e70b998.jpg', '3', '1', '2015-03-08 21:29:27', '2015-03-08 21:29:27');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70 7]  
5.更新数据

UPDATE tb_item set title='华为P40',brand='华为' where id=3;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70 8]  
增删改是可以的,删除自己试试吧  
参考：  
https://blog.csdn.net/tuesdayma/article/details/103294448  
https://segmentfault.com/a/1190000019066098?utm\_source=tag-newest  
https://www.cnblogs.com/dalaoyang/p/11069850.html  
https://www.cnblogs.com/caoweixiong/p/11825303.html

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200610183338266.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0,size_16,color_FFFFFF,t_70
[20200610181603977.png]: https://img-blog.csdnimg.cn/20200610181603977.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200610181857136.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2020061017040811.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0,size_16,color_FFFFFF,t_70
[2020061017092083.png]: https://img-blog.csdnimg.cn/2020061017092083.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200610171155118.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200610164551714.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200610164658174.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200610164611770.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200610182400486.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200610182722699.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0,size_16,color_FFFFFF,t_70