将kafka数据写入mysql

https://debezium.io/documentation/reference/1.7/connectors/mysql.html

Debezium MySQL源连接器配置属性

//mysql.properties
name=inventory-connector
connector.class=io.debezium.connector.mysql.MySqlConnector
database.hostname=10.17.81.212  --MySQL数据库服务器的IP地址或主机名。
database.port=3306  --MySQL数据库服务器的整数端口号。
database.user=binlog  连接到MySQL数据库服务器时使用的用户名。
database.password=Binlog-123  连接到MySQL数据库服务器时使用的密码。
database.server.id=184054

该数据库客户端的数字ID，在MySQL群集中所有当前正在运行的数据库进程中，该ID必须唯一。该连接器作为另一个服务器（具有此唯一ID）加入MySQL数据库集群，因此它可以读取binlog。默认情况下，在5400和之间会生成一个随机数6400。

database.server.name=fullfillment

逻辑名称，用于标识特定的MySQL数据库服务器/集群并为其提供名称空间。逻辑名称在所有其他连接器上都应该是唯一的，因为它用作该连接器发出的所有Kafka主题名称的前缀。默认为host:_port_，其中host是database.hostname属性的值，port是属性的值database.port

database.whitelist=inventory

与要监视的数据库名称匹配的正则表达式的可选逗号分隔列表。白名单中未包括的任何数据库名称都将从监视中排除。默认情况下，将监视所有数据库。不可database.blacklist与一起使用

database.blacklist

与数据库名称匹配的正则表达式的可选逗号分隔列表，这些名称与要从监视中排除的数据库名称匹配。黑名单中未包括的任何数据库名称都将受到监视。不可与一起使用database.whitelist。

database.history.kafka.bootstrap.servers=10.17.81.211:9092

连接器将用于建立与Kafka群集的初始连接的主机/端口对的列表。该连接将用于检索先前由连接器存储的数据库架构历史，并用于写入从源数据库读取的每个DDL语句。这应该指向Kafka Connect进程使用的同一Kafka群集。

database.history.kafka.topic=dbhistory.fullfillment

连接器将在其中存储数据库架构历史记录的Kafka主题的全名。

include.schema.changes=true

操作过程：

1.下载debezium的mysql插件

2.下载MySQL Connector plugin archive：

 https://repo1.maven.org/maven2/io/debezium/debezium-connector-mysql/0.9.5.Final/debezium-connector-mysql-0.9.5.Final-plugin.tar.gz

参考文档：https://debezium.io/docs/connectors/mysql/

1. 创建kafka 插件路径plugins：

解压 ：tar -zxvf debezium-connector-mysql-0.9.5.Final-plugin.tar.gz

1. 修改mysql 的配置  vim  /etc/my.cnf

# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin #添加这一行
binlog-format=ROW #选择row模式

server_id=1 #配置mysql replaction需要定义，不能和canal的slaveId重复

   

  添加binlog 用户权限并且授权

CREATE USER binlog IDENTIFIED BY 'Binlog-123';
GRANT SELECT, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'binlog'@'%';
-- GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'binlog'@'%';
-- GRANT SELECT, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'binlog'@'%';
-- GRANT ALL PRIVILEGES ON *.* TO 'binlog'@'%' ;
FLUSH PRIVILEGES;

5.创建mysql.properties

文件内容：

name=inventory-connector
connector.class=io.debezium.connector.mysql.MySqlConnector
database.hostname=192.168.1.102
database.port=3306
database.user=binlog
database.password=Binlog-123
database.server.id=184054
database.server.name=fullfillment
database.whitelist=inventory
database.history.kafka.bootstrap.servers=192.168.1.102:9092
database.history.kafka.topic=dbhistory.fullfillment
include.schema.changes=true

6.启动Zookeeper集群：

1. 启动kafka 集群

8.修改  kafka  config 配置 config/connect-standalone.properties

9 启动kafka 连接器

bin/connect-standalone.sh config/connect-standalone.properties   config/mysql.properties

1. 新建白名单中的数据库inventory

 11 . 查看kafka  topic

12  新建表 test查看白名单数据库

CREATE TABLE `test` (
  `id` int(11)  auto_increment,
    name  varchar(128) ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

13  监听topic  fullfillment.inventory.test

[root@hadoop102 kafka]# bin/kafka-console-consumer.sh --bootstrap-server hadoop102:9092 --from-beginning  --topic fullfillment.inventory.test

https://www.bejson.com/json/format/

14  增删改 表 test ,查看 kafka  消费者的消费

消息内容：

{
	"schema": {
		"type": "struct",
		"fields": [{
			"type": "struct",
			"fields": [{
				"type": "int32",
				"optional": false,
				"field": "id"
			}, {
				"type": "string",
				"optional": true,
				"field": "name"
			}],
			"optional": true,
			"name": "fullfillment.inventory.test.Value",
			"field": "before"
		}, {
			"type": "struct",
			"fields": [{
				"type": "int32",
				"optional": false,
				"field": "id"
			}, {
				"type": "string",
				"optional": true,
				"field": "name"
			}],
			"optional": true,
			"name": "fullfillment.inventory.test.Value",
			"field": "after"
		}, {
			"type": "struct",
			"fields": [{
				"type": "string",
				"optional": true,
				"field": "version"
			}, {
				"type": "string",
				"optional": true,
				"field": "connector"
			}, {
				"type": "string",
				"optional": false,
				"field": "name"
			}, {
				"type": "int64",
				"optional": false,
				"field": "server_id"
			}, {
				"type": "int64",
				"optional": false,
				"field": "ts_sec"
			}, {
				"type": "string",
				"optional": true,
				"field": "gtid"
			}, {
				"type": "string",
				"optional": false,
				"field": "file"
			}, {
				"type": "int64",
				"optional": false,
				"field": "pos"
			}, {
				"type": "int32",
				"optional": false,
				"field": "row"
			}, {
				"type": "boolean",
				"optional": true,
				"default": false,
				"field": "snapshot"
			}, {
				"type": "int64",
				"optional": true,
				"field": "thread"
			}, {
				"type": "string",
				"optional": true,
				"field": "db"
			}, {
				"type": "string",
				"optional": true,
				"field": "table"
			}, {
				"type": "string",
				"optional": true,
				"field": "query"
			}],
			"optional": false,
			"name": "io.debezium.connector.mysql.Source",
			"field": "source"
		}, {
			"type": "string",
			"optional": false,
			"field": "op"
		}, {
			"type": "int64",
			"optional": true,
			"field": "ts_ms"
		}],
		"optional": false,
		"name": "fullfillment.inventory.test.Envelope"
	},
	"payload": {
		"before": {
			"id": 4,
			"name": "曾纪先牛逼"
		},
		"after": null,
		"source": {
			"version": "0.9.5.Final",
			"connector": "mysql",
			"name": "fullfillment",
			"server_id": 1,
			"ts_sec": 1629607909,
			"gtid": null,
			"file": "mysql-bin.000001",
			"pos": 2238,
			"row": 0,
			"snapshot": false,
			"thread": 7,
			"db": "inventory",
			"table": "test",
			"query": null
		},
		"op": "d",
		"ts_ms": 1629607909888
	}
}