mysql binlog 监听缓存 mysql数据监听

转载

bingfeng 2023-10-06 19:10:21

文章标签 mysql binlog 监听缓存服务器运维 java mysql 文章分类 MySQL 数据库

1. 本地服务器实现方案

1.1 创建触发器

mysql binlog 监听缓存 mysql数据监听_java

BEGIN
	IF((SELECT stcd from st_stbprp_b where stcd = NEW.stcd) IS NOT NULL) THEN
		CALL s_insert_total(NEW.stcd, NEW.tm , NEW.intv);
	END IF;	
END

触发器创建在需要监听数据的表上面。具体代码由自己的业务去变动。
统一步骤，大概都是先判断是否符合条件，符合的话调用编写好的存储过程。

1.2 创建存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `s_insert_total`(
	# 函数入参
	IN s_stcd VARCHAR(50),
	IN s_tm DATETIME,
	IN s_intv VARCHAR(10)
)
BEGIN 
		# 声明变量 data_num
		DECLARE data_stcd VARCHAR(50) DEFAULT NULL;
		DECLARE thirty_num, one_num, three_num DOUBLE DEFAULT 0;
		# 输入执行的SQL
		# 查询是否有该条数据
		SELECT 
				stcd INTO data_stcd 
		FROM 
				st_data_s 
		WHERE 
				stcd = s_stcd
		AND
				intv = s_intv;
				
		# 查询指定范围内的统计值		
		SELECT SUM(drp) as drp INTO one_num 
		FROM 
				pptn_r 
		WHERE 
			stcd = s_stcd 
		AND 
			tm > DATE_ADD(s_tm, INTERVAL -1 hour) 
		AND 
			tm <= s_tm 
		AND 
			intv = s_intv;
			
		SELECT SUM(drp) as drp INTO three_num 
		FROM 
				pptn_r 
		WHERE 
			stcd = s_stcd 
		AND 
			tm > DATE_ADD(s_tm, INTERVAL -3 hour) 
		AND 
			tm <= s_tm 
		AND 
			intv = s_intv;
			
		SELECT SUM(drp) as drp INTO thirty_num 
		FROM 
				pptn_r 
		WHERE 
			stcd = s_stcd 
		AND 
			tm > DATE_ADD(s_tm, INTERVAL -30 MINUTE) 
		AND 
			tm <= s_tm 
		AND 
			intv = s_intv;
		
		# 根据data_stcd判断是否存在统计值,存在就更新，否则就插入
		IF data_stcd IS NULL THEN
				INSERT INTO st_data_s (
						stcd,
						stnm,
						update_time,
						thirty_min,
						one_hour,
						three_hour,
						intv
					)
				VALUES
					(s_stcd, '', s_tm, thirty_num, one_num, three_num, s_intv);
		ELSE
				UPDATE 
						st_data_s
				SET 
						update_time = st_tm,
						thirty_min = thirty_num,
						one_hour = one_num,
						three_hour = three_num
				WHERE
						stcd = s_stcd
				AND
						intv = s_intv;
		END IF;
END

存储过程也需要和自己的业务相关，我也贴出自己的。我的业务是统计该条数据最近的半小时，一小时，三小时的统计数据。然后更新到指定数据库。

2. 跨服务器实现方案

2.1 下载canal

1. canal主要是替代触发器，用于监听远程数据库更新。canal下载太慢，我已经上传百度网盘。
链接：https://pan.baidu.com/s/1GGmucVZ6KPSn9XVPDToYgQ?pwd=mpal
提取码：mpal
--来自百度网盘超级会员V2的分享

2.1 安装配置canal

安装解压后找到“\conf\example”这个路径下的，这个选中的文件打开。

mysql binlog 监听缓存 mysql数据监听_mysql_02

canal.instance.master.address=127.0.0.1:3306 // 这个配置数据库的地址和端口，根据实际情况修改。
canal.instance.dbUsername=root // 数据库账户
canal.instance.dbPassword=123456 // 数据库密码
其他的不用修改。

启动canal，双击“\bin”目录下的“startup.bat”文件。

2.2 创建桥接表

2.2.1检查federated引擎是否开启

Mysql执行这个语句show engines。

mysql binlog 监听缓存 mysql数据监听_mysql_03

FEDERATED字段为YES说明已开启。如果未开启，需要修改mysql的my.ini文件。在最后一行添加“federated”。然后重启Mysql服务。

mysql binlog 监听缓存 mysql数据监听_服务器_04

方式一：

mysql binlog 监听缓存 mysql数据监听_java_05

DROP TABLE IF EXISTS `pptn_r`;
CREATE TABLE `pptn_r`  (

  “这里是具体的字段，需要和你远程需要监听的表结构一致就行了。”
 
) ENGINE=InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '[启用]雨量监测表_热表' ROW_FORMAT = Compact 
ENGINE=FEDERATED  CONNECTION = 'mysql://root:123456@127.0.0.1:3306/test/pptn_r';

方式一可能出现的问题是，密码如果包含特殊符号是会报错的。本人本地测试是没问题。但是实际生产环境，用到的账户密码包含了@。导致创建失败。如果密码为正常密码。可以使用方式一创建桥接表。

方式二

mysql binlog 监听缓存 mysql数据监听_mysql binlog 监听缓存_06

CREATE SERVER pptnlink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'root', PASSWORD '123456',HOST '127.0.0.1', PORT 3307, DATABASE 'test');

CREATE TABLE `pptn_r`  (

    同样的，这里为远程数据表的结构。
 
) ENGINE=InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '[启用]雨量监测表_热表' ROW_FORMAT = Compact 
ENGINE=FEDERATED  CONNECTION = 'pptnlink/pptn_r';

2.3 创建存储过程

这里和本地服务器的一致。我就不重复编写了。

2.4 编写canal客户端

本人使用的Springboot编写的canal客户端。pom引入以下依赖。

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

package com.hi.canal;

import com.alibaba.fastjson.JSONObject;
import com.alibaba.otter.canal.client.CanalConnector;
import com.alibaba.otter.canal.client.CanalConnectors;
import com.alibaba.otter.canal.protocol.CanalEntry;
import com.alibaba.otter.canal.protocol.Message;
import com.hi.common.core.redis.RedisCache;
import com.hi.common.utils.spring.SpringUtils;
import com.hi.system.domain.StStbprpB;
import com.hi.system.mapper.StoredProceduresMapper;
import com.hi.system.service.IStStbprpBService;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.net.InetSocketAddress;
import java.util.HashMap;
import java.util.List;

/**
 * @Author: 胡天霸
 * @Date: 2022/10/06/16:17
 * @Description:
 */

@Component
public class CanalUtil {
    @Value("${canal-config.hostname}")
    private String host;

    @Value("${canal-config.port}")
    private Integer port;

    @Value("${canal-config.tableName}")
    private String tableName;

    @Value("${canal-config.destination}")
    private String destination;


    private final static int BATCH_SIZE = 10000;

    // 开始日志监控
    public void startMonitorSql() {
        // 创建链接
        CanalConnector connector = CanalConnectors
                .newSingleConnector(new InetSocketAddress(host, port), destination, "", "");
        int batchSize = 1000;
        try {
            connector.connect();
            connector.subscribe(tableName);
            connector.rollback();
            System.out.println("开始监听");
            while (true) {
                // 获取指定数量的数据
                Message message = connector.getWithoutAck(batchSize);
                long batchId = message.getId();
                int size = message.getEntries().size();
                if (batchId == -1 || size == 0) {
                    try {
                        System.out.println("无变化");
                        Thread.sleep(15000);
                    } catch (InterruptedException e) {
                    }
                } else {
                    printEntry(message.getEntries());
                }
                // 提交确认
                connector.ack(batchId);
            }
        } finally {
            connector.disconnect();
        }
    }

    private static void printEntry(List<CanalEntry.Entry> entrys) {
        for (CanalEntry.Entry entry : entrys) {
            if (entry.getEntryType() == CanalEntry.EntryType.TRANSACTIONBEGIN
                    || entry.getEntryType() == CanalEntry.EntryType.TRANSACTIONEND) {
                continue;
            }

            CanalEntry.RowChange rowChage = null;
            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();

            for (CanalEntry.RowData rowData : rowChage.getRowDatasList()) {
                if (eventType == CanalEntry.EventType.DELETE) {
                    // 删除，获取删除前的数据
                    redisDelete(rowData.getBeforeColumnsList(),entry.getHeader().getTableName());
                } else if (eventType == CanalEntry.EventType.INSERT) {
                    // 新增，获取新增后的数据
                    redisSet(rowData.getAfterColumnsList(),entry.getHeader().getTableName());
                } else {
                    // 修改，获取修改后的数据
                    //                    printColumn(rowData.getBeforeColumnsList());
                    redisSet(rowData.getAfterColumnsList(),entry.getHeader().getTableName());
                }
            }
        }
    }

    private static void redisSet(List<CanalEntry.Column> columns, String tableName) {
        HashMap<String, Object> map = new HashMap<>();
        JSONObject json = new JSONObject();
        // 字段和值的列表，放入json，后续作为redis的值
        for (CanalEntry.Column column : columns) {
            json.put(column.getName(), column.getValue());
        }
        RedisCache redisCache = SpringUtils.getBean(RedisCache.class);
        List<StStbprpB> stStbprpBS = redisCache.getCacheObject("stcList");
        if (null == stStbprpBS || stStbprpBS.size() < 1){
            stStbprpBS = SpringUtils.getBean(IStStbprpBService.class).selectStStbprpBList(new StStbprpB());
            redisCache.setCacheObject("stcList", stStbprpBS);
        }
        for (StStbprpB stStbprpB :stStbprpBS){
            if (stStbprpB.getStcd().equals(columns.get(0).getValue()) && columns.size() > 0){
                System.out.println("set key: " + columns.get(0).getValue() + ", value: " + json.toJSONString());
                map.put("stcd", json.get("stcd"));
                map.put("tm", json.get("tm"));
                map.put("intv", json.get("intv"));
                map.put("stnm", stStbprpB.getStnm());
                // 调用存储过程
                SpringUtils.getBean(StoredProceduresMapper.class).insertTotal(map);
                map.clear();
            }
        }

    }

    private static void redisDelete(List<CanalEntry.Column> columns, String tableName) {
        JSONObject json = new JSONObject();
        for (CanalEntry.Column column : columns) {
            json.put(column.getName(), column.getValue());
        }
        if (columns.size() > 0) {
            System.out.println("delete key: " + columns.get(0).getValue());           
        }
    }
}

可以监听到插入、删除、更新等数据行。具体业务逻辑自己修改。

package com.hi.canal;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Configurable;
import org.springframework.boot.CommandLineRunner;
import org.springframework.context.annotation.Configuration;

/**
 * @Author: 胡天霸
 * @Date: 2022/10/06/16:26
 * @Description:
 */

@Configuration
public class InitCanalConfig implements CommandLineRunner {
    @Autowired
    private CanalUtil canalUtil;


    @Override
    public void run(String... args) throws Exception {
        canalUtil.startMonitorSql();
    }
}

2.5 Java调用存储过程

Mapper层

package com.hi.system.mapper;

import java.util.HashMap;

/**
 * @Author: 胡天霸
 * @Date: 2022/10/07/9:50
 * @Description:
 */
public interface StoredProceduresMapper {
    void insertTotal(HashMap<String, Object> map);
}

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hi.system.mapper.StoredProceduresMapper">
    <select id="insertTotal" parameterType="map" statementType="CALLABLE">
        {
            call s_insert_total(
                #{stcd, mode=IN, jdbcType=VARCHAR},
                #{tm, mode=IN, jdbcType=VARCHAR},
                #{intv, mode=IN, jdbcType=VARCHAR},
                #{stnm, mode=IN, jdbcType=VARCHAR}
            )
        }
    </select>

</mapper>

调用代码

map.put("stcd", json.get("stcd"));
map.put("tm", json.get("tm"));
map.put("intv", json.get("intv"));
map.put("stnm", stStbprpB.getStnm());
// 说明一下，这个是因为调用代码在static方法里面，不能试用注入，所以我用的Spring工具类，去获取的StoredProceduresMapper调用里面实现的方法。正常情况直接注入调用就行。
SpringUtils.getBean(StoredProceduresMapper.class).insertTotal(map);

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：Android计算器乘法手机计算器乘号是哪个

下一篇：mysql视图列排除 mysql视图菜鸟教程

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯