使用canal1.1.5让MySQL5.7同步到ES7.x

最新推荐文章于 2023-10-31 20:00:00 发布

好大一个消失点

最新推荐文章于 2023-10-31 20:00:00 发布

阅读量912

点赞数

分类专栏： ElasticSearch MySQL

本文链接：https://blog.csdn.net/weixin_42681513/article/details/114716818

版权

ElasticSearch 同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

MySQL

1 篇文章 0 订阅

订阅专栏

哎！也算是记录自己的踩坑日记吧
注意： MySQL和ES7请自己自行安装好，请大家一定确定自己MySQL的版本。
使用的系统：Centos，Ubuntu。windows请移步

# 这里的版本不能低于5.7， 如果小于5.7的请移步，使用8的也请移步
SELECT VERSION();

好，步入正文

MySQL配置

1.设置MySQL

# 使用已有的账号和密码登录
myql -u root -p
# CREATE USER 用户名 IDENTIFIED BY '密码'; 
CREATE USER canal IDENTIFIED BY 'canal';  

# 赋予权限
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
-- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;

# 刷新权限
FLUSH PRIVILEGES;

2.设置binlog

使用yum安装的，文件位置大概都是这个/etc/my.cnf
所以请 vim /etc/my.cnf

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

！如果相把日志单独放的朋友请一定设置权限，好吧！如
log-bin=/usr/local/mysql/binlogs/mysql-bin # 开启 binlog
chown -R mysql:mysql  /usr/local/mysql/binlogs # 设置权限

mysql> show variables like 'binlog_format%';       #查看binlog模式，如果显示如下，就说明成功了
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.06 sec)

# 创建数据库
create database `fgn-user` character set utf8;

# 创建表
CREATE TABLE `users` (  
	`id` int(11) NOT NULL,
	`age` int(11) NOT NULL,
	`name` varchar(200) NOT NULL,
	`address` varchar(1000) DEFAULT NULL,
	`created_at` datetime DEFAULT NULL,
	`updated_at` datetime DEFAULT NULL,
	`deleted_at` datetime DEFAULT NULL,
	PRIMARY KEY (id)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

部署Canal-deployer服务端

1.下载并解压

wget https://github.com/alibaba/canal/releases/download/canal-1.1.5-alpha-1/canal.deployer-1.1.5-SNAPSHOT.tar.gz
#如果下载不下来，试试看我保存的
wget https://file.hytwfy.top/canal.deployer-1.1.5-SNAPSHOT.tar.gz
mkdir /usr/local/canal-deployer
tar xf canal.deployer-1.1.5-SNAPSHOT.tar.gz -C /usr/local/canal-deployer/

2.修改配置文件

# 如果前面和我设置路径一样的话，就直接复制吧，如果不是请使用自己的
[root@wfy]# vim /usr/local/canal-deployer/conf/example/instance.properties
## mysql serverId , v1.0.26+ will autoGen
canal.instance.mysql.slaveId=3     #修改ID，不能和MySQL数据库一致

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

# position info
canal.instance.master.address=127.0.0.1:3306    #指定mysql数据库地址及端口
canal.instance.master.journal.name=
canal.instance.master.position=
canal.instance.master.timestamp=
canal.instance.master.gtid=

# rds oss binlog
canal.instance.rds.accesskey=
canal.instance.rds.secretkey=
canal.instance.rds.instanceId=

# table meta tsdb info
canal.instance.tsdb.enable=true
#canal.instance.tsdb.url=jdbc:mysql://127.0.0.1:3306/canal_tsdb
#canal.instance.tsdb.dbUsername=canal
#canal.instance.tsdb.dbPassword=canal

#canal.instance.standby.address =
#canal.instance.standby.journal.name =
#canal.instance.standby.position =
#canal.instance.standby.timestamp =
#canal.instance.standby.gtid=

# username/password
canal.instance.dbUsername=canal    #MySQL账号
canal.instance.dbPassword=canal    #MySQL密码
canal.instance.connectionCharset = UTF-8    #字符集格式，需要与mysql保持一致
# enable druid Decrypt database password
canal.instance.enableDruid=false
#canal.instance.pwdPublicKey=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALK4BUxdDltRRE5/zXpVEVPUgunvscYFtEip3pmLlhrWpacX7y7GCMo2/JM6LeHmiiNdH1FWgGCpUfircSwlWKUCAwEAAQ==

# table regex
canal.instance.filter.regex=.*\\..*     #表名监控的正则
# table black regex
canal.instance.filter.black.regex=mysql\\.slave_.*
# table field filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2)
#canal.instance.filter.field=test1.t_product:id/subject/keywords,test2.t_company:id/name/contact/ch
# table field black filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2)
#canal.instance.filter.black.field=test1.t_product:subject/product_image,test2.t_company:id/name/contact/ch

# mq config
canal.mq.topic=example
# dynamic topic route by schema or table regex
#canal.mq.dynamicTopic=mytest1.user,mytest2\\..*,.*\\..*
canal.mq.partition=0
# hash partition config
#canal.mq.partitionsNum=3
#canal.mq.partitionHash=test.table:id^name,.*\\..*
#canal.mq.dynamicTopicPartitionNum=test.*:4,mycanal:6

3.启动canal-deployer
因为canal-depaloyer由java开发，所以需要jdk环境，jdk版本使用1.8

yum install java-1.8.0-openjdk.x86_64 java-1.8.0-openjdk-devel.x86_64 -y
/usr/local/canal-deployer/bin/startup.sh

4.查看日志及端口

[root@wfy]# ss -anplt | grep java
LISTEN     0      50           *:11110                    *:*                   users:(("java",pid=7577,fd=91))
LISTEN     0      50           *:11111                    *:*                   users:(("java",pid=7577,fd=87))
LISTEN     0      3            *:11112                    *:*                   users:(("java",pid=7577,fd=70))
LISTEN     0      100          *:8081                     *:*                   users:(("java",pid=7657,fd=109))

[root@wfy]# tail -f /usr/local/canal-deployer/logs/example/example.log 
2021-03-13 00:47:23.588 [main] INFO  c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [example/instance.properties]
2021-03-13 00:47:23.755 [main] INFO  c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [canal.properties]
2021-03-13 00:47:23.756 [main] INFO  c.a.o.c.i.spring.support.PropertyPlaceholderConfigurer - Loading properties file from class path resource [example/instance.properties]
2021-03-13 00:47:24.121 [main] INFO  c.a.otter.canal.instance.spring.CanalInstanceWithSpring - start CannalInstance for 1-example 
2021-03-13 00:47:24.129 [main] WARN  c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table filter : ^.*\..*$
2021-03-13 00:47:24.129 [main] WARN  c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table black filter : ^mysql\.slave_.*$
2021-03-13 00:47:24.216 [destination = example , address = /127.0.0.1:3306 , EventParser] WARN  c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - ---> begin to find start position, it will be long time for reset or first position
2021-03-13 00:47:24.236 [main] INFO  c.a.otter.canal.instance.core.AbstractCanalInstance - start successful....
2021-03-13 00:47:24.238 [destination = example , address = /127.0.0.1:3306 , EventParser] WARN  c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - prepare to find start position just show master status
2021-03-13 00:47:24.548 [destination = example , address = /127.0.0.1:3306 , EventParser] WARN  c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - ---> find start position successfully, EntryPosition[included=false,journalName=mysql-bin.000001,position=4,serverId=1,gtid=<null>,timestamp=1615567588000] cost : 320ms , the next step is binlog dump

部署Canal-adapter客户端

1.下载并解压

wget https://github.com/alibaba/canal/releases/download/canal-1.1.5-alpha-1/canal.adapter-1.1.5-SNAPSHOT.tar.gz
#如果下载不下来，试试看我保存的
wget https://file.hytwfy.top/canal.adapter-1.1.5-SNAPSHOT.tar.gz
mkdir /usr/local/canal-adapter
tar xf canal.adapter-1.1.5-SNAPSHOT.tar.gz -C /usr/local/canal-adapter/

2.修改application.yml

[root@wfy]# vim /usr/local/canal-adapter/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 #tcp kafka rocketMQ rabbitMQ
  flatMessage: true
  zookeeperHosts:
  syncBatchSize: 1000
  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:
    # kafka consumer
    kafka.bootstrap.servers: 127.0.0.1:9092
    kafka.enable.auto.commit: false
    kafka.auto.commit.interval.ms: 1000
    kafka.auto.offset.reset: latest
    kafka.request.timeout.ms: 40000
    kafka.session.timeout.ms: 30000
    kafka.isolation.level: read_committed
    kafka.max.poll.records: 1000
    # rocketMQ consumer
    rocketmq.namespace:
    rocketmq.namesrv.addr: 127.0.0.1:9876
    rocketmq.batch.size: 1000
    rocketmq.enable.message.trace: false
    rocketmq.customized.trace.topic:
    rocketmq.access.channel:
    rocketmq.subscribe.filter:
    # rabbitMQ consumer
    rabbitmq.host:
    rabbitmq.virtual.host:
    rabbitmq.username:
    rabbitmq.password:
    rabbitmq.resource.ownerId:

  srcDataSources:
    defaultDS:
      url: jdbc:mysql://127.0.0.1:3306/fgn-user?useUnicode=true
      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: es7         #请改成es7
        key: fgnKey		  #请一定要添加key
        hosts: 127.0.0.1:9200 # 127.0.0.1:9200 for rest mode
        properties:
          mode: rest # transport or rest
#          # security.auth: test:123456 #  only used for rest mode
          cluster.name: docker-cluster   #es集群名，按照自己的设置
#        - name: kudu
#          key: kudu
#          properties:
#            kudu.master.address: 127.0.0.1 # ',' split multi address

3.修改适配器映射文件

[root@wfy]#vim /usr/local/canal-adapter/conf/es7/mytest_user.yml

dataSourceKey: defaultDS   #指定在application.yml文件中srcDataSources源数据源自定义的名称
outerAdapterKey: fgnKey    #和上面的配置文件的key一致。
destination: example
groupId: g1
esMapping:
  _index: fgn-article     #指定索引名称
  _id: _id            #指定文档id，_id 此值则由es自动分配文档ID
#  upsert: true
#  pk: id
  sql: "select id as _id, user_id, title, content, issue_time, created_at, updated_at, deleted_at from article_profiles"   # 这里请特别注意，写成这个样子默认的是bug。。。
#  objFields:
#rticle    _labels: array:;
#  etlCondition: "where a.c_time>={}"
  commitBatch: 3000

**4.在ES7创建相对应的mapping

#	建议把索引设置成为和数据库名一样
PUT /fgn-user
{
    "mappings": {
            "properties": {
                "created_at": {
                    "type": "date"
                },
                "deleted_at": {
                    "type": "date"
                }
                "updated_at": {
                    "type": "date"
                },
                "age": {
                    "type": "keyword"
                },
                 "address": {
                    "type": "text"
                }
            }
        }
}

5.启动Canal-adapter并写入数据

/usr/local/canal-adapter/bin/startup.sh

tail -f /usr/local/canal-adapter/logs/adapter/adapter.log
# 如果这个不存在，请回到/usr/local/canal-adapter/bin/查看错误
cd /usr/local/canal-adapter/bin

参考的博客地址