canal数据库同步

一：适用于mysql5.1.x , 5.5.x , 5.6.x , 5.7.x , 8.0.x

二：mysql配置开启binary log

如果是win：则找到mysql目录下的my.ini

如果是Linux：则在找到my.cnf

 配置以下几项：

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

三：为mysql创建用于canal服务连接的账号

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

四：canal服务端配置

下载服务端：Releases · alibaba/canal · GitHub

目录

conf -> example -> instance.properties
日志文件名称和记录位置,如下图所示，修改数据库连接地址、日志文件、连接的用户名和密码

show master status(数据库查询日志文件命令)

 

 五：springboot连接canal的客户端

案例下载：https://gitee.com/there-is-wind-in-the-woods/springboot_canal

pom文件
<repositories>
        <repository>
            <id>maven-develop</id>
            <url>https://gitee.com/warehouse-lxf/develop/blob/maven/</url>
        </repository>
    </repositories>
    <dependencies>
        <dependency>
            <groupId>com.xpand</groupId>
            <artifactId>starter-canal</artifactId>
            <version>1.0.2-SNAPSHOT</version>
        </dependency>
    </dependencies>

application.yml文件

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/pro2run?serverTimezone=GMT%2B8
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
    max-idle: 10
    max-wait: 10000
    min-idle: 5
    initial-size: 5

#canal服务 数据同步
canal:
  client:
    instances:
      example:
        host: 192.168.3.92 #canal服务端ip
        port: 11111   #canal服务端默认端口为11111
        batchSize: 1000

Listener监听器

import com.alibaba.otter.canal.protocol.CanalEntry;
import com.xpand.starter.canal.annotation.*;
import com.xpand.starter.canal.annotation.CanalEventListener;

import java.util.List;

@CanalEventListener
public class BusinessListener {
    /**
     * 数据增量同步
     * 实现业务逻辑
     */
    @UpdateListenPoint
    public void update(CanalEntry.EventType eventType, CanalEntry.RowData rowData){
        System.out.println(eventType);
        //数据变更前
        List<CanalEntry.Column> beforeColumnsList = rowData.getBeforeColumnsList();
        for (CanalEntry.Column column : beforeColumnsList) {
            System.out.println(column.getName() + ":" + column.getValue());
        }
        //数据变更后
        List<CanalEntry.Column> afterColumnsList = rowData.getAfterColumnsList();
        for (CanalEntry.Column column : afterColumnsList) {
            System.out.println(column.getName() + ":" + column.getValue());
        }
    }

    @InsertListenPoint(destination = "example", schema = "pro2run", table = {"run2user"})
    public void insert(CanalEntry.EventType eventType, CanalEntry.RowData rowData){
        System.out.println(eventType);
        //数据变更后
        List<CanalEntry.Column> afterColumnsList = rowData.getAfterColumnsList();
        System.out.println("afterColumnsList = " + afterColumnsList);
        for (CanalEntry.Column column : afterColumnsList) {
            System.out.println(column.getName() + ":" + column.getValue());
        }
    }

    @DeleteListenPoint
    public void delete(CanalEntry.EventType eventType, CanalEntry.RowData rowData){
        System.out.println(eventType);
        //数据变更前
        List<CanalEntry.Column> beforeColumnsList = rowData.getBeforeColumnsList();
        for (CanalEntry.Column column : beforeColumnsList) {
            System.out.println(column.getName() + ":" + column.getValue());
        }
    }

    /**
     * destination 指定监听实例
     * schema	指定监听数据库名称
     * table	指定表名 {"user"} || {"user","user_info"}
     * eventType 指定监听CURD类型
     */
    @ListenPoint(destination = "example", schema = "pro2run", table = {"run2user","run2info"}
            , eventType = {CanalEntry.EventType.INSERT,CanalEntry.EventType.UPDATE})
    public void listenPoint(CanalEntry.EventType eventType, CanalEntry.RowData rowData){
        System.out.println(eventType);
        List<CanalEntry.Column> beforeColumnsList = rowData.getBeforeColumnsList();
        for (CanalEntry.Column column : beforeColumnsList) {
            System.out.println(column.getName() + ":" + column.getValue());
        }
    }

}