使用debezium同步mysql数据到kafka
环境:
kafka:kafka_2.13-3.1.0
jdk:1.8
部署方式:3个节点组成的kafka集群
1.下载介质包
wget https://maven.aliyun.com/repository/public/io/debezium/debezium-connector-mysql/1.9.7.Final/debezium-connector-mysql-1.9.7.Final-plugin.tar.gz
2.创建插件目录
每个节点都需要操作
mkdir -p /opt/kafka/connect/plugins
3.解压到插件目录
每个节点都需要操作
tar -zxvf /soft/debezium-connector-mysql-1.9.7.Final-plugin.tar.gz -C /opt/kafka/connect/plugins
4.配置文件
vi /opt/kafka/config/connect-distributed.properties
# 插件路径
plugin.path=/opt/kafka/connect/plugins
# 3节点kafka集群地址，三台机器配置一模一样
bootstrap.servers=192.168.1.134:9092,192.168.1.135:9092,192.168.1.136:9092
# 集群组ID，【三台必须完全相同！】
group.id=debezium-connect-cluster
# Connect内部存储topic，三台保持一致
config.storage.topic=connect_configs
offset.storage.topic=connect_offsets
status.storage.topic=connect_statuses
# 消息转换器
key.converter=org.apache.kafka.connect.json.JsonConverter
value.converter=org.apache.kafka.connect.json.JsonConverter
key.converter.schemas.enable=false
value.converter.schemas.enable=false
# REST服务监听，本机地址
listeners=http://0.0.0.0:8083
# 重点：每台机器填自己本机IP
rest.advertised.host.name=192.168.1.134
# node2写 192.168.1.135 node3写192.168.1.136
rest.advertised.port=8083
拷贝到另外的节点
scp /opt/kafka/config/connect-distributed.properties root@192.168.1.135:/opt/kafka/config/
scp /opt/kafka/config/connect-distributed.properties root@192.168.1.136:/opt/kafka/config/
拷贝过去后需要修改的地方
rest.advertised.host.name=192.168.1.135
rest.advertised.host.name=192.168.1.136
5.启动
每台节点执行
cd /opt/kafka
/opt/kafka/bin/connect-distributed.sh /opt/kafka/config/connect-distributed.properties
nohup方式
cd /opt/kafka
nohup /opt/kafka/bin/connect-distributed.sh /opt/kafka/config/connect-distributed.properties > connect.log 2>&1 &
查看当前节点
curl http://192.168.1.134:8083/connector-plugins
curl http://192.168.1.135:8083/connector-plugins
curl http://192.168.1.136:8083/connector-plugins
6.准备同步文件
准备连接器json文件
vi /opt/kafka/connect/mysql-cdc.json
{
"name": "mysql-57-cdc",
"config": {
"connector.class": "io.debezium.connector.mysql.MySqlConnector",
"tasks.max": "1",
"database.hostname": "192.168.1.14",
"database.port": "3306",
"database.user": "flink",
"database.password": "flink",
"database.server.id": "184054",
"database.server.name": "mytestdb",
"database.include.list": "db_admin",
"table.include.list": "db_admin.user,db_admin.user01",
"database.history.kafka.bootstrap.servers": "192.168.1.134:9092,192.168.1.135:9092,192.168.1.136:9092",
"database.history.kafka.topic": "mytestdb_history",
"snapshot.mode": "initial",
"snapshot.locking.minimal": "true",
"database.allowPublicKeyRetrieval": "true"
}
}
创建topic:mytestdb.history
在kafka其中一台机器上执行
/opt/kafka/bin/kafka-topics.sh --create --topic mytestdb_history \
--bootstrap-server 192.168.1.134:9092,192.168.1.135:9092,192.168.1.136:9092 \
--partitions 1 --replication-factor 3
7.同步账号赋予最小权限
mysql> show grants for flink@'%';
+---------------------------------------------------------------------------+
| Grants for flink@% |
+---------------------------------------------------------------------------+
| GRANT RELOAD, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'flink'@'%' |
| GRANT SELECT, LOCK TABLES, SHOW VIEW ON `db_admin`.* TO 'flink'@'%' |
+---------------------------------------------------------------------------+
2 rows in set (0.00 sec)
8.提交作用
curl -X POST -H "Content-Type:application/json" -d @/opt/kafka/connect/mysql-cdc.json http://192.168.1.134:8083/connectors
查看运行情况
[root@host134 connect]# curl -s http://192.168.1.134:8083/connectors/mysql-57-cdc/status | jq .
{
"name": "mysql-57-cdc",
"connector": {
"state": "RUNNING",
"worker_id": "192.168.1.134:8083"
},
"tasks": [
{
"id": 0,
"state": "RUNNING",
"worker_id": "192.168.1.134:8083"
}
],
"type": "source"
}
9.若运行错误重新提交
##删除之前的job
curl -X DELETE http://192.168.1.134:8083/connectors/mysql-57-cdc
##删除topic
/opt/kafka/bin/kafka-topics.sh --delete --topic mytestdb_history --bootstrap-server 192.168.1.134:9092,192.168.1.135:9092,192.168.1.136:9092
##重建topic
/opt/kafka/bin/kafka-topics.sh --create --topic mytestdb_history \
--bootstrap-server 192.168.1.134:9092,192.168.1.135:9092,192.168.1.136:9092 \
--partitions 1 --replication-factor 3
##重新提交任务
curl -X POST -H "Content-Type:application/json" -d @/opt/kafka/connect/mysql-cdc.json http://192.168.1.134:8083/connectors
10.查看同步数据
##表user
/opt/kafka/bin/kafka-console-consumer.sh \
--bootstrap-server 192.168.1.134:9092,192.168.1.135:9092,192.168.1.136:9092 \
--topic mytestdb.db_admin.user \
--from-beginning
##表user01
/opt/kafka/bin/kafka-console-consumer.sh \
--bootstrap-server 192.168.1.134:9092,192.168.1.135:9092,192.168.1.136:9092 \
--topic mytestdb.db_admin.user01 \
--from-beginning
11.数据验证
dml 可同步
ddl
alter table user01 add column name1 varchar(32);
ddl会同步到这个topic:mytestdb_history
/opt/kafka/bin/kafka-console-consumer.sh \
--bootstrap-server 192.168.1.134:9092,192.168.1.135:9092,192.168.1.136:9092 \
--topic mytestdb_history --from-beginning
{
"source" : {
"server" : "mytestdb"
},
"position" : {
"transaction_id" : null,
"ts_sec" : 1790578986,
"file" : "binlog.000299",
"pos" : 82614449,
"gtids" : "3f38e97d-cef5-11ee-a41e-d094667047fb:1-1,89f22099-6f76-11e9-b9b0-d094667047fb:1-4776526",
"server_id" : 1
},
"databaseName" : "db_admin",
"ddl" : "alter table user01 add column name1 varchar(32)",
"tableChanges" : [ {
"type" : "ALTER",
"id" : "\"db_admin\".\"user01\"",
"table" : {
"defaultCharsetName" : "utf8mb4",
"primaryKeyColumnNames" : [ "id" ],
"columns" : [ {
"name" : "id",
"jdbcType" : -5,
"typeName" : "BIGINT",
"typeExpression" : "BIGINT",
"charsetName" : null,
"length" : 20,
"position" : 1,
"optional" : false,
"autoIncremented" : true,
"generated" : true,
"comment" : null,
"hasDefaultValue" : false,
"enumValues" : [ ]
}, {
"name" : "name",
"jdbcType" : 12,
"typeName" : "VARCHAR",
"typeExpression" : "VARCHAR",
"charsetName" : "utf8mb4",
"length" : 255,
"position" : 2,
"optional" : true,
"autoIncremented" : false,
"generated" : false,
"comment" : null,
"hasDefaultValue" : true,
"enumValues" : [ ]
}, {
"name" : "email",
"jdbcType" : 12,
"typeName" : "VARCHAR",
"typeExpression" : "VARCHAR",
"charsetName" : "utf8mb4",
"length" : 255,
"position" : 3,
"optional" : true,
"autoIncremented" : false,
"generated" : false,
"comment" : null,
"hasDefaultValue" : true,
"enumValues" : [ ]
}, {
"name" : "name1",
"jdbcType" : 12,
"typeName" : "VARCHAR",
"typeExpression" : "VARCHAR",
"charsetName" : "utf8mb4",
"length" : 32,
"position" : 4,
"optional" : true,
"autoIncremented" : false,
"generated" : false,
"comment" : null,
"hasDefaultValue" : true,
"enumValues" : [ ]
} ]
},
"comment" : null
} ]
}
浙公网安备 33010602011771号