FLINK CDC 3.0 demo mysql 整库同步历程(3.sql cdc同步)

结论：FLINK CDC 3.0 在flink 1.17上，不支持mysql to doris 的 demo。
1.mysql开启binlog参考debizium(https://debezium.io/documentation/reference/2.5/connectors/mysql.html#setting-up-mysql)，但上面部分是伪代码，说明意思，但不是直接可以用的命令。
相关设置如下：
CREATE TABLE c1(id int PRIMARY KEY,name varchar(50))

CREATE TABLE c2(id2 int PRIMARY KEY,name2 varchar(50))

select * from c1

SELECT variable_value as "BINARY LOGGING STATUS (log-bin) ::"

SELECT variable_value as "BINARY LOGGING STATUS (log-bin) ::"
FROM information_schema.global_variables WHERE variable_name='log_bin';

show variables like '%log_bin%';
show variables like '%binlog_rows_query_log_events%';
set global binlog_rows_query_log_events=on
select @@binlog_rows_query_log_events
select version() a

show global variables where variable_name = 'binlog_row_value_options';
set binlog_row_value_options="" ;

SELECT variable_value FROM information_schema.global_variables WHERE variable_name='server_id';

select * from metastore.DBS

--把show_compatibility_56打开
mysql> set global show_compatibility_56=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%show_compatibility_56%';

mysql> set global gtid_mode=ON
Enable enforce_gtid_consistency:

mysql> enforce_gtid_consistency=ON
Confirm the changes:

mysql> show global variables like '%GTID%';

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

select @@interactive_timeout
select @@wait_timeout

2.mysql_to_doris_cdc.yaml
################################################################################

Description: Sync mssql all tables to Doris

################################################################################
source:
type: mysql
hostname: xx.xx.40.xx
port: 3306
username: root
password: 123456
tables: flinkcdc..*

sink:
type: doris
fenodes: xx.168.100.xx:8030
username: root
password: ""

pipeline:
name: Sync mssql Database to Doris
parallelism: 1

3.整库同步不行，那就FLINK SQL
Flink SQL> SET 'execution.checkpointing.interval' = '3s';

-- register a MySQL table 'orders' in Flink SQL
Flink SQL> CREATE TABLE c1in (
id INT,
name varchar(50),
PRIMARY KEY(id) NOT ENFORCED
) WITH (
'connector' = 'mysql-cdc',
'hostname' = '172.16.40.26',
'port' = '3306',
'username' = 'root',
'password' = '123456',
'database-name' = 'flinkcdc',
'table-name' = 'c1');

-- read snapshot and binlogs from orders table
Flink SQL> SELECT * FROM c1in;

---

CREATE TABLE ftin (
id INT,
name VARCHAR(50),
PRIMARY KEY (id) NOT ENFORCED
) WITH (
'connector' = 'sqlserver-cdc',
'hostname' = 'xx.16.xx.xx',
'port' = '1433',
'username' = 'sa',
'password' = '123456',
'database-name' = 'dw_bak',
'table-name' = 'dbo.ft',
'scan.startup.mode' = 'latest-offset'
);