使用flink同步mysql-to-mysql

环境:
OS:uubuntu 26
flink:1.20
mysql:5.7

1.准备flink-sql文件
vi /opt/flink/flinksql/mysql2mysql.sql

root@dify:/opt/flink/flinksql# more mysql2mysql.sql 
-- 开启Checkpoint，保障断点续传，不丢数据
SET 'pipeline.name' = '测试';
SET 'execution.checkpointing.interval' = '10s';
SET 'table.exec.sink.upsert-materialize' = 'NONE';


-- 【源端CDC表】 你的user表
CREATE TABLE mysql_source_user (
            id BIGINT,
            name STRING,
            email STRING,
            create_time TIMESTAMP(3),
            modify_time TIMESTAMP(3),
            PRIMARY KEY(id) NOT ENFORCED
) WITH (
    'connector' = 'mysql-cdc',
    'hostname' = '192.168.1.14',
    'port' = '3306',
    'username' = 'flink',
    'password' = 'flink',
    'database-name' = 'db_admin',
    'table-name' = 'user',
    'server-id' = '5501', -- 全局唯一，每个任务不能重复
    'server-time-zone' = 'Asia/Shanghai',
    'scan.startup.mode' = 'initial', -- initial：全量快照 + 后续增量binlog；如果只要增量改为 latest-offset
    'scan.incremental.snapshot.enabled' = 'true' -- 无锁增量快照，降低源库压力
);

-- 【目标MySQL Sink表】
CREATE TABLE mysql_sink_user (
            id BIGINT,
            name STRING,
            email STRING,
            create_time TIMESTAMP(3),
            modify_time TIMESTAMP(3),
            PRIMARY KEY(id) NOT ENFORCED
) WITH (
    'connector' = 'jdbc',
    'url' = 'jdbc:mysql://192.168.1.113:3306/db_admin?useUnicode=true&characterEncoding=UTF-8',
    'table-name' = 'user',
    'username' = 'hxl01',
    'password' = 'mysql',
    'driver' = 'com.mysql.cj.jdbc.Driver',
    -- JDBC批量刷写优化
    'sink.buffer-flush.max-rows' = '1000',
    'sink.buffer-flush.interval' = '1s',
    'sink.max-retries' = '3'
);

-- 流数据插入：source -> sink
INSERT INTO mysql_sink_user
SELECT id, name, email,create_time,modify_time FROM mysql_source_user;

3.创建账号
源端:
CREATE USER 'flink'@'%' IDENTIFIED BY 'flink';
GRANT REPLICATION SLAVE,REPLICATION CLIENT,SELECT ON *.* TO flink@'%';
FLUSH PRIVILEGES;

目的端的账号之前存在的就不需要创建了

4.启动job
/opt/flink/bin/sql-client.sh -f /opt/flink/flinksql/mysql2mysql.sql

5.源端添加字段
use db_admin
alter table user add column name1 varchar(32);

写入数据
insert into user(name,name1,email) values('uuuu','uuuuu','uuuu@qq.com');

目的端目前没有新增字段，同步过去的数据没有新字段的值

6.目标端新增字段并修改配置

6.1 查看当前的job

root@dify:/opt/flink/flinksql# /opt/flink/bin/flink list
Waiting for response...
------------------ Running/Restarting Jobs -------------------
17.09.2026 10:43:41 : 0284ce3aeb6012354d214927b1d0101b : insert-into_default_catalog.default_database.mysql_sink_user (RUNNING)
--------------------------------------------------------------
No scheduled jobs.

6.2 创建用于保存的savepoint目录
mkdir -p /opt/flink/savepoints
chmod 777 /opt/flink/savepoints

6.3 停掉job

root@dify:/opt/flink/flinksql# /opt/flink/bin/flink stop --savepoint file:///opt/flink/savepoints  0284ce3aeb6012354d214927b1d0101b
Suspending job "0284ce3aeb6012354d214927b1d0101b" with a CANONICAL savepoint.
Triggering stop-with-savepoint for job 0284ce3aeb6012354d214927b1d0101b.
Waiting for response...
Savepoint completed. Path: file:/opt/flink/savepoints/savepoint-0284ce-e08238eab630

需要记录下这个Savepoint file:/opt/flink/savepoints/savepoint-0284ce-e08238eab630
下面启动需要用到

6.4 目的端新加字段,并修改同步的sql
alter table user add column name1 varchar(32);

6.5 修改同步配置文件
vi /opt/flink/flinksql/mysql2mysql.sql

root@dify:/opt/flink/flinksql# more mysql2mysql.sql 
-- 开启Checkpoint，保障断点续传，不丢数据
SET 'pipeline.name' = '测试';
SET 'execution.savepoint.path' = 'file:/opt/flink/savepoints/savepoint-0284ce-e08238eab630';
SET 'execution.checkpointing.interval' = '10s';
SET 'table.exec.sink.upsert-materialize' = 'NONE';


-- 【源端CDC表】 你的user表
CREATE TABLE mysql_source_user (
            id BIGINT,
            name STRING,
            email STRING,
            name1 STRING,
            create_time TIMESTAMP(3),
            modify_time TIMESTAMP(3),
            PRIMARY KEY(id) NOT ENFORCED
) WITH (
    'connector' = 'mysql-cdc',
    'hostname' = '192.168.1.14',
    'port' = '3306',
    'username' = 'flink',
    'password' = 'flink',
    'database-name' = 'db_admin',
    'table-name' = 'user',
    'server-id' = '5501', -- 全局唯一，每个任务不能重复
    'server-time-zone' = 'Asia/Shanghai',
    'scan.startup.mode' = 'initial', -- initial：全量快照 + 后续增量binlog；如果只要增量改为 latest-offset
    'scan.incremental.snapshot.enabled' = 'true' -- 无锁增量快照，降低源库压力
);

-- 【目标MySQL Sink表】
CREATE TABLE mysql_sink_user (
            id BIGINT,
            name STRING,
            email STRING,
            name1 STRING,
            create_time TIMESTAMP(3),
            modify_time TIMESTAMP(3),
            PRIMARY KEY(id) NOT ENFORCED
) WITH (
    'connector' = 'jdbc',
    'url' = 'jdbc:mysql://192.168.1.113:3306/db_admin?useUnicode=true&characterEncoding=UTF-8',
    'table-name' = 'user',
    'username' = 'hxl01',
    'password' = 'mysql',
    'driver' = 'com.mysql.cj.jdbc.Driver',
    -- JDBC批量刷写优化
    'sink.buffer-flush.max-rows' = '1000',
    'sink.buffer-flush.interval' = '1s',
    'sink.max-retries' = '3'
);

-- 流数据插入：source -> sink
INSERT INTO mysql_sink_user
SELECT id, name, email,name1,create_time,modify_time FROM mysql_source_user;

或是不加下面的这个命令
SET 'execution.savepoint.path' = 'file:/opt/flink/savepoints/savepoint-0284ce-e08238eab630';

直接恢复的时候带上参数execution.savepoint.path

/opt/flink/bin/sql-client.sh \
-D execution.savepoint.path="file:/opt/flink/savepoints/savepoint-c21bcf-d940ab2148ad" \
-f /opt/flink/flinksql/mysql2mysql.sql

6.6 重新启动
/opt/flink/bin/sql-client.sh -f /opt/flink/flinksql/mysql2mysql.sql

这样的话新增的字段就可以同步过去了

6.7 修改同步配置文件去掉如下项
vi /opt/flink/flinksql/mysql2mysql.sql
##SET 'execution.savepoint.path' = 'file:/opt/flink/savepoints/savepoint-0284ce-e08238eab630';

posted @ 2026-09-17 10:46 slnngk 阅读(7) 评论(0) 收藏举报

刷新页面返回顶部

使用flink同步mysql-to-mysql

公告