离线安装Flink，使用Flink SQL同步数据。

前情提要

同步mysqlA中表AA 至 mysqlB中表BA，此处读采用mysql-cdc，写采用JDBC

资源准备

• 下载相应jar包

  • flink-sql-connector-mysql-cdc-2.3.0.jar
  • flink-connector-jdbc_2.12-1.14.6.jar
  • mysql-connector-java-8.0.30.jar
    将下载好的包，复制到flink-1.16.1/lib

• 数据库开启GTID

  找到my.cnf，在[mysqld]中加入如下信息

  [mysqld]
  ……
  server-id=1
  log-bin=mysql-bin
  binlog-format=row
  gtid-mode=ON
  enforce-gtid-consistency=true
  log-slave-updates=ON
  

• 准备数据

  mysqlA中信息

  CREATE DATABASE test_db;
  USE test_db;
  CREATE TABLE orders (
    order_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    order_date DATETIME NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 5) NOT NULL,
    product_id INTEGER NOT NULL,
    order_status BOOLEAN NOT NULL -- Whether order has been placed
  ) AUTO_INCREMENT = 10001;
  
  INSERT INTO orders
  VALUES (default, '2020-07-30 10:08:22', 'Jark', 50.50, 102, false),
         (default, '2020-07-30 10:11:09', 'Sally', 15.00, 105, false),
         (default, '2020-07-30 12:00:30', 'Edward', 25.25, 106, false);
  

  mysqlB中信息

  CREATE DATABASE test_dw;
  USE test_dw;
  CREATE TABLE orders (
    order_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    order_date DATETIME NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 5) NOT NULL,
    product_id INTEGER NOT NULL,
    order_status BOOLEAN NOT NULL -- Whether order has been placed
  )
  

离线安装Flink 文档

• 安装java 11

  # mac 安装 java11
  $  brew install java11
  $  sudo ln -sfn /usr/local/opt/openjdk@11/libexec/openjdk.jdk /Library/Java/JavaVirtualMachines/openjdk-11.jdk
  $  java -version
  openjdk version "11.0.16.1" 2022-08-12
  OpenJDK Runtime Environment Homebrew (build 11.0.16.1+0)
  OpenJDK 64-Bit Server VM Homebrew (build 11.0.16.1+0, mixed mode)
  

• 下载 release 1.16.1 并解压

  $ tar -xzf flink-1.16.1-bin-scala_2.12.tgz
  $ cd flink-1.16.1
  

• 启动集群

  $ cd flink-1.16.1
  # 启动集群
  $ ./bin/start-cluster.sh
  # 停止
  $ ./bin/stop-cluster.sh
  

  启动集群之后访问：http://localhost:8081/即可看到web UI

使用Fink SQL，零代码实现同步

• 启动 Fink SQL

  $ cd flink-1.16.1
  $ ./bin/sql-client.sh
  
                                     ▒▓██▓██▒
                                 ▓████▒▒█▓▒▓███▓▒
                              ▓███▓░░        ▒▒▒▓██▒  ▒
                            ░██▒   ▒▒▓▓█▓▓▒░      ▒████
                            ██▒         ░▒▓███▒    ▒█▒█▒
                              ░▓█            ███   ▓░▒██
                                ▓█       ▒▒▒▒▒▓██▓░▒░▓▓█
                              █░ █   ▒▒░       ███▓▓█ ▒█▒▒▒
                              ████░   ▒▓█▓      ██▒▒▒ ▓███▒
                           ░▒█▓▓██       ▓█▒    ▓█▒▓██▓ ░█░
                     ▓░▒▓████▒ ██         ▒█    █▓░▒█▒░▒█▒
                    ███▓░██▓  ▓█           █   █▓ ▒▓█▓▓█▒
                  ░██▓  ░█░            █  █▒ ▒█████▓▒ ██▓░▒
                 ███░ ░ █░          ▓ ░█ █████▒░░    ░█░▓  ▓░
                ██▓█ ▒▒▓▒          ▓███████▓░       ▒█▒ ▒▓ ▓██▓
             ▒██▓ ▓█ █▓█       ░▒█████▓▓▒░         ██▒▒  █ ▒  ▓█▒
             ▓█▓  ▓█ ██▓ ░▓▓▓▓▓▓▓▒              ▒██▓           ░█▒
             ▓█    █ ▓███▓▒░              ░▓▓▓███▓          ░▒░ ▓█
             ██▓    ██▒    ░▒▓▓███▓▓▓▓▓██████▓▒            ▓███  █
            ▓███▒ ███   ░▓▓▒░░   ░▓████▓░                  ░▒▓▒  █▓
            █▓▒▒▓▓██  ░▒▒░░░▒▒▒▒▓██▓░                            █▓
            ██ ▓░▒█   ▓▓▓▓▒░░  ▒█▓       ▒▓▓██▓    ▓▒          ▒▒▓
            ▓█▓ ▓▒█  █▓░  ░▒▓▓██▒            ░▓█▒   ▒▒▒░▒▒▓█████▒
             ██░ ▓█▒█▒  ▒▓▓▒  ▓█                █░      ░░░░   ░█▒
             ▓█   ▒█▓   ░     █░                ▒█              █▓
              █▓   ██         █░                 ▓▓        ▒█▓▓▓▒█░
               █▓ ░▓██░       ▓▒                  ▓█▓▒░░░▒▓█░    ▒█
                ██   ▓█▓░      ▒                    ░▒█▒██▒      ▓▓
                 ▓█▒   ▒█▓▒░                         ▒▒ █▒█▓▒▒░░▒██
                  ░██▒    ▒▓▓▒                     ▓██▓▒█▒ ░▓▓▓▓▒█▓
                    ░▓██▒                          ▓░  ▒█▓█  ░░▒▒▒
                        ▒▓▓▓▓▓▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒░░▓▓  ▓░▒█░
  
      ______ _ _       _       _____  ____  _         _____ _ _            _  BETA
     |  ____| (_)     | |     / ____|/ __ \| |       / ____| (_)          | |
     | |__  | |_ _ __ | | __ | (___ | |  | | |      | |    | |_  ___ _ __ | |_
     |  __| | | | '_ \| |/ /  \___ \| |  | | |      | |    | | |/ _ \ '_ \| __|
     | |    | | | | | |   <   ____) | |__| | |____  | |____| | |  __/ | | | |_
     |_|    |_|_|_| |_|_|\_\ |_____/ \___\_\______|  \_____|_|_|\___|_| |_|\__|
  
          Welcome! Enter 'HELP;' to list all available commands. 'QUIT;' to exit.
  
  Command history file path: /Users/zhangkai/.flink-sql-history
  
  Flink SQL>
  

• 设置checkpoint

  • 参考文档1
  • 参考文档2

  # 每隔3秒做一次 checkpoint 生产建议5-10分钟
  Flink SQL> SET execution.checkpointing.interval = 3s;
  

• 使用 Flink SQL CLI 创建对应的表，用于同步这些底层数据库表的数据

# 读取mysqlA中信息
CREATE TABLE orders (
   order_id INT,
   order_date TIMESTAMP(0),
   customer_name STRING,
   price DECIMAL(10, 5),
   product_id INT,
   order_status BOOLEAN,
   PRIMARY KEY (order_id) NOT ENFORCED
 ) WITH (
   'connector' = 'mysql-cdc',
   'hostname' = 'localhost',
   'port' = '3306',
   'username' = 'root',
   'password' = 'secret',
   'database-name' = 'test_db',
   'table-name' = 'orders'
 );

• 创建 dw_orders 表， 用来将关联后的订单数据写入 mysqlB 中

# 用于写入mysqlB 的过度表
CREATE TABLE dw_orders (
   order_id INT,
   order_date TIMESTAMP(0),
   customer_name STRING,
   price DECIMAL(10, 5),
   product_id INT,
   order_status BOOLEAN,
   PRIMARY KEY (order_id) NOT ENFORCED
 ) WITH (
   'connector' = 'jdbc',
   'url' = 'jdbc:mysql://localhost:3306/test_dw',
   'username' = 'root',
   'password' = 'secret',
   'table-name' = 'orders'
 );

• 执行写入 mysqlB 中

INSERT INTO dw_orders SELECT * FROM orders;

• 至此，test_db.orders 增删改都会同步至 test_dw.orders中

• 总的来说就是

  mysql-cdc 读取mysqlA中信息，jdbc 过度写入mysqlB中。