使用flink消费kafka的数据(maxwell产生)同步到mysql

环境:
OS:Centos 7
flink:1.20
同步说明:使用maxwell同步msql数据到kafka,然后再使用flink消费kafka的数据到另外一个mysql实例

 

1. 下载jar包
cd /opt/flink/lib
# Kafka SQL连接器
wget https://maven.aliyun.com/repository/public/org/apache/flink/flink-sql-connector-kafka/3.4.0-1.20/flink-sql-connector-kafka-3.4.0-1.20.jar

# JDBC SQL连接器（写入MySQL）
wget https://repo1.maven.org/maven2/org/apache/flink/flink-connector-jdbc/3.4.0-1.20/flink-connector-jdbc-3.4.0-1.20.jar

# mysql驱动
wget https://repo1.maven.org/maven2/com/mysql/mysql-connector-j/8.0.33/mysql-connector-j-8.0.33.jar

 

2.重启Flink加载jar
/opt/flink/bin/stop-cluster.sh
/opt/flink/bin/start-cluster.sh

 

3. 创建目标表
use test
CREATE TABLE target_user(
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);

 

4.进入Flink SQL 交互式客户端sql-client

/opt/flink/bin/sql-client.sh

SET execution.checkpointing.interval = '3000';
SET table.exec.source.idle-timeout = '10000';
SET parallelism.default=1;

-- source 表
CREATE TABLE source_maxwell (
  id INT,
  name STRING,
  email STRING,
  origin_database STRING METADATA FROM 'value.database' VIRTUAL,
  origin_table STRING METADATA FROM 'value.table' VIRTUAL,
  origin_ts TIMESTAMP_LTZ(3) METADATA FROM 'value.ingestion-timestamp' VIRTUAL
) WITH (
 'connector' = 'kafka',
 'topic' = 'maxwell-mysql',
 'properties.bootstrap.servers' = '192.168.1.134:9092,192.168.1.135:9092,192.168.1.136:9092',
 'properties.group.id' = 'flink_maxwell_mysql_sink_v4',
 'format' = 'maxwell-json',
 'scan.startup.mode' = 'latest-offset'
);

-- mysql sink表
CREATE TABLE sink_mysql_user (
  id INT,
  name STRING,
  email STRING,
  PRIMARY KEY (id) NOT ENFORCED
) WITH (
 'connector' = 'jdbc',
 'url' = 'jdbc:mysql://192.168.1.14:3306/test?useSSL=false&serverTimezone=Asia/Shanghai',
 'table-name' = 'target_user',
 'username' = 'hxl',
 'password' = 'mysql',
 'driver' = 'com.mysql.cj.jdbc.Driver',
 'sink.buffer-flush.max-rows' = '100',
 'sink.buffer-flush.interval' = '1s',
 'sink.max-retries' = '3'
);

-- 写入任务，maxwell-json自动处理insert/update/delete
INSERT INTO sink_mysql_user
SELECT id, name, email
FROM source_maxwell;

 

5.界面上查看job运行状态

 

 

6.源端写入数据，让kafka里有数据

use db_admin;
insert into user (name,email) values('111','111@qq.com');
update user set name ='testtest' where id=10;
delete from user where id=10;

 

7.目标表查看数据
查看test.target_user表数据是否同步过来

 

8.暂停任务
查看job_id
root@dify:/soft# /opt/flink/bin/flink list
Waiting for response...
------------------ Running/Restarting Jobs -------------------
15.09.2026 15:12:58 : 020debbf50f93eb20dd31b9e45e3745f : insert-into_default_catalog.default_database.sink_mysql_user (RUNNING)
--------------------------------------------------------------
No scheduled jobs.

停止
mkdir -p /opt/flink/savepoints
chmod 777 /opt/flink/savepoints

生成savepoint快照(任务继续运行)
root@dify:/opt/flink# /opt/flink/bin/flink savepoint 020debbf50f93eb20dd31b9e45e3745f file:///opt/flink/savepoints
Triggering savepoint for job 020debbf50f93eb20dd31b9e45e3745f.
Waiting for response...
Savepoint completed. Path: file:/opt/flink/savepoints/savepoint-020deb-91fed08b2aaf
You can resume your program from this savepoint with the run command.

快照成功后，关闭任务
root@dify:/opt/flink# /opt/flink/bin/flink cancel 020debbf50f93eb20dd31b9e45e3745f
Cancelling job 020debbf50f93eb20dd31b9e45e3745f.
Cancelled job 020debbf50f93eb20dd31b9e45e3745f.

 

 

然后重执行下面的语句进行启动

/opt/flink/bin/sql-client.sh
SET execution.savepoint.path = 'file:/opt/flink/savepoints/savepoint-020deb-91fed08b2aaf';


/opt/flink/bin/sql-client.sh

SET execution.checkpointing.interval = '3000';
SET table.exec.source.idle-timeout = '10000';
SET parallelism.default=1;

-- source 表
CREATE TABLE source_maxwell (
  id INT,
  name STRING,
  email STRING,
  origin_database STRING METADATA FROM 'value.database' VIRTUAL,
  origin_table STRING METADATA FROM 'value.table' VIRTUAL,
  origin_ts TIMESTAMP_LTZ(3) METADATA FROM 'value.ingestion-timestamp' VIRTUAL
) WITH (
 'connector' = 'kafka',
 'topic' = 'maxwell-mysql',
 'properties.bootstrap.servers' = '192.168.1.134:9092,192.168.1.135:9092,192.168.1.136:9092',
 'properties.group.id' = 'flink_maxwell_mysql_sink_v4',
 'format' = 'maxwell-json',
 'scan.startup.mode' = 'latest-offset'
);

-- mysql sink表
CREATE TABLE sink_mysql_user (
  id INT,
  name STRING,
  email STRING,
  PRIMARY KEY (id) NOT ENFORCED
) WITH (
 'connector' = 'jdbc',
 'url' = 'jdbc:mysql://192.168.1.14:3306/test?useSSL=false&serverTimezone=Asia/Shanghai',
 'table-name' = 'target_user',
 'username' = 'hxl',
 'password' = 'mysql',
 'driver' = 'com.mysql.cj.jdbc.Driver',
 'sink.buffer-flush.max-rows' = '100',
 'sink.buffer-flush.interval' = '1s',
 'sink.max-retries' = '3'
);

-- 写入任务，maxwell-json自动处理insert/update/delete
INSERT INTO sink_mysql_user
SELECT id, name, email
FROM source_maxwell;