Canal

Canal

一、介绍

canal 主要是基于 MySQL 数据库增量日志解析，提供增量数据订阅和消费，eg.更新redis缓存、es数据

官网：https://github.com/alibaba/canal

工作原理

• canal 将自己伪装成MySQL从节点 ，向 MySQL主节点发送 dump 协议
• MySQL 主节点 收到 dump 请求，开始推送 binary log 给 canal
• canal 解析 binary log 获取库表增删改的数据

二、安装

docker安装mysql

mysql.cnf：

[client]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

server_id=1
##指定不需要同步的数据库名称
binlog-ignore-db=mysql
##开启binlog, 指定名称
log-bin=mysql-master-bin
##设置binlog 使用内存大小
binlog-cache-size=1M
##binlog 过期时间
expire_logs_days=7
##忽略主从复制中的错误,避免从库复制中断 1062错误指主键重复 1032错误指主从不一致
slave_skip_errors=1062
#设置binlog日志格式为row
binlog_format=row

bin_format记录了日志的格式，Mysql日志格式：

日志格式	说明
STATEMENT	记录SQL语句，对数据进行修改的SQL会被记录在日志中
ROW(默认)	记录每一行的数据变更
MIXED	混合模式，默认采用STATEMENT，特殊情况会自动切换成ROW

**注意：**必须设置为ROW，canal中没有mysql存储引擎，对于STATEMENT或MIXED格式日志无法解析sql语句

MySQL8以上版本的账户加密方式是caching_sha2_password，Navicat不支持这种账户加密方式

use mysql;
select host,user,plugin from user;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码'; //修改为原始加密方式

详细安装命令

docker安装canal

docker pull canal/canal-server //拉取镜像
docker run --name canal -d canal/canal-server

//复制配置文件
docker cp canal:/home/admin/canal-server/conf/canal.properties /root/canal/conf
docker cp canal:/home/admin/canal-server/conf/example/instance.properties /root/canal/conf

docker rm -f canal

修改canal.properties文件(tcp模式)：

修改instance.properties文件：

slaveId与mysql serverId不一样即可

数据库用户名密码默认为root，修改为自己的用户名密码

yum install tree
tree //显示目录图

docker run -d -p 11111:11111 --name=canal --privileged=true 
-v /root/canal/conf/canal.properties:/home/admin/canal-server/conf/canal.properties 
-v /root/canal/conf/example/instance.properties:/home/admin/canal-server/conf/example/instance.properties 
-v /root/canal/logs:/home/admin/canal-server/logs canal/canal-server

注意：如果服务器内存不够需要修改startup.sh文件

docker cp canal:/home/admin/canal-server/bin/startup.sh .
docker run -d -p 11111:11111 --name=canal --privileged=true 
-v /root/canal/conf/canal.properties:/home/admin/canal-server/conf/canal.properties 
-v /root/canal/conf/example/instance.properties:/home/admin/canal-server/conf/example/instance.properties 
-v /root/canal/logs:/home/admin/canal-server/logs 
-v /root/canal/startup.sh:/home/admin/canal-server/bin/startup.sh //挂载修改过的startup.sh
canal/canal-server 

三、更新redis缓存

canal接收binlog日志 封装成Message对象：

canal监听mysql增删改操作更新redis数据：

创建SpringBoot项目并导入依赖：

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

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

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
    <exclusions>
        <exclusion>
            <groupId>org.junit.vintage</groupId>
            <artifactId>junit-vintage-engine</artifactId>
        </exclusion>
    </exclusions>
</dependency>

<!--热部署-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-devtools</artifactId>
    <optional>true</optional>
</dependency>

<!--自定义类配置提示-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-configuration-processor</artifactId>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.8</version>
</dependency>

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.3.4</version>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.80</version>
</dependency>

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

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.4</version>
</dependency>

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

<dependency>
    <groupId>org.springframework.amqp</groupId>
    <artifactId>spring-rabbit-test</artifactId>
    <scope>test</scope>
</dependency>

<!-- canal依赖 -->
<dependency>
    <groupId>com.alibaba.otter</groupId>
    <artifactId>canal.client</artifactId>
    <version>1.1.4</version>
</dependency>

application.yaml：

# 应用服务 WEB 访问端口
server:
  port: 8080
# 应用名称
spring:
  application:
    name: Canal
  datasource:
    username: 用户名
    password: 密码
    url: jdbc:mysql://服务器IP:3306/test
    driver-class-name: com.mysql.cj.jdbc.Driver

    druid:
      aop-patterns: com.qingsongxyz.* #  配置Spring监控
      filters: 'stat,wall'
      stat-view-servlet:
        enabled: true # 打开监控统计功能
        login-username: admin
        login-password: admin
        reset-enable: true
      web-stat-filter:
        enabled: true # Web关联监控配置
      filter:
        stat:
          enabled: true # 开启sql监控
        wall:
          enabled: true # 开启防火墙
          db-type: mysql
          config:
            drop-table-allow: false
  redis:
    # 单点redis配置
    host: 服务器IP
    port: 6379
    password: 密码
    client-type: lettuce

  rabbitmq:
    host: 服务器IP
    port: 5672
    username: 用户名
    password: 密码

logging:
  level:
    io:
      letture:
        core: debug
  pattern:
    dateformat: MM-dd HH:mm:ss:SSS


mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # 配置日志
    map-underscore-to-camel-case: false

# Canal配置
canal:
  host: 服务器IP
  port: 11111
  destination: example
  database: test.*

建立test数据库teacher表：

CREATE TABLE `teacher` (
  `tno` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

MybatisPlus配置类：

@Configuration
public class MybatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        //乐观锁
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());

        //分表
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());

        //阻止恶意的全表更新删除
        interceptor.addInnerInterceptor(new BlockAttackInnerInterceptor());

        return interceptor;
    }
}

Redis配置类：

@Configuration
public class RedisConfig {

    //配置集群的主从读写分离
    @Bean
    public LettuceClientConfigurationBuilderCustomizer configurationBuilderCustomizer(){
        return configurationBuilder -> configurationBuilder.readFrom(ReadFrom.REPLICA_PREFERRED);
    }

    @Bean
    public RedisTemplate<String, Object> redisTemplate(RedisConnectionFactory redisConnectionFactory) {
        RedisTemplate<String, Object> template = new RedisTemplate<>();
        template.setConnectionFactory(redisConnectionFactory);

        Jackson2JsonRedisSerializer jackson2JsonRedisSerializer = new Jackson2JsonRedisSerializer(Object.class);

        ObjectMapper objectMapper = new ObjectMapper();
        objectMapper.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.ANY);

        //方法已过时
        //objectMapper.enableDefaultTyping(ObjectMapper.DefaultTyping.NON_FINAL);
        objectMapper.activateDefaultTyping(LaissezFaireSubTypeValidator.instance, ObjectMapper.DefaultTyping.NON_FINAL, JsonTypeInfo.As.PROPERTY);

        jackson2JsonRedisSerializer.setObjectMapper(objectMapper);

        StringRedisSerializer stringRedisSerializer = new StringRedisSerializer();

        // key采用String的序列化方式
        template.setKeySerializer(stringRedisSerializer);

        // hash的key也采用String的序列化方式
        template.setHashKeySerializer(stringRedisSerializer);

        // value序列化方式采用jackson
        template.setValueSerializer(jackson2JsonRedisSerializer);

        // hash的value序列化方式采用jackson
        template.setHashValueSerializer(jackson2JsonRedisSerializer);

        template.afterPropertiesSet();

        return template;
    }
}

Canal配置类：

@Configuration
@ConfigurationProperties(prefix = "canal")
@Data
public class CanalConfig {

    private String host;

    private Integer port;

    private String destination;

    private String database;

    @Bean
    public CanalConnector CanalConnector(){
        //1.创建连接
        CanalConnector canalConnector = CanalConnectors.newSingleConnector(new InetSocketAddress(host, port), destination, "", "");

        //2.连接
        canalConnector.connect();

        //3.订阅数据库
        canalConnector.subscribe(database);
        return canalConnector;
    }
}

RabbitMQ交换机队列配置：

@Configuration
public class CacheConfig {

    public static final String CANAL_EXCHANGE_NAME = "canal_exchange";

    public static final String REDIS_QUEUE_NAME = "canal_redis_queue";

    @Bean
    public FanoutExchange canalExchange(){
        return ExchangeBuilder.fanoutExchange(CANAL_EXCHANGE_NAME).build();
    }

    @Bean
    public Queue canalRedisQueue(){
        return QueueBuilder.durable(REDIS_QUEUE_NAME).build();
    }

    @Bean
    public Binding canalExchangeToRedisQueue(@Qualifier("canalExchange") Exchange canalExchange,
                                             @Qualifier("canalRedisQueue") Queue redisQueue){
        return BindingBuilder.bind(redisQueue).to(canalExchange).with("").noargs();
    }
}

教师实体类：

@NoArgsConstructor
@AllArgsConstructor
@Data
@TableName(value = "`teacher`")
public class Teacher implements Serializable {

    @TableId(value = "`tno`", type = IdType.INPUT)
    private String tno;

    @TableField(value = "`name`")
    private String name;
}

相应实体类：

@Data
@NoArgsConstructor
@AllArgsConstructor
public class CommonResult {

    private Integer code;

    private Object data;

    private String message;

    public static CommonResult ok(){
        return new CommonResult(HttpStatus.HTTP_OK, null, null);
    }

    public static CommonResult ok(String message){
        return new CommonResult(HttpStatus.HTTP_OK, null, message);
    }

    public static CommonResult ok(Object data, String message){
        return new CommonResult(HttpStatus.HTTP_OK, data, message);
    }

    public static CommonResult failure(Integer code, String message){
        return new CommonResult(code, null, message);
    }

    public static CommonResult failure(Integer code, List<ObjectError> error){

        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("参数校验失败");
        for (ObjectError objectError : error) {
            stringBuilder.append(",");
            stringBuilder.append(objectError.getDefaultMessage());
        }
        return new CommonResult(code, null, stringBuilder.toString());
    }

}

Redis常量类：

public class RedisConstant {

    public static final String TEACHER_LIST_KEY = "teacherList";

    public static final Integer TEACHER_LIST_KEY_TTL_SECOND = 120;
}

TeacherMapper：

@Mapper
public interface TeacherMapper extends BaseMapper<Teacher> {

}

TeacherService：

public interface TeacherService extends IService<Teacher> {

    int addTeacher(Teacher teacher);

    int deleteTeacher(String tno);

    int updateTeacher(Teacher teacher);

    Teacher getTeacherByTno(String tno);

    List<Teacher> getTeacherList();

}

TeacherServiceImpl：

@Service
@Slf4j
public class TeacherServiceImpl extends ServiceImpl<TeacherMapper, Teacher> implements TeacherService {

    @Autowired
    private TeacherMapper teacherMapper;

    @Autowired
    private StringRedisTemplate stringRedisTemplate;

    @Override
    public int addTeacher(Teacher teacher) {
        return teacherMapper.insert(teacher);
    }

    @Override
    public int deleteTeacher(String tno) {
        return teacherMapper.deleteById(tno);
    }

    @Override
    public int updateTeacher(Teacher teacher) {
        return teacherMapper.updateById(teacher);
    }

    @Override
    public Teacher getTeacherByTno(String tno) {
        //1.查询redis获取教师信息
        Map<Object, Object> map = stringRedisTemplate.opsForHash().entries(TEACHER_LIST_KEY + ":" + tno);

        if (ObjectUtil.isNotEmpty(map)) {
            Teacher teacher = BeanUtil.fillBeanWithMap(map, new Teacher(), CopyOptions.create().ignoreNullValue());
            return teacher;
        }
		
        //2.缓存未命中,从数据查询
        Teacher teacher = getById(tno);
        //3.设置缓存
        if (ObjectUtil.isNotEmpty(teacher)) {
            Map<String, Object> beanToMap = BeanUtil.beanToMap(teacher, new HashMap<>(), CopyOptions.create().ignoreNullValue());
            log.info("getTeacherByTno --> beanToMap:{}", beanToMap);
            stringRedisTemplate.opsForHash().putAll(TEACHER_LIST_KEY + ":" + teacher.getTno(), beanToMap);
            //设置过期时间添加随机值避免缓存雪崩
            Random random = new Random();
            int randomTime = random.nextInt(20);
            stringRedisTemplate.expire(TEACHER_LIST_KEY + ":" + teacher.getTno(), TEACHER_LIST_KEY_TTL_SECOND + randomTime, TimeUnit.SECONDS);
        }
        return teacher;
    }

    @Override
    public List<Teacher> getTeacherList() {
        return list();
    }
}

TeacherController：

@RestController
public class TeacherController {

    @Autowired
    private TeacherService teacherService;

    @Autowired
    private CanalServiceImpl canalServiceImpl;

    @PostConstruct
    void listen() throws InvalidProtocolBufferException, InterruptedException {
        canalServiceImpl.listen();
    }

    @PostMapping("/{tno}")
    public CommonResult addTeacher(@PathVariable("tno") String tno, String name) {
        Teacher teacher = new Teacher(tno, name);
        int success = teacherService.addTeacher(teacher);
        if (success > 0) {
            return CommonResult.ok(teacher, "添加教师成功!");
        }
        return CommonResult.failure(HttpStatus.HTTP_INTERNAL_ERROR, "添加教师失败!!!");
    }

    @DeleteMapping("/{tno}")
    public CommonResult deleteTeacher(@PathVariable("tno") String tno) {
        int success = teacherService.deleteTeacher(tno);
        if (success > 0) {
            return CommonResult.ok("删除教师成功!");
        }
        return CommonResult.failure(HttpStatus.HTTP_INTERNAL_ERROR, "删除教师失败!!!");
    }

    @PutMapping("/{tno}")
    public CommonResult updateTeacher(@PathVariable("tno") String tno, Teacher teacher) {
        teacher.setTno(tno);
        int success = teacherService.updateTeacher(teacher);
        if (success > 0) {
            return CommonResult.ok(teacher, "修改教师信息成功!");
        }
        return CommonResult.failure(HttpStatus.HTTP_INTERNAL_ERROR, "修改教师信息失败!!!");
    }

    @GetMapping("/{tno}")
    public CommonResult getTeacherByTno(@PathVariable("tno") String tno) {
        Teacher teacher = teacherService.getTeacherByTno(tno);
        return CommonResult.ok(teacher, "通过id查询教师信息成功!");
    }

    @GetMapping("/list")
    public CommonResult getTeacherList() {
        List<Teacher> teacherList = teacherService.getTeacherList();
        return CommonResult.ok(teacherList, "查询教师列表成功!");
    }
}

Entry类型：

开启异步任务，一直监听mysql：

@Service
@Slf4j
public class CanalServiceImpl {

    @Autowired
    private CanalConnector canalConnector;

    @Autowired
    private RabbitTemplate rabbitTemplate;

    @Async
    public void listen() throws InterruptedException {
        log.info("异步任务启动...");
        while (true) {
            //1.获取数据
            //尝试拿数据,有多少取多少,不会阻塞等待
            Message message = canalConnector.get(100);

            //2.获取Entry集合
            List<CanalEntry.Entry> entries = message.getEntries();

            if (ObjectUtils.isEmpty(entries)) {
                log.info("没有数据...");
                TimeUnit.SECONDS.sleep(1);
            } else {
                //2.1 遍历entries
                for (CanalEntry.Entry entry : entries) {

                    //获取entry类型
                    CanalEntry.EntryType entryType = entry.getEntryType();

                    //判断entry是否为ROWDATA(增删改操作),传入消息队列
                    if (CanalEntry.EntryType.ROWDATA.equals(entryType)) {

                        //获取序列化后的数据
                        ByteString storeValue = entry.getStoreValue();
                        log.info("投放数据:{}", storeValue.toString());
                        rabbitTemplate.convertAndSend(CANAL_EXCHANGE_NAME, "", storeValue);
                    }
                }
            }
        }
    }
}

操作类型：

消费canal生产的消息，更新缓存：

@Component
@Slf4j
public class CacheListener {

    @Autowired
    private StringRedisTemplate stringRedisTemplate;

    @RabbitListener(queues = {"canal_redis_queue"})
    public void receive(ByteString storeValue, Message message) throws InvalidProtocolBufferException {

        //反序列化数据
        CanalEntry.RowChange rowChange = CanalEntry.RowChange.parseFrom(storeValue);

        //获取entry类型
        CanalEntry.EventType eventType = rowChange.getEventType();

        //获取数据集
        List<CanalEntry.RowData> rowDataList = rowChange.getRowDatasList();

        //对于insert操作没有前数据,delete操作没有后数据,修改操作有前后数据
        for (CanalEntry.RowData rowData : rowDataList) {
            //更新之前的数据
            List<CanalEntry.Column> beforeColumnsList = rowData.getBeforeColumnsList();

            //更新之后的数据
            List<CanalEntry.Column> afterColumnsList = rowData.getAfterColumnsList();

            JSONObject beforeJsonObject = new JSONObject();

            JSONObject afterJsonObject = new JSONObject();

            for (CanalEntry.Column column : beforeColumnsList) {
                beforeJsonObject.put(column.getName(), column.getValue());
            }

            for (CanalEntry.Column column : afterColumnsList) {
                afterJsonObject.put(column.getName(), column.getValue());
            }

            Random random = new Random();
            int randomTime = random.nextInt(20);

            switch (eventType) {
                case INSERT:
                    //对于插入操作,构建缓存
                    log.info("insert:{}", afterJsonObject);
                    stringRedisTemplate.opsForHash().putAll(TEACHER_LIST_KEY + ":" + afterJsonObject.getString("tno"), afterJsonObject.getInnerMap());
                    stringRedisTemplate.expire(TEACHER_LIST_KEY + ":" + afterJsonObject.getString("tno"), TEACHER_LIST_KEY_TTL_SECOND + randomTime, TimeUnit.SECONDS);
                    break;
                case DELETE:
                    //对于删除操作,删除缓存
                    log.info("delete:{}", beforeJsonObject);
                    stringRedisTemplate.delete(TEACHER_LIST_KEY + ":" + beforeJsonObject.getString("tno"));
                    break;
                case UPDATE:
                    //对于修改操作,更新缓存
                    log.info("update:{}|{}", beforeJsonObject, afterJsonObject);
                    stringRedisTemplate.opsForHash().putAll(TEACHER_LIST_KEY + ":" + afterJsonObject.getString("tno"), afterJsonObject.getInnerMap());
                    stringRedisTemplate.expire(TEACHER_LIST_KEY + ":" + afterJsonObject.getString("tno"), TEACHER_LIST_KEY_TTL_SECOND + randomTime, TimeUnit.SECONDS);
                    break;
            }
        }
    }
}

启动测试

新增教师：

修改教师信息：

删除教师：

Gitee仓库