Debezium 抽取mysql数据

1、环境介绍
操作系统：centos 7.9
jdk版本：11.0.12
kafka版本：2.8.0
Debezium版本：1.6（debezium-connector-mysql-1.6.1.Final-plugin.tar.gz）
mysql版本：5.7

kafka connect 分布式部署
https://blog.csdn.net/zyj81092211/article/details/119647591
kafka connector 配置 Debezium
https://blog.csdn.net/zyj81092211/article/details/119840744

2、安装mysql
https://blog.csdn.net/zyj81092211/article/details/119852278

3、创建Debezium MySQL 连接器用户

CREATE USER 'debezium'@'%' IDENTIFIED BY '1qazXSW@';
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'debezium' IDENTIFIED BY '1qazXSW@';
FLUSH PRIVILEGES;

4、修改mysql配置文件，开启show_compatibility_56

vi /etc/my.cnf

在[mysqld]中添加如下

show_compatibility_56 = 1

5、检查log-bin选项是否已开启

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

如果显示OFF，进行下面操作：
编辑my.cnf配置文件

vi /etc/my.cnf

在[mysqld]中添加如下：

server-id = 223344
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 10

重启mysql 服务：

systemctl restart mysqld

再次检查，bin-log 已经变成ON状态

6、其他 mysql参数
编辑my.cnf配置文件

vi /etc/my.cnf

在[mysqld]中添加如下：

gtid_mode=ON
enforce_gtid_consistency=ON
interactive_timeout=60
wait_timeout=60
binlog_rows_query_log_events=ON

参数解释：
GTID:
全局事务标识符 (GTID) 唯一标识集群内服务器上发生的事务。尽管 Debezium MySQL 连接器不需要，但使用 GTID 可以简化复制并使您能够更轻松地确认主服务器和副本服务器是否一致。
enforce_gtid_consistency:
指定服务器是否通过允许执行可以以事务安全方式记录的语句来强制执行 GTID 一致性。使用 GTID 时需要。
interactive_timeout:
服务器在关闭交互式连接之前等待活动的秒数。
wait_timeout:
服务器在关闭非交互式连接之前等待活动的秒数。
binlog_rows_query_log_events:
查看SQL每个 binlog 事件的原始语句

重启mysql 服务：

systemctl restart mysqld

7、my.cnf完整配置示例

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

show_compatibility_56 = 1

server-id = 223344
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 10

gtid_mode=ON
enforce_gtid_consistency=ON
interactive_timeout=60
wait_timeout=60
binlog_rows_query_log_events=ON

8、创建连接器
官方示例和解释

curl -H "Content-Type: application/json" -X POST -d  '{
    "name": "source-mysql200", 
    "config": {
        "connector.class": "io.debezium.connector.mysql.MySqlConnector", 
        "database.hostname": "10.99.99.200", 
        "database.port": "3306", 
        "database.user": "debezium", 
        "database.password": "1qazXSW@", 
        "database.server.id": "223344", 
        "database.server.name": "mysql200", 
        "database.include.list": "source", 
        "database.history.kafka.bootstrap.servers": "kafkac01.wtown.com:9092,kafkac02.wtown.com:9092,kafkac03.wtown.com:9092", 
        "database.history.kafka.topic": "mysql-test.mysql200", 
        "include.schema.changes": "true" 
    }
}' http://kafkac01.wtown.com:8083/connectors/

新建立了两个topic

9、测试
登陆mysql，创建测试数据库source

create database source;

切换到source数据库

 use source;

创建user表

CREATE TABLE `user` (
`id`  int(128) NOT NULL AUTO_INCREMENT ,
`name`  varchar(256) NULL ,
PRIMARY KEY (`id`)
)
;

插入数据到user表中

insert into user(id,name) values(1,'zhangsan');
insert into user(id,name) values(2,'lisi');

查看topic，出现了一个新的topic --> mysql200.source.user

消费 mysql200.source.user

kafka-console-consumer.sh --bootstrap-server kafkac01.wtown.com:9092,kafkac02.wtown.com:9092,kafkac03.wtown.com:9092 --topic  mysql200.source.user  --from-beginning

将输出的json，格式化（https://www.sojson.com/），发现lisi这条数据，已经抓取到

消费mysql-test.mysql200，里面记录着ddl语句

kafka-console-consumer.sh --bootstrap-server kafkac01.wtown.com:9092,kafkac02.wtown.com:9092,kafkac03.wtown.com:9092 --topic mysql-test.mysql200  --from-beginning