canal监听mysql

canal实际是阿里开发出来的开源的监控数据库的插件，就是通过配置一个canal服务端，本地部署一个客户端和服务端做socket通讯，简单的来讲就是用一个while循环实时向服务端请求，一旦服务端有响应了，说明数据库发生了变化，（服务端那边来监控mysql里的binlog变化）。

具体需要如下步骤：

1.去git里下载客户端canal.deployer-1.1.4.tar.gz

部署相应的服务器后修改两个配置文件

 

 

 以上步骤很可能报错，请查看是否执行成功

 

 

 2.mysql创建用户，授权，开启binlog功能

会使用一下指令：

CREATE USER 'canal'@'%' IDENTIFIED BY 'canal';
GRANT SHOW VIEW, SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
flush privileges;

show variables like 'log_%';

测试binlog变更：
UPDATE `mysql`.`proxies_priv`
SET `Host` = 'localhost',
`User` = 'root',
`Proxied_host` = '',
`Proxied_user` = '',
`With_grant` = '1',
`Grantor` = 'boot@connecting host'
WHERE
(`Host` = 'localhost')
AND (`User` = 'root')
AND (`Proxied_host` = '')
AND (`Proxied_user` = '');

show binlog events;
show master logs;
show variables like 'log_%';
show variables like 'datadir';
show binlog events in 'mysql-bin.000003';

 

3.创建springboot

<dependency>
            <groupId>com.alibaba.otter</groupId>
            <artifactId>canal.client</artifactId>
            <version>1.1.3</version>
</dependency>

这个应该用默认的maven路径

 

package com.fleet;

import javax.annotation.Resource;

import com.fleet.config.CanalUtil;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.cloud.client.discovery.EnableDiscoveryClient;
import org.springframework.cloud.openfeign.EnableFeignClients;

@SpringBootApplication
@EnableDiscoveryClient
@EnableFeignClients
@MapperScan({ "com.fleet.mapper"})
public class BasicApplication  implements CommandLineRunner {

    @Resource
    private CanalUtil canalUtil;

    public static void main(String[] args) {
        SpringApplication.run(BasicApplication.class,args);
    }

    @Override
    public void run(String... strings) throws Exception {
        //项目启动，执行canal客户端监听
        canalUtil.run();
    }
}

package com.fleet.config;


import com.alibaba.otter.canal.client.CanalConnector;
import com.alibaba.otter.canal.client.CanalConnectors;
import com.alibaba.otter.canal.protocol.CanalEntry;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.net.InetSocketAddress;
import java.util.List;

import com.alibaba.otter.canal.protocol.Message;

/**
 * @program: fas-haiot-interface
 * @description: 市级平台数据上传接口相关实现
 * @author: liuAnmin
 * @create: 2021-03-22 15:52
 **/

@Component
@Slf4j
public class CanalUtil {

    @Value("${canal-monitor-mysql.hostname}")
    String canalMonitorHost;

    @Value("${canal-monitor-mysql.port}")
    Integer canalMonitorPort;

    @Value("${canal-monitor-mysql.tableName}")
    String canalMonitorTableName;

    private final static int BATCH_SIZE = 10000;

    /**
     * 启动服务
     */
    @Async("TaskPool")
    public void run() {
        while (true) {
            CanalConnector connector = CanalConnectors.newSingleConnector(new InetSocketAddress(canalMonitorHost, canalMonitorPort), "example", "", "");
            try {
                //打开连接
                connector.connect();
                log.info("数据库检测连接成功!" + canalMonitorTableName);
                //订阅数据库表,全部表q
                connector.subscribe(".*\\..*");
                //回滚到未进行ack的地方，下次fetch的时候，可以从最后一个没有ack的地方开始拿
                connector.rollback();
                while (true) {
                    // 获取指定数量的数据
                    Message message = connector.getWithoutAck(BATCH_SIZE);
                    long batchId = message.getId();
                    int size = message.getEntries().size();
                    if (batchId == -1 || size == 0) {
                    } else {
                        handleDATAChange(message.getEntries());
                    }
                    // 提交确认
                    connector.ack(batchId);
                }
            } catch (Exception e) {
                e.printStackTrace();
                log.error("成功断开监测连接!尝试重连");
            } finally {
                connector.disconnect();
                //防止频繁访问数据库链接: 线程睡眠 10秒
                try {
                    Thread.sleep(10 * 1000);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 打印canal server解析binlog获得的实体类信息
     */
    private void handleDATAChange(List<CanalEntry.Entry> entrys) {
        for (CanalEntry.Entry entry : entrys) {
            if (entry.getEntryType() == CanalEntry.EntryType.TRANSACTIONBEGIN || entry.getEntryType() == CanalEntry.EntryType.TRANSACTIONEND) {
                continue;
            }
            //RowChange对象，包含了一行数据变化的所有特征
            CanalEntry.RowChange rowChage;
            try {
                rowChage = CanalEntry.RowChange.parseFrom(entry.getStoreValue());
            } catch (Exception e) {
                throw new RuntimeException("ERROR ## parser of eromanga-event has an error , data:" + entry.toString(),e);
            }
            CanalEntry.EventType eventType = rowChage.getEventType();
            log.info("Canal监测到更新:【{}】", entry.getHeader().getTableName());
            switch (eventType) {
                /**
                 * 删除操作
                 */
                case DELETE:
                    break;
                /**
                 * 添加操作
                 */
                case INSERT:
                    break;
                /**
                 * 更新操作
                 */
                case UPDATE:
                    System.out.println("UPDATE");
                    break;
                default:
                    break;
            }

        }
    }
}

yml

canal-monitor-mysql:
  hostname: 10.100.2.160
  port: 11111
  tableName: super_fleet