FlinkCDC2.0利用FlinkSQL采集MySQL

大数据研习社 2022-07-18

686

长按二维码关注

大数据领域必关注的公众号

1.依赖管理

将如下依赖包放到FLINK_HOME/lib下。

flink-sql-connector-mysql-cdc-2.2.0.jar

flink-connector-jdbc_2.11-1.14.3.jar

flink-shaded-hadoop-2-uber-2.8.3-10.0.jar

2.Flink全局配置

修改flink-conf.yaml文件：

execution.checkpointing.externalized-checkpoint-retention: RETAIN_ON_CANCELLATION
execution.checkpointing.max-concurrent-checkpoints: 1
execution.checkpointing.mode: EXACTLY_ONCE
execution.checkpointing.timeout: 10min
state.backend: filesystem
state.checkpoints.dir: hdfs://mycluster/flinkcdc-checkpoints

3.sql-client提交作业模式

1.Standalone模式

启动sql-client：bin/sql-client.sh embedded

注意，如果使用standalone模式运行，需要先启动一个Flink standalone集群，方法如下：

bin/start-cluster.sh

2.yarn-session模式（本案例使用方式）

先启动Flink yarn-session集群：bin/yarn-session.sh -s 1 -jm 1024 -tm 1024

然后再启动sql-client：bin/sql-client.sh embedded -s yarn-session

4.checkpoint配置

官网地址：https://nightlies.apache.org/flink/flink-docs-release-1.14/docs/deployment/config/#checkpointing

#sql-client设置checkpoint参数

SET 'execution.checkpointing.interval' = '10s';
SET 'parallelism.default' = '3';

5.创建source table

CREATE TABLE `cars`(
 `id` BIGINT,
 `owerId` BIGINT,
 `carCode` STRING,
 `carColor` STRING,
 `type` BIGINT,
 `remark` STRING,
 PRIMARY KEY(id) NOT ENFORCED
) WITH (
 'connector' = 'mysql-cdc',
 'hostname' = 'hadoop1',
 'port' = '3306',
 'username' = 'hive',
 'password' = 'hive',
 'database-name' = 'sca',
 'table-name' = 'cars',
 'connect.timeout' = '60s'
);

6.创建sink table

CREATE TABLE `cars_copy`(
 `id` BIGINT,
 `owerId` BIGINT,
 `carCode` STRING,
 `carColor` STRING,
 `type` BIGINT,
 `remark` STRING,
 PRIMARY KEY(id) NOT ENFORCED
) WITH (
 'connector' = 'jdbc',
 'url' = 'jdbc:mysql://hadoop1:3306/sca?useUnicode=true&characterEncoding=utf8',
 'username' = 'hive',
 'password' = 'hive',
 'table-name' = 'cars_copy',
 'sink.parallelism' = '2'
);

7.source to sink table

将采集过来的数据写入MySQL

insert into cars_copy SELECT * FROM cars;

查询结果表数据记录数

select count(*) from cars_copy

新增测试数据集（再次查看结果表）

insert into `cars` (`id`, `owerId`, `carCode`, `carColor`, `type`, `remark`) values('10096006','10244815','港T·7RONE','红色','1',NULL);
insert into `cars` (`id`, `owerId`, `carCode`, `carColor`, `type`, `remark`) values('10096007','10244816','港T·7RONE','黄色','1',NULL);

备注：如果通过手动cacel job，下次重新启动job仍然会重头采集表中的数据。

8.cacel job时保存Save point

bin/flink stop --savepointPath hdfs://mycluster/flinkcdc-savepoints -Dyarn.application.id=application_1658045078748_0001 79ce915e39fc1d18a194b6a464d7c3fd

备注：结尾一个参数为yarn中的job id，第二个参数为flink的job id。

9.cacel job之后重新恢复job

#设置job从上一次savepoint位置开始处理

SET 'execution.checkpointing.interval' = '10s';
SET 'parallelism.default' = '3';
SET 'execution.savepoint.path' = 'hdfs://mycluster/flinkcdc-savepoints/savepoint-79ce91-92206bcaaad2';

备注：该参数的值为savepoint路径。

#执行flink sql job

insert into cars_copy SELECT * FROM cars;

#新增测试数据集

insert into `cars` (`id`, `owerId`, `carCode`, `carColor`, `type`, `remark`) values('10096008','10244815','港T·7RONE','红色','1',NULL);
insert into `cars` (`id`, `owerId`, `carCode`, `carColor`, `type`, `remark`) values('10096009','10244816','港T·7RONE','黄色','1',NULL);