前情提要
同步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
# 每隔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中。