Cancal同步MySQL數(shù)據(jù)

參考博客：https://www.cnblogs.com/liuxuebagaomizhe/p/13809317.html

MySQL配置

1. 查看MySQL是否開(kāi)啟binary log記錄功能

   show variables like 'log_bin';

2. 如果是OFF，在my.cnf(mysql配置文件)追加

   log-bin=mysql-bin #添加這一行就ok
   binlog-format=ROW #選擇row模式
   server_id=1 #配置mysql replaction需要定義，不能和canal的slaveId重復(fù)
   

3. 重啟mysql

   docker restart mysql

4. 再次查看MySQL是否開(kāi)啟binary log記錄功能，確保是ON狀態(tài)

   show variables like 'log_bin';

5. 進(jìn)入MySQL容器

   docker exec -it mysql bash

6. 創(chuàng)建canal用戶(hù)

   mysql -uroot -proot
   # 創(chuàng)建賬號(hào)
   CREATE USER canal IDENTIFIED BY 'canal'; 
   # 授予權(quán)限
   GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
   -- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;
   # 刷新并應(yīng)用
   FLUSH PRIVILEGES;
   

docker安裝cancal

1. 拉取鏡像

   docker pull canal/canal-server
   

2. 啟動(dòng)并復(fù)制配置文件

   #啟動(dòng)鏡像 
   docker run --name canal -d canal/canal-server
   #找到文件位置后 exit退出容器 將容器內(nèi)部文件copy到外部
   docker cp canal:/home/admin/canal-server/conf/canal.properties /mydata/canal
   docker cp canal:/home/admin/canal-server/conf/example/instance.properties /mydata/canal
   

3. 修改剛剛復(fù)制的instance文件

   vi /mydata/canal/instance.properties 
   
   ## 找到數(shù)據(jù)庫(kù)信息,按需更改
   canal.instance.master.address=127.0.0.1:3306
   canal.instance.dbUsername=canal
   canal.instance.dbPassword=canal
   ## 按照需要同步的表選擇
   canal.instance.filter.regex=db.table
   

4. 重建一個(gè)容器

   #關(guān)閉容器
   docker stop canal
   
   #移除容器
   docker rm canal
   
   #啟動(dòng)新的 這里-v是將外部的文件掛載到容器內(nèi)部 這樣就不用每次啟動(dòng)都要配置參數(shù)了
   docker run --name canal -p 11111:11111 -d -v /mydata/canal/instance.properties:/home/admin/canal-server/conf/example/instance.properties -v /mydata/canal/canal.properties:/home/admin/canal-server/conf/canal.properties canal/canal-server
   

docker部署canal-adapter

1. 拉取鏡像

   docker pull slpcat/canal-adapter:v1.1.5

2. 創(chuàng)建配置文件

   mkdir -p /mydata/canal-adapter/conf
   touch application.yml
   mkdir -p /mydata/canal-adapter/conf/es7
   cd es7
   touch ceshi.yml
   

3. application.yml內(nèi)容

   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
     flatMessage: true
     zookeeperHosts:
     syncBatchSize: 1000
     retries: 0
     timeout:
     accessKey:
     secretKey:
     consumerProperties:
       # canal tcp consumer
       canal.tcp.server.host: canal-server地址:11111
       canal.tcp.batch.size: 500
       canal.tcp.username:
       canal.tcp.password:
     srcDataSources:
       defaultDS:
         url: jdbc:mysql://數(shù)據(jù)庫(kù)IP:數(shù)據(jù)庫(kù)端口/數(shù)據(jù)庫(kù)名?useUnicode=true
         username: db_username
         password: db_password
     canalAdapters:
     - instance: example # canal instance Name or mq topic name
       groups:
       - groupId: g1
         outerAdapters:
         - name: logger
         - name: es7 # 該版本發(fā)現(xiàn)只能是es7/es6
           hosts: esip地址:9300 # 127.0.0.1:9200 for rest mode
           properties:
             mode: transport # transport # or rest
   

4. ceshi.yml文件,文件內(nèi)容要對(duì)應(yīng)數(shù)據(jù)庫(kù)和esindex

   dataSourceKey: defaultDS
   destination: example
   groupId: g1
   esMapping:
     _index: ceshi
     _id: _id
     _type: _doc
     upsert: true
     #  pk: id
     sql: "SELECT a.id _id from t_ceshi a "
     #  objFields:
     #    _labels: array:;
     etlCondition: "where a.>={}"
     commitBatch: 3000
   

5. 創(chuàng)建并啟動(dòng)容器，掛在剛剛創(chuàng)建的文件夾

   docker run --name canal-adapter -p 8081:8081 -v /mydata/canal-adapter/conf:/opt/canal-adapter/conf  -d slpcat/canal-adapter:v1.1.5
   

6. 查看運(yùn)行情況，不報(bào)錯(cuò)即可

   
   
   image-20210617150629160.png