技術文章 » Canal-監聽資料庫表的變化

Canal-監聽資料庫表的變化

2022-06-15 06:00:33

1. 簡介

Canal是阿里巴巴旗下的一款開源專案，純Java開發。基於資料庫增量紀錄檔解析，提供增量資料訂閱&消費功能。

工作原理

Mysql主備複製原理

  • MySQL master 將資料變更寫入二進位制紀錄檔( binary log, 其中記錄叫做二進位制紀錄檔事件binary log events，可以通過 show binlog events 進行檢視)
  • MySQL slave 將 master 的 binary log events 拷貝到它的中繼紀錄檔(relay log)
  • MySQL slave 重放 relay log 中事件，將資料變更反映它自己的資料

canal工作原理

  • canal 模擬 MySQL slave 的互動協定，偽裝自己為 MySQL slave ，向 MySQL master 傳送 dump 協定
  • MySQL master 收到 dump 請求，開始推播 binary log 給 slave (即 canal )
  • canal 解析 binary log 物件(原始為 byte 流)

mysql的binlog

它記錄了所有的DDL和DML(除了資料查詢語句)語句，以事件形式記錄，還包含語句所執行的消耗的時間。主要用來備份和資料同步。

binlog有三種模式：STATEMENT、ROW、MIXED

  1. STATEMENT 記錄的是執行的sql語句
  2. ROW 記錄的是真實的行資料記錄
  3. MIXED 記錄的是1+2，優先按照1的模式記錄

比如：

update user set age = 33

對應STATEMENT模式只是記錄了當前執行的sql，而對應ROW模式則有可能有成千上萬條記錄（當然這取決於你user表的記錄數）

2. 可以幹什麼

基於紀錄檔增量訂閱和消費的業務包括

  • 資料庫映象
  • 資料庫實時備份
  • 索引構建和實時維護(拆分異構索引、倒排索引等)
  • 業務 cache 重新整理
  • 帶業務邏輯的增量資料處理

當前的 canal 支援源端 MySQL 版本包括 5.1.x , 5.5.x , 5.6.x , 5.7.x , 8.0.x

3. 安裝

原生安裝請參考：https://github.com/alibaba/canal/wiki/QuickStart

3.1 docker-compose安裝

3.1.1 建立同步使用者

如果想檢視mysql server的相關設定可以參考 https://www.cnblogs.com/ludangxin/p/16358928.html 中的master設定

CREATE USER canal IDENTIFIED BY 'canal';  
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
-- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;
FLUSH PRIVILEGES;

3.1.2 修改組態檔

首先啟動一個零時的容器用於 將容器中的組態檔資訊copy到宿主機

# run 零時容器
docker run --name canal-temp -d --rm canal/canal-server:v1.1.6
# 執行copy操作  copy組態檔到當前目錄中
docker cp canal-temp:/home/admin/canal-server/conf ./canal-server/conf

canal-server/conf組態檔目錄結構如下


canal-server/conf
├── canal.properties # canal server 的組態檔引數資訊 例如：服務的埠/叢集引數/server 模式（# tcp, kafka, rocketMQ, rabbitMQ, pulsarMQ）等
├── canal_local.properties
├── example # canal 範例相關設定資訊，如果想要監聽多個mysql server 可以copy此檔案進行設定，當然也要在canal.properties的 canal.destinations 中新增對應的資料夾名稱
│   ├── h2.mv.db
│   ├── instance.properties
│   └── meta.dat
├── logback.xml
...

修改組態檔資訊

canal.properties我們使用預設的設定資訊 即：canal.serverMode = tcp

example/instance.properties中設定mysql server連線資訊 如下：

# position info
# mysql url 我這裡直接使用的是 mysql容器name
canal.instance.master.address=my_mysql:3306
# 監聽的binlog 檔名稱 例：mysql-bin.000007
canal.instance.master.journal.name=
# 紀錄檔檔案的Offset
canal.instance.master.position=

# username/password
canal.instance.dbUsername=canal
canal.instance.dbPassword=canal
canal.instance.connectionCharset = UTF-8

# table regex
# 預設設定是同步所有的庫和表
#canal.instance.filter.regex=.*\\..*
# 設定只監聽test庫的user表，如果需要讀取多個表可以使用正規表示式或者用逗號隔開
canal.instance.filter.regex=test.user

mysql 資料解析關注的表，Perl正規表示式.
多個正則之間以逗號(,)分隔，跳脫符需要雙斜槓()
常見例子：
所有表：.* or .\…
canal schema下所有表： canal\…*
canal下的以canal打頭的表：canal.canal.*
canal schema下的一張表：canal.test1
多個規則組合使用：canal\…*,mysql.test1,mysql.test2 (逗號分隔)
注意：此過濾條件只針對row模式的資料有效(ps. mixed/statement因為不解
析sql，所以無法準確提取tableName進行過濾)

3.1.3 啟動canal

docker-compose.yaml 如下

因為canal需要讀取mysql server的bin-log所以需要設定加入到mysql server的網路中去

version: '3'   
services:
    canal:
        image: canal/canal-server:v1.1.6
        hostname: canal
        container_name: canal
        restart: "no"
        ports:
            - "11111:11111"
        volumes:
            - "./canal-server/conf:/home/admin/canal-server/conf"
            - "./canal-server/logs:/home/admin/canal-server/logs"
        networks:
            - mysql_mysql 
networks:
  mysql_mysql:
    external: true

4. springboot 測試

tips：可參考 https://github.com/NormanGyllenhaal/canal-client

4.1 新增依賴

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

<!-- https://mvnrepository.com/artifact/top.javatool/canal-spring-boot-starter -->
<dependency>
    <groupId>top.javatool</groupId>
    <artifactId>canal-spring-boot-starter</artifactId>
    <version>1.2.1-RELEASE</version>
</dependency>

4.2 新增設定

canal:
  server: localhost:11111
  destination: example

logging:
  level:
    top.javatool.canal.client.client: OFF

4.3 監聽canal資料

package com.ldx.canaldemo.handler;

import com.ldx.canaldemo.domain.User;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import top.javatool.canal.client.annotation.CanalTable;
import top.javatool.canal.client.handler.EntryHandler;

@Slf4j
@Component
// 監聽user表
@CanalTable(value = "user")
public class UserHandler implements EntryHandler<User> {

    @Override
    public void insert(User user) {
        log.info("insert info {}", user);
    }

    @Override
    public void update(User before, User after) {
        log.info("update before {} ", before);
        log.info("update after {}", after);
    }

    @Override
    public void delete(User user) {
        log.info("delete {}", user);
    }
}

package com.ldx.canaldemo.domain;

import lombok.Data;

import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;

@Data
@Table(name = "user")
public class User implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    @Column(name = "username")
    private String username;

    @Column(name = "password")
    private String password;

    @Column(name = "sex")
    private Integer sex;
}

4.4 測試

user表資訊如下

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `username` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(1) DEFAULT NULL,
  `sex` int(1) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

測試新增

INSERT INTO `test`.`user`(`id`, `username`, `password`, `age`, `sex`) VALUES (5, 'zhangtieniu', '123456', 28, 1);

檢視控制檯輸出如下

2022-06-14 13:50:38.144  INFO 71053 --- [xecute-thread-1] com.ldx.canaldemo.handler.UserHandler    : insert info User(id=5, username=zhangtieniu, password=123456, age=28, sex=1)

測試修改

UPDATE `test`.`user` SET `username` = 'zhangsan', age = 23 WHERE id = 5;

檢視控制檯輸出如下

2022-06-14 13:54:55.997  INFO 71053 --- [xecute-thread-2] com.ldx.canaldemo.handler.UserHandler    : update before User(id=null, username=zhangtieniu, password=null, age=28, sex=null) 
2022-06-14 13:54:55.997  INFO 71053 --- [xecute-thread-2] com.ldx.canaldemo.handler.UserHandler    : update after User(id=5, username=zhangsan, password=123456, age=23, sex=1)

測試刪除

DELETE FROM `test`.`user` WHERE id = 5;

檢視控制檯輸出如下

2022-06-14 13:56:46.359  INFO 71053 --- [xecute-thread-3] com.ldx.canaldemo.handler.UserHandler    : delete User(id=5, username=zhangsan, password=123456, age=23, sex=1)

5. rabbit mq 測試

5.1 修改canal設定

canal.properties 修改如下

# 將serverMode 修改成rabbitMQ
canal.serverMode = rabbitMQ

# 新增rabbitmq 設定資訊
rabbitmq.host = rabbitmq:5672
rabbitmq.virtual.host = /
rabbitmq.exchange = canal.exchange
rabbitmq.username = admin
rabbitmq.password = admin123

example/instance.properties 新增路由規則

canal.mq.topic=canal_routing_key

5.2 springboot consumer

5.2.1 新增依賴

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-amqp</artifactId>
</dependency>

5.2.2 新增設定

spring:
  rabbitmq:
    host: localhost
    port: 5672
    # rabbit 預設的虛擬主機
    virtual-host: /
    # rabbit 使用者名稱密碼
    username: admin
    password: admin123
    listener:
      simple:
        # manual 手動確認
        acknowledge-mode: manual

5.2.3 新增 consumer

package com.ldx.canaldemo.rabbitmq;

import com.rabbitmq.client.Channel;
import lombok.extern.slf4j.Slf4j;
import org.springframework.amqp.core.Message;
import org.springframework.amqp.core.MessageProperties;
import org.springframework.amqp.rabbit.annotation.Exchange;
import org.springframework.amqp.rabbit.annotation.Queue;
import org.springframework.amqp.rabbit.annotation.QueueBinding;
import org.springframework.amqp.rabbit.annotation.RabbitListener;
import org.springframework.stereotype.Component;

import java.io.IOException;

/**
 * @author ludangxin
 * @date 2022/6/14
 */
@Slf4j
@Component
public class RabbitMQConsumer {
   @RabbitListener(bindings =
         {@QueueBinding(
               value = @Queue(value = MqConstant.CANAL_QUEUE, durable = "true"),
               exchange = @Exchange(value = MqConstant.CANAL_EXCHANGE),
               key = MqConstant.CANAL_ROUTING_KEY)
         })
   public void helloRabbitMq(Message message, Channel channel) throws IOException {
      MessageProperties messageProperties = message.getMessageProperties();
      try {
         log.info(message.toString());
         log.info(new String(message.getBody()));
         channel.basicAck(messageProperties.getDeliveryTag(), false);
      } catch(Exception e) {
         // 當前的訊息是否重新投遞的訊息,也就是該訊息是重新回到佇列裡的訊息
         if(messageProperties.getRedelivered()) {
            log.info("訊息已重複處理失敗,拒絕再次接收...");
            // 拒絕訊息Ò
            channel.basicReject(messageProperties.getDeliveryTag(), false);
         }
         else {
            log.info("訊息即將再次返回佇列處理...");
            channel.basicNack(messageProperties.getDeliveryTag(), false, true);
         }
      }
   }
}

package com.ldx.canaldemo.rabbitmq;

/**
 * @author ludangxin
 * @date 2022/6/14
 */
public interface MqConstant {
    String CANAL_EXCHANGE = "canal.exchange";
    String CANAL_QUEUE = "canal_queue";
    String CANAL_ROUTING_KEY = "canal_routing_key";
}

5.3 啟動測試

先啟動專案讓程式自動建立所需mq中的交換機和佇列

測試新增

INSERT INTO `test`.`user`(`id`, `username`, `password`, `age`, `sex`) VALUES (8, 'zhangtieniu', '123456', 28, 1);

檢視控制檯輸出如下

2022-06-14 14:42:04.818  INFO 73549 --- [ntContainer#0-1] c.l.canaldemo.rabbitmq.RabbitMQConsumer  : (Body:'[B@189a76a(byte[414])' MessageProperties [headers={}, contentLength=0, redelivered=false, receivedExchange=canal.exchange, receivedRoutingKey=canal_routing_key, deliveryTag=4, consumerTag=amq.ctag-KXSHZ8D0wMQo7z2_L2LKsg, consumerQueue=canal_queue])
2022-06-14 14:42:04.818  INFO 73549 --- [ntContainer#0-1] c.l.canaldemo.rabbitmq.RabbitMQConsumer  : {"data":[{"id":"8","username":"zhangtieniu","password":"123456","age":"28","sex":"1"}],"database":"test","es":1655188924000,"id":6,"isDdl":false,"mysqlType":{"id":"int(11)","username":"varchar(255)","password":"varchar(255)","age":"int(11)","sex":"varchar(255)"},"old":null,"pkNames":["id"],"sql":"","sqlType":{"id":4,"username":12,"password":12,"age":4,"sex":12},"table":"user","ts":1655188924822,"type":"INSERT"}

測試修改

UPDATE `test`.`user` SET `username` = 'zhangsan', age = 23 WHERE id = 8;

檢視控制檯輸出如下

2022-06-14 14:56:23.471  INFO 73549 --- [ntContainer#0-1] c.l.canaldemo.rabbitmq.RabbitMQConsumer  : (Body:'[B@6a3a1f0(byte[446])' MessageProperties [headers={}, contentLength=0, redelivered=false, receivedExchange=canal.exchange, receivedRoutingKey=canal_routing_key, deliveryTag=5, consumerTag=amq.ctag-KXSHZ8D0wMQo7z2_L2LKsg, consumerQueue=canal_queue])
2022-06-14 14:56:23.471  INFO 73549 --- [ntContainer#0-1] c.l.canaldemo.rabbitmq.RabbitMQConsumer  : {"data":[{"id":"8","username":"zhangsan","password":"123456","age":"23","sex":"1"}],"database":"test","es":1655189783000,"id":7,"isDdl":false,"mysqlType":{"id":"int(11)","username":"varchar(255)","password":"varchar(255)","age":"int(11)","sex":"varchar(255)"},"old":[{"username":"zhangtieniu","age":"28"}],"pkNames":["id"],"sql":"","sqlType":{"id":4,"username":12,"password":12,"age":4,"sex":12},"table":"user","ts":1655189783493,"type":"UPDATE"}

測試刪除

DELETE FROM `test`.`user` WHERE id = 8;

檢視控制檯輸出如下

2022-06-14 14:57:06.407  INFO 73549 --- [ntContainer#0-1] c.l.canaldemo.rabbitmq.RabbitMQConsumer  : (Body:'[B@628caa50(byte[411])' MessageProperties [headers={}, contentLength=0, redelivered=false, receivedExchange=canal.exchange, receivedRoutingKey=canal_routing_key, deliveryTag=6, consumerTag=amq.ctag-KXSHZ8D0wMQo7z2_L2LKsg, consumerQueue=canal_queue])
2022-06-14 14:57:06.408  INFO 73549 --- [ntContainer#0-1] c.l.canaldemo.rabbitmq.RabbitMQConsumer  : {"data":[{"id":"8","username":"zhangsan","password":"123456","age":"23","sex":"1"}],"database":"test","es":1655189826000,"id":8,"isDdl":false,"mysqlType":{"id":"int(11)","username":"varchar(255)","password":"varchar(255)","age":"int(11)","sex":"varchar(255)"},"old":null,"pkNames":["id"],"sql":"","sqlType":{"id":4,"username":12,"password":12,"age":4,"sex":12},"table":"user","ts":1655189826419,"type":"DELETE"}

6. canal admin管理canal

詳情檢視：https://gitee.com/zhengqingya/docker-compose

使用手冊：https://github.com/alibaba/canal/wiki/Canal-Admin-Guide

原生安裝：https://github.com/alibaba/canal/wiki/Canal-Admin-QuickStart

  1. 建立canal admin 資料庫 canal_manager

  2. 執行初始化sqlLiunx/canal/canal_admin/canal_manager.sql

    檔案內容如下：

    CREATE DATABASE /*!32312 IF NOT EXISTS*/ `canal_manager` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */;

USE `canal_manager`;

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for canal_adapter_config
-- ----------------------------
DROP TABLE IF EXISTS `canal_adapter_config`;
CREATE TABLE `canal_adapter_config` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `category` varchar(45) NOT NULL,
  `name` varchar(45) NOT NULL,
  `status` varchar(45) DEFAULT NULL,
  `content` text NOT NULL,
  `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for canal_cluster
-- ----------------------------
DROP TABLE IF EXISTS `canal_cluster`;
CREATE TABLE `canal_cluster` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(63) NOT NULL,
  `zk_hosts` varchar(255) NOT NULL,
  `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for canal_config
-- ----------------------------
DROP TABLE IF EXISTS `canal_config`;
CREATE TABLE `canal_config` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cluster_id` bigint(20) DEFAULT NULL,
  `server_id` bigint(20) DEFAULT NULL,
  `name` varchar(45) NOT NULL,
  `status` varchar(45) DEFAULT NULL,
  `content` text NOT NULL,
  `content_md5` varchar(128) NOT NULL,
  `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sid_UNIQUE` (`server_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for canal_instance_config
-- ----------------------------
DROP TABLE IF EXISTS `canal_instance_config`;
CREATE TABLE `canal_instance_config` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cluster_id` bigint(20) DEFAULT NULL,
  `server_id` bigint(20) DEFAULT NULL,
  `name` varchar(45) NOT NULL,
  `status` varchar(45) DEFAULT NULL,
  `content` text NOT NULL,
  `content_md5` varchar(128) DEFAULT NULL,
  `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for canal_node_server
-- ----------------------------
DROP TABLE IF EXISTS `canal_node_server`;
CREATE TABLE `canal_node_server` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cluster_id` bigint(20) DEFAULT NULL,
  `name` varchar(63) NOT NULL,
  `ip` varchar(63) NOT NULL,
  `admin_port` int(11) DEFAULT NULL,
  `tcp_port` int(11) DEFAULT NULL,
  `metric_port` int(11) DEFAULT NULL,
  `status` varchar(45) DEFAULT NULL,
  `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for canal_user
-- ----------------------------
DROP TABLE IF EXISTS `canal_user`;
CREATE TABLE `canal_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(31) NOT NULL,
  `password` varchar(128) NOT NULL,
  `name` varchar(31) NOT NULL,
  `roles` varchar(31) NOT NULL,
  `introduction` varchar(255) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------
-- Records of canal_user
-- ----------------------------
BEGIN;
INSERT INTO `canal_user` VALUES (1, 'admin', '6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9', 'Canal Manager', 'admin', NULL, NULL, '2019-07-14 00:05:28');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

  3. docker-compose.yaml如下

    version: '3'
services:
  canal_admin:
    image: canal/canal-admin:v1.1.6
    container_name: canal_admin             
    restart: unless-stopped                 
    volumes: 
      - "./canal/canal-admin/logs:/home/admin/canal-admin/logs"
    environment:
      TZ: Asia/Shanghai
      LANG: en_US.UTF-8
      canal.adminUser: admin
      canal.adminPasswd: 123456
      spring.datasource.address: my_mysql:3306
      spring.datasource.database: canal_manager
      spring.datasource.username: root
      spring.datasource.password: 123456
    ports:
      - "8089:8089"
    networks:
      - canal
      - mysql_mysql
  canal_server:
    image: canal/canal-server:v1.1.6
    container_name: canal_server              
    restart: unless-stopped                   
    volumes:                                  
      - "./canal/canal-server/logs:/home/admin/canal-server/logs"
    environment:                              
      TZ: Asia/Shanghai
      LANG: en_US.UTF-8
      canal.admin.manager: canal_admin:8089
      canal.admin.port: 11110
      canal.admin.user: admin
      canal.admin.passwd: 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
    ports:
      - "11110:11110"
      - "11111:11111"
      - "11112:11112"
    depends_on:
      - canal_admin
    links:
      - canal_admin
    networks:
      - canal
      - mysql_mysql
networks:
  canal:
  mysql_mysql:
    external: true

  4. 啟動服務/存取http://localhost:8089/#/canalServer/nodeServers

    登陸 使用者名稱/密碼：admin/123456