springboot 使用flinkCDC 基于bin_log 监听mysql 数据 到 mysql (多表分片处理)

前提: 被监听数据库需要开启bin_log , 账号需要有可查看bin_log日志权限

1. 添加依赖


    <flink-version>1.12.0</flink-version>
    <flink-mysql-version>2.0.0</flink-mysql-version>



    <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-java</artifactId>
            <version>${flink-version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-streaming-java_2.11</artifactId>
            <version>${flink-version}</version>
        </dependency>


        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-clients_2.11</artifactId>
            <version>${flink-version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-table-planner-blink_2.11</artifactId>
            <version>1.12.0</version>
        </dependency>


        <dependency>
            <groupId>com.ververica</groupId>
            <artifactId>flink-connector-mysql-cdc</artifactId>
            <version>${flink-mysql-version}</version>

        </dependency>

        <!--flinkcdc 结束-->

2. 编写封装自定义监听返回结果集 json

/**
 * @author: xrp
 * @date: 2022/05/05/10:25
 * @description
 */
public class LinkConfig  implements DebeziumDeserializationSchema<String> {

    /**
     *
     * @param sourceRecord
     * @param collector
     */
    @Override
    public void deserialize(SourceRecord sourceRecord, Collector<String> collector) {
        JSONObject result = new JSONObject();
        String topic = sourceRecord.topic();
        String[] fields = topic.split("\\.");
        result.put("db", fields[1]);
        result.put("tableName", fields[2]);

        //获取before数据
        Struct value = (Struct) sourceRecord.value();
        Struct before = value.getStruct("before");
        JSONObject beforeJson = new JSONObject();
        if (before != null) {
            beforeJson = getJson(before);
        }
        result.put("before", beforeJson);

        //获取after数据
        Struct after = value.getStruct("after");
        JSONObject afterJson = new JSONObject();
        if (after != null) {
            //获取列信息
            afterJson = getJson(after);
        }
        result.put("after", afterJson);

        //获取操作类型
        Envelope.Operation operation = Envelope.operationFor(sourceRecord);
        result.put("op", operation);
        collector.collect(result.toJSONString());
    }

    private JSONObject getJson(Struct struct) {
        JSONObject jsonObject = new JSONObject();
        //获取列信息
        Schema schema = struct.schema();
        List<Field> fieldList = schema.fields();

        for (Field field : fieldList) {
            jsonObject.put(field.name(), struct.get(field));
        }
        return jsonObject;
    }




    @Override
    public TypeInformation<String> getProducedType() {
        return BasicTypeInfo.STRING_TYPE_INFO;
    }
}

3. 编写主程序,实现CommandLineRunner ,并且使用@component (目的为了自动执行改方法,进行实时数据监听)

/**
 * @author: xrp
 * @date: 2022/05/05/14:49
 * @description
 */
@Component
public class StartUp implements CommandLineRunner {
    @Override
    public void run(String... args) throws Exception {
        

        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(1);
        DebeziumSourceFunction<String> sourceFunction = MySqlSource.<String>builder()
                .hostname("要监听的数据库地址")
                .port(端口号)
                .username("数据库账户")
                .password("数据库密码")
                .databaseList("数据库")
                .tableList("数据库.数据库表1", "数据库。数据库表2") //多个表逗号分隔
                .deserializer(new LinkConfig()) //自定义返回结果集
                .startupOptions(StartupOptions.initial())
                .serverTimeZone("UTC")
                .build();

        DataStreamSource<String> streamSource = env.addSource(sourceFunction);
        // 多表进行分片处理
        OutputTag<String> orderTag = new OutputTag<>("表1", Types.STRING);

        OutputTag<String> userTag = new OutputTag<>("表2",Types.STRING);


        SingleOutputStreamOperator<String> process = streamSource.map((MapFunction<String, JSONObject>) JSON::parseObject).process(new ProcessFunction<JSONObject, String>() {
            @Override
            public void processElement(JSONObject value, Context context, Collector<String> collector) {
                if ("表1".equals(value.getString("tableName"))) {
                    context.output(orderTag, value.toJSONString());
                } else if ("表2".equals(value.getString("tableName"))) {
                    context.output(userTag, value.toJSONString());
                }
            }
        });
        DataStream<String> orderStream = process.getSideOutput(orderTag);
        DataStream<String> userStream = process.getSideOutput(userTag);
        orderStream.print();
        userStream.print();
        //自定义sink
        streamSource.addSink(new ListenerOrderSink());
        userStream.addSink(new  ListenerUserSink());
        env.executeAsync("fLinkCDC");
    }
}

4. 编写sink (一张表一个对应一个sink)


/**
 * @author: xrp
 * @date: 2022/05/24/17:27
 * @description  订单sink
 */
public class ListenerOrderSink extends RichSinkFunction<String> {

    private static final Logger LOGGER = LoggerFactory.getLogger(ListenerOrderSink.class);


    private PreparedStatement ps = null;

    private Connection connection = null;
    String driver = "com.mysql.cj.jdbc.Driver";
    String url = "jdbc:mysql://要将监听到的数据同步到哪,另一个数据库地址:端口号/数据库名字?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
    String username = "数据库账号";
    String password = "数据库密码";

    @Override
    public void open(Configuration parameters) throws Exception {
        super.open(parameters);
        connection = getConn();
        ps = connection.prepareStatement("insert into 数据库名.表名values (?,?,?,?,?,?,?,?,?,?,?)");
    }
    private Connection getConn() {
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, username, password);
            LOGGER.error("数据库连接成功");
        } catch (Exception e) {
            LOGGER.error("数据库连接失败");
        }
        return connection;
    }


    @Override
    public void invoke(String p, Context context) throws Exception {
    //TranslateJson  将自定义的返回结果集转为具体实体
        TranslateJson translateJson = JSON.parseObject(p, TranslateJson.class);
        if (ConstantValue.CREATE_INFO.equals(translateJson.getOp())) {
            ErpOrder erpOrder = JSON.parseObject(translateJson.getAfter(), ErpOrder.class);
            // 注意: 字段个数需要与表字段个数对应上
            ps.setString(1,erpOrder.getId());
            ps.setString(2,erpOrder.getCode());
            ps.setString(3,erpOrder.getCustomerName());
            ps.setBigDecimal(4,erpOrder.getOrderAmount());
            ps.setString(5,erpOrder.getUserId());
            ps.setString(6,erpOrder.getUserName());
            ps.setString(7,erpOrder.getCreateName());
            ps.setString(8,erpOrder.getCreateDate());
            ps.setInt(9,erpOrder.getStatus());
            ps.setInt(10,erpOrder.getOrderId());
            ps.setString(11,erpOrder.getOrderCode());
            ps.executeUpdate();
        }
    }

    @Override
    public void close() throws Exception {
        super.close();
        if(connection != null){
            connection.close();
        }
        if (ps != null){
            ps.close();
        }
    }


}

5. 将自定义统一监听结果集转为实体

/**
 * @author: xrp
 * @date: 2022/05/05/10:50
 * @description
 */
@Data
public class TranslateJson {
    private static final long serialVersionUID = -74375380912179188L;
    private String op;
    private String before;
    private String after;
    private String db;
    private String tableName;
}

  • 0
    点赞
  • 14
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
要在 Spring Boot监听 MySQL 的增删改日志文件,可以使用 MySQLbinlogbinlogMySQL 的二进制日志,记录了 MySQL 的所有更新操作,包括增删改等。 下面是使用 Spring Boot 监听 MySQL binlog 的步骤: 1. 在 MySQL 配置文件中开启 binlog,可以在 my.cnf 或 my.ini 文件中添加如下配置: ``` [mysqld] log-bin=mysql-bin binlog-format=ROW ``` 这里将 binlog 日志文件存储在名为 mysql-bin 的文件中,格式为 ROW。 2. 在 Spring Boot 中添加 MySQL 驱动和 binlog 相关的依赖,例如: ``` <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.23</version> </dependency> <dependency> <groupId>com.github.shyiko</groupId> <artifactId>mysql-binlog-connector-java</artifactId> <version>0.17.0</version> </dependency> ``` 这里使用mysql-connector-java 和 mysql-binlog-connector-java 两个依赖。 3. 在 Spring Boot 中编写监听 binlog 日志的代码,例如: ``` @Component public class BinlogListener { @Autowired private DataSource dataSource; @PostConstruct public void init() { BinaryLogClient client = new BinaryLogClient("localhost", 3306, "root", "password"); client.setServerId(1); client.setBinlogFilename("mysql-bin.000001"); client.registerEventListener(new BinaryLogClient.EventListener() { @Override public void onEvent(Event event) { EventData data = event.getData(); if (data instanceof WriteRowsEventData) { WriteRowsEventData write = (WriteRowsEventData) data; System.out.println("inserted rows: " + write.getRows()); } else if (data instanceof UpdateRowsEventData) { UpdateRowsEventData update = (UpdateRowsEventData) data; System.out.println("updated rows: " + update.getRows()); } else if (data instanceof DeleteRowsEventData) { DeleteRowsEventData delete = (DeleteRowsEventData) data; System.out.println("deleted rows: " + delete.getRows()); } } }); try { client.connect(); } catch (IOException e) { e.printStackTrace(); } } } ``` 这里使用BinaryLogClient 类来连接 MySQL,通过 setServerId 和 setBinlogFilename 方法设置 binlog 相关参数,通过 registerEventListener 方法注册事件监听器来监听 binlog 日志的写入、更新、删除操作。 需要注意的是,直接监听 MySQLbinlog 日志文件可能会对性能和稳定性造成影响,建议在使用前先进行充分测试和评估。同时,也建议使用专业的数据同步工具来进行 MySQL 数据库的同步,如阿里云的 DTS、腾讯云的 CDC 等。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值