第三章 FlinkCDC专题之mysql-cdc-source基础练习（Flinksql）

本文链接：https://blog.csdn.net/qq_27924553/article/details/122240007

FlinkCDC专题

官网:https://ververica.github.io/flink-cdc-connectors/master/content/connectors/oracle-cdc.html

1、导入依赖

<dependencies>
 <dependency>
 <groupId>org.apache.flink</groupId>
 <artifactId>flink-java</artifactId>
 <version>1.12.0</version>
 </dependency>
 <dependency>
 <groupId>org.apache.flink</groupId>
 <artifactId>flink-streaming-java_2.12</artifactId>
 <version>1.12.0</version>
 </dependency>
 <dependency>
 <groupId>org.apache.flink</groupId>
 <artifactId>flink-clients_2.12</artifactId>
 <version>1.12.0</version>
 </dependency>
 <dependency>
 <groupId>org.apache.hadoop</groupId>
 <artifactId>hadoop-client</artifactId>
 <version>3.1.3</version>
 </dependency>
 <dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <version>5.1.49</version>
</dependency>
<dependency>
 <groupId>org.apache.flink</groupId>
 <artifactId>flink-table-planner-blink_2.12</artifactId>
 <version>1.12.0</version>
</dependency>
<dependency>
 <groupId>com.ververica</groupId>
<artifactId>flink-connector-mysql-cdc</artifactId>
 <version>2.0.2</version>
</dependency>
 <dependency>
 <groupId>com.alibaba</groupId>
 <artifactId>fastjson</artifactId>
 <version>1.2.75</version>
 </dependency>
</dependencies>
<build>
 <plugins>
 <plugin>
 <groupId>org.apache.maven.plugins</groupId>
 <artifactId>maven-assembly-plugin</artifactId>
 <version>3.0.0</version>
 <configuration>
 <descriptorRefs>
 <descriptorRef>jar-with-dependencies</descriptorRef>
 </descriptorRefs>
 </configuration>
 <executions>
 <execution>
 <id>make-assembly</id>
 <phase>package</phase>
 <goals>
 <goal>single</goal>
 </goals>
 </execution>
 </executions>
 </plugin>
 </plugins>
</build>

2、测试环境准备

在mysql中对数据监测的库开启binlog

sudo vim /etc/my.cnf

#添加数据库的binlog
binlog-do-db=flink

#重启MySQL服务
sudo systemctl restart mysqld

在这里插入图片描述

查询生成日志

cd /var/lib/mysql

在这里插入图片描述

创建库表并插入数据

create database flink;

use flink;

CREATE TABLE `stu` (
  `id` int(5),
  `name` varchar(20) ,
  `grade` int(5),
  `score` int(5),
    primary key (id)
) 


insert into stu value(1,"zhangsan",9,99);

3、FlinkCDC之FlinkSQL实战案例

（1）编写脚本

package com.hxjy;

import org.apache.flink.api.java.tuple.Tuple;
import org.apache.flink.api.java.tuple.Tuple2;
import org.apache.flink.streaming.api.datastream.DataStream;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.table.api.Table;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;
import org.apache.flink.types.Row;

public class flinksqldemo {
    public static void main(String[] args) throws Exception{
        // 1、创建执行环境
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(1);
        StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env);

        // 2、创建Flink-Mysql-CDC的Source
        tableEnv.executeSql(
                "CREATE TABLE user_info (" +
                        " id INT," +
                        " name STRING," +
                        " grade INT," +
                        " score INT," +
                        " PRIMARY KEY (`id`)  NOT ENFORCED" +
                        ") WITH (" +
                        " 'connector' = 'mysql-cdc'," +
                        " 'hostname' = '192.168.6.102'," +
                        " 'port' = '3306'," +
                        " 'username' = 'root'," +
                        " 'password' = '123456'," +
                        " 'database-name' = 'flink'," +
                        " 'table-name' = 'stu'," +
                        "   'debezium.snapshot.mode' = 'initial' " +  // 读取mysql的全量,增量以及更新数据
                        ")");
        // 3、打印输出
        Table table = tableEnv.sqlQuery("select * from user_info");
        DataStream<Tuple2<Boolean, Row>> stream = tableEnv.toRetractStream(table,Row.class);
        stream.print();

        // 4、提交任务
        env.execute();
    }
}