利用Kafka-connect单机模式实现mysql数据库存储到topic，并从topic取数据到mysql数据库

一、搭建kafka

二、下载connect jar包和mysql-connect到kafka目录下的lib文件夹并配置connect-standalone.properties

cd /usr/local/kafka/config
vi connect-standalone.properties
#内容如下
bootstrap.servers={ip}:9092
key.converter=org.apache.kafka.connect.json.JsonConverter
value.converter=org.apache.kafka.connect.json.JsonConverter
key.converter.schemas.enable=true
value.converter.schemas.enable=true
offset.storage.file.filename=/tmp/connect.offsets
offset.flush.interval.ms=10000

三、在kafka目录下的config下编写source-mysql.properties文件(从mysql取数据到topic)

记得先关防火墙

#建表
CREATE TABLE `users_input` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
CREATE TABLE `users_output` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;

cd /usr/local/kafka/config
vi source-mysql.properties

内容如下

name=test-source-mysql-jdbc-autoincrement
connector.class=io.confluent.connect.jdbc.JdbcSourceConnector
tasks.max=1
connection.url=jdbc:mysql://{ip}:3306/test?user=root&password=root&serverTimezone=GMT%2B8
mode=incrementing
incrementing.column.name=uid
table.whitelist=users_input
topic.prefix=test-mysql-

四、运行connect单机模式

#创建主题
bin/kafka-topics.sh --create --zookeeper {ip}:2181 --replication-factor 2 --partitions 4 --topic test-mysql-users_input
#单机模式运行
cd /usr/local/kafka/bin
./connect-standalone.sh ../config/connect-standalone.properties ../config/source-mysql.properties 
#往数据库添加数据
insert into users_input(`name`, `age`) values('小明', 15);
insert into users_input(`name`, `age`) values('小白', 13);
insert into users_input(`name`, `age`) values('小李', 17);
#查看
 kafka-console-consumer.sh --bootstrap-server {ip}:9092 --topic test-mysql-test --from-beginning

成功传输

五、在kafka目录下的config下编写sink-mysql.properties文件(从topic取数据到mysql)

cd /usr/local/kafka/config
vi sink-mysql.properties
#内容如下
name=test-sink
connector.class=io.confluent.connect.jdbc.JdbcSinkConnector
tasks.max=1
topics=test-mysql-users_input
connection.url=jdbc:mysql://{ip}:3306/test?user=root&password=root&serverTimezone=GMT%2B8
auto.create=false
pk.mode=record_value
pk.fields=uid
table.name.format=users_output

六、运行connect单机模式

cd /usr/local/kafka/bin
./connect-standalone.sh ../config/connect-standalone.properties ../config/sink-mysql.properties 

成功传输

一起启动，避免端口占用

./connect-standalone.sh ../config/connect-standalone.properties ../config/sink-mysql.properties  ../config/source-mysql.properties