Real-time synchronization

This article provides implementation ideas, configuration examples, and verification methods for MySQL real-time synchronization scenarios, which can be used as a reference for actual project configuration.

Real-time synchronization of data from the result table in MySQL to the target table

Case

Synchronize data in cdc_source to cdc_target in real time in MySQL

Premise

• Binlog service needs to be enabled
• User authorization

GRANT SELECT, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'ispong'@'%';  
GRANT RELOAD ON *.* TO 'ispong'@'%';  
FLUSH PRIVILEGES;

solution

Create a Flink SQL job type and add flink-sql-connector-MySQL-cdc dependency

• flink-sql-connector-MySQL-cdc-3.2.1.jar download

CREATE TABLE from_table
(
    username STRING,
    age      INT
) WITH ( 'connector' = 'mysql-cdc',
      'hostname' = 'localhost',
      'port' = '30306',
      'username' = 'ispong',
      'password' = 'ispong123',
      'database-name' = 'isxcode_db',
      'table-name' = 'cdc_source',
      'scan.incremental.snapshot.enabled' = 'false',
      'server-time-zone' = 'UTC',
      'scan.startup.mode' = 'latest-offset'
      );

CREATE TABLE target_table
(
    username STRING,
    age      INT,
    PRIMARY KEY (username) NOT ENFORCED
) WITH ( 'connector' = 'jdbc',
      'url' = 'jdbc:mysql://localhost:30306/isxcode_db?useSSL=false&allowPublicKeyRetrieval=true',
      'driver' = 'com.mysql.cj.jdbc.Driver',
      'table-name' = 'cdc_target',
      'username' = 'ispong',
      'password' = 'ispong123');

INSERT INTO target_table
select *
from from_table;