目录
1、利用Binlog和Kafka实时同步mysql数据到Elasticsearch(一) - 开启Binlog日志
2、利用Binlog和Kafka实时同步mysql数据到Elasticsearch(二) - 安装并运行Kafka
3、利用Binlog和Kafka实时同步mysql数据到Elasticsearch(三) - Binlog日志生产消息到Kafka
4、利用Binlog和Kafka实时同步mysql数据到Elasticsearch(四) - 消费Kafka消息同步数据到ES
前言
- 项目模块
BinlogMiddleware
1、binlog中间件，负责解析binlog，把变动的数据以json形式发送到kafka队列。
KafkaMiddleware
2、kafka中间件，负责消费kafka队列中的Message，把数据写入Elasticsearch中。
- 基础服务
（1）Mysql
（2）Kafka（用于存放mysql变动消息，存放于Kafka队列）
（3）Elasticsearch
- 项目源码
码云：https://gitee.com/OrgXxxx/SyncMysqlToElasticsearch
简介：
KafkaMiddleware服务主要负责消费Kafka队列消息，并将其同步到Elastcsearch（及Kafka消费者）。
- 本示例模拟监听teemoliu数据库的user、role表。为了方便表结构设计的很简单，均只含有id、name两个属性。
- 中间件读取Kafka队列的消息格式如下：
{"event":"teemoliu.user.update","value":[1,"TeemoLiu"]}
{"event":"teemoliu.role.insert","value":[1,"管理员"]}
-
项目结构如下：
image.png
1、导入maven引用
<!--kafka-->
<dependency>
<groupId>org.springframework.kafka</groupId>
<artifactId>spring-kafka</artifactId>
<version>2.2.4.RELEASE</version>
</dependency>
<!-- elasticsearch http api client -->
<dependency>
<groupId>io.searchbox</groupId>
<artifactId>jest</artifactId>
<version>5.3.3</version>
</dependency>
<!--fastjson-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.49</version>
</dependency>
2、配置文件如下：
#停用服务端口
spring.main.web-environment=false
#============== kafka ===================
# 指定kafka 代理地址，可以多个
spring.kafka.bootstrap-servers=localhost:9092
#=============== consumer =======================
# 指定默认消费者group id
spring.kafka.consumer.group-id=consumer1
spring.kafka.consumer.auto-offset-reset=latest
#spring.kafka.consumer.auto-offset-reset=earliest
spring.kafka.consumer.enable-auto-commit=true
#spring.kafka.consumer.auto-commit-interval=100
# 指定消息key和消息体的编解码方式
spring.kafka.consumer.key-deserializer=org.apache.kafka.common.serialization.StringDeserializer
spring.kafka.consumer.value-deserializer=org.apache.kafka.common.serialization.StringDeserializer
# 消息JSON格式化模板
es.data.format.user={"id":"0","name":"1"}
es.data.format.role={"id":"0","name":"1"}
3、初始化Jest客户端
public class EsJestClient {
private static JestClient client;
/**
* 获取客户端
*
* @return jestclient
*/
public static synchronized JestClient getClient() {
if (client == null) {
build();
}
return client;
}
/**
* 关闭客户端
*/
public static void close(JestClient client) {
if (!Objects.isNull(client)) {
try {
client.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 建立连接
*/
private static void build() {
JestClientFactory factory = new JestClientFactory();
factory.setHttpClientConfig(
new HttpClientConfig
.Builder(Config.ES_HOST)
.multiThreaded(true)
//一个route 默认不超过2个连接 路由是指连接到某个远程注解的个数。总连接数=route个数 * defaultMaxTotalConnectionPerRoute
.defaultMaxTotalConnectionPerRoute(2)
//所有route连接总数
.maxTotalConnection(2)
.connTimeout(10000)
.readTimeout(10000)
.gson(new GsonBuilder()
.setDateFormat("yyyy-MM-dd HH:mm:ss")
.create())
.build()
);
client = factory.getObject();
}
}
4、实现Kafka批量消费
@Configuration
@EnableKafka
public class KafkaConsumerConfig {
@Value("${spring.kafka.consumer.group-id}")
String groupId;
@Value("${spring.kafka.bootstrap-servers}")
String bootstrapServers;
@Value("${spring.kafka.consumer.auto-offset-reset}")
String autoOffsetReset;
@Bean
KafkaListenerContainerFactory<?> batchFactory() {
ConcurrentKafkaListenerContainerFactory<String, String> factory = new
ConcurrentKafkaListenerContainerFactory<>();
factory.setConsumerFactory(new DefaultKafkaConsumerFactory<>(consumerConfigs()));
factory.setBatchListener(true);
return factory;
}
@Bean
public Map<String, Object> consumerConfigs() {
Map<String, Object> props = new HashMap<>();
props.put(ConsumerConfig.GROUP_ID_CONFIG, groupId);
props.put(ConsumerConfig.AUTO_OFFSET_RESET_CONFIG, autoOffsetReset);
props.put(ConsumerConfig.BOOTSTRAP_SERVERS_CONFIG, bootstrapServers);
props.put(ConsumerConfig.MAX_POLL_RECORDS_CONFIG, 100);//每一批数量
props.put(ConsumerConfig.AUTO_COMMIT_INTERVAL_MS_CONFIG, "100");
props.put(ConsumerConfig.SESSION_TIMEOUT_MS_CONFIG, 120000);
props.put(ConsumerConfig.REQUEST_TIMEOUT_MS_CONFIG, 180000);
props.put(ConsumerConfig.KEY_DESERIALIZER_CLASS_CONFIG, StringDeserializer.class);
props.put(ConsumerConfig.VALUE_DESERIALIZER_CLASS_CONFIG, StringDeserializer.class);
return props;
}
}
5、实现ES 通用业务逻辑层
@Service
public class ESService{
private JestClient client;
public ESService(JestClient client) {
this.client = client;
}
public boolean update(String id, String esType, Object object) {
Index index = new Index.Builder(object).index(Config.ES_INDICES).type(esType).id(id).refresh(true).build();
try {
JestResult result = client.execute(index);
return result != null && result.isSucceeded();
} catch (Exception ignore) {
}
return false;
}
public Index getUpdateIndex(String id, String esType, Object object) {
return new Index.Builder(object).index(Config.ES_INDICES).type(esType).id(id).refresh(true).build();
}
public Delete getDeleteIndex(String id, String esType) {
return new Delete.Builder(id).index(Config.ES_INDICES).type(esType).build();
}
public boolean executeESClientRequest(List indexList, String esType) {
Bulk bulk = new Bulk.Builder()
.defaultIndex(Config.ES_INDICES)
.defaultType(esType)
.addAction(indexList)
.build();
indexList.clear();
try {
JestResult result = client.execute(bulk);
return result != null && result.isSucceeded();
} catch (Exception ignore) {
}
return false;
}
public boolean delete(String id, String esType) {
try {
DocumentResult result = client.execute(new Delete.Builder(id)
.index(Config.ES_INDICES)
.type(esType)
.build());
return result.isSucceeded();
} catch (Exception e) {
throw new RuntimeException("delete exception", e);
}
}
}
6、实现消费逻辑
@Component
public class JsonConsumer {
@Value("${es.data.format.user}")
String userFormat;
@Value("${es.data.format.role}")
String roleFormat;
JestClient client = EsJestClient.getClient();
ESService documentDao = new ESService(client);
@KafkaListener(topics = Config.KAFKA_JSON_TOPICS, id = Config.KAFKA_JSON_ID, containerFactory = "batchFactory")
public void listen(List<ConsumerRecord<?, ?>> list) {
List<String> messages = new ArrayList<>();
for (ConsumerRecord<?, ?> record : list) {
Optional<?> kafkaMessage = Optional.ofNullable(record.value());
// 获取消息
kafkaMessage.ifPresent(o -> messages.add(o.toString()));
}
if (messages.size() > 0) {
// 更新索引
updateES(messages);
}
try {
Thread.sleep(1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
/**
* 获取ES的TYPE
*
* @param tableName
* @return
*/
private String getESType(String tableName) {
String esType = "";
switch (tableName) {
case "role": {
esType = Config.ES_ROLE_TYPE;
break;
}
case "user": {
esType = Config.ES_USER_TYPE;
break;
}
}
return esType;
}
/**
* 获取消息JSON解析格式
*
* @param tableName
* @return
*/
private String getJsonFormat(String tableName) {
String format = "";
switch (tableName) {
case "role": {
format = roleFormat;
break;
}
case "user": {
format = userFormat;
break;
}
}
return format;
}
/**
* 获取解析后的ES对象
*
* @param message
* @param tableName
* @return
*/
private JSONObject getESObject(JSONArray message, String tableName) {
JSONObject resultObject = new JSONObject();
String format = getJsonFormat(tableName);
if (!format.isEmpty()) {
JSONObject jsonFormatObject = JSON.parseObject(format);
for (String key : jsonFormatObject.keySet()) {
String[] formatValues = jsonFormatObject.getString(key).split(",");
if (formatValues.length < 2) {
resultObject.put(key, message.get(jsonFormatObject.getInteger(key)));
} else {
Object object = message.get(Integer.parseInt(formatValues[0]));
if (object == null) {
String[] array = {};
resultObject.put(key, array);
} else {
String objectStr = message.get(Integer.parseInt(formatValues[0])).toString();
String[] result = objectStr.split(formatValues[1]);
resultObject.put(key, result);
}
}
}
}
return resultObject;
}
/**
* 更新ES索引
*
* @param messages
*/
private void updateES(List<String> messages) {
List<Index> updateUserList = new ArrayList<>();
List<Index> updateRoleList = new ArrayList<>();
List<Delete> deleteUserList = new ArrayList<>();
List<Delete> deleteRoleList = new ArrayList<>();
for (String message : messages) {
JSONObject result = null;
try {
result = JSON.parseObject(message);
} catch (Exception e) {
continue;
}
// 获取事件类型 event:"wtv3.videos.insert"
String event = (String) result.get("event");
String[] eventArray = event.split("\\.");
String tableName = eventArray[1];
String eventType = eventArray[2];
// 获取具体数据
JSONArray valueStr = (JSONArray) result.get("value");
// 转化为对应格式的json字符串
JSONObject object = getESObject(valueStr, tableName);
// 获取ES的type
String esType = getESType(tableName);
switch (eventType) {
case "insert": {
appendUpdateList(updateUserList, updateRoleList, object, esType);
break;
}
case "update": {
// 更新videos
appendUpdateList(updateUserList, updateRoleList, object, esType);
break;
}
case "delete": {
// 删除videos
appendDeleteList(deleteUserList, deleteRoleList, object, esType);
break;
}
}
}
if (updateUserList.size() > 0) {
documentDao.executeESClientRequest(updateUserList, Config.ES_USER_TYPE);
}
if (updateRoleList.size() > 0) {
documentDao.executeESClientRequest(updateRoleList, Config.ES_ROLE_TYPE);
}
if (deleteUserList.size() > 0) {
documentDao.executeESClientRequest(deleteUserList, Config.ES_USER_TYPE);
}
if (deleteRoleList.size() > 0) {
documentDao.executeESClientRequest(deleteRoleList, Config.ES_ROLE_TYPE);
}
}
private void appendDeleteList(List<Delete> userList, List<Delete> roleList, JSONObject object, String esType) {
switch (esType) {
case Config.ES_USER_TYPE: {
userList.add(documentDao.getDeleteIndex(object.get("id").toString(), esType));
break;
}
case Config.ES_ROLE_TYPE: {
roleList.add(documentDao.getDeleteIndex(object.get("id").toString(), esType));
break;
}
}
}
private void appendUpdateList(List<Index> userList, List<Index> roleList, JSONObject object, String esType) {
switch (esType) {
case Config.ES_USER_TYPE: {
userList.add(documentDao.getUpdateIndex(object.get("id").toString(), esType, object));
break;
}
case Config.ES_ROLE_TYPE: {
roleList.add(documentDao.getUpdateIndex(object.get("id").toString(), esType, object));
break;
}
}
}
}
7、运行结果（源码在前言）
Mysql-role表数据
Mysql-user表数据
Elasticsearch同步数据
网友评论