ElasticSearch与Mysql同步数据解决方案

程序员文章站 2022-05-09 14:18:01

...

ES数据和数据库数据如何同步整合?

1. canel的通道是利用mysql的bin_log同步

2. 通过消息队列同步,优势失败重试机制保证100%同步成功

本文介绍通过RocketMq消息队列同步:

首先是架构思路图:

ElasticSearch与Mysql同步数据解决方案

CRUD操作与我们的平时一样,但是在CRUD操作后我们会向RocketMq服务器事先定义好的Topic发送消息, 注意:Topic是同一个, 根据不同Tag来判断是什么类型的操作(新增, 删除, 更新), 废话不多说直接看代码

github地址:

1. 准备RocketMQ环境,

如果不会安装RocketMQ的走下面的传送门

2. pom文件准备

下面列举了三个重要依赖, 数据库, RocketMQ, 以及ES的依赖

        <!--数据库相关-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.18</version>
        </dependency>


       <!--rocketmq-->
        <dependency>
            <groupId>org.apache.rocketmq</groupId>
            <artifactId>rocketmq-client</artifactId>
            <version>4.3.0</version>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-elasticsearch</artifactId>
            <version>2.4.0-SNAPSHOT</version>
        </dependency>

3. 配置文件

server:
  port: 8082
spring:
  elasticsearch:
    rest:
      uris: localhost:9200
      read-timeout: 30s
      connection-timeout: 5s

  datasource:
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver


rocketmq:
  namesrvAddr: localhost:9876
  sendMsgTimeoutMillis: 3000
  reconsumeTimes: 3

mybatis:
  configuration:
    use-generated-keys: true
  mapper-locations: classpath*:mapper/*.xml

4. 编写RocketMq的配置类, 常量类

package com.es.jd.config.mq;


import org.apache.rocketmq.client.producer.DefaultMQProducer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.stereotype.Component;


/**
 * Created by xiaoxudong on 2019/3/22
 * @author zhouzhou
 */
@Component
public class RocketMQConfig {
    Logger log = LoggerFactory.getLogger(RocketMQConfig.class);

    @Autowired
    private RocketMqProperties mqProperties;

    /**
     * 初始化生产者
     *
     * @return
     */
    @Bean
    public DefaultMQProducer defaultProducer() throws Exception {
        // 实例化消息生产者Producer
        DefaultMQProducer producer = new DefaultMQProducer(MqConstant.ConsumeGroup.ES_USER_IMPORT);
        // 设置NameServer的地址
        producer.setNamesrvAddr(mqProperties.getNamesrvAddr());
        // 设置发送消息超时时间
        producer.setSendMsgTimeout(mqProperties.getSendMsgTimeoutMillis());
        // 设置重试次数
        producer.setRetryTimesWhenSendFailed(mqProperties.getReconsumeTimes());
        // 启动Producer实例
        producer.start();

        return producer;
    }

}

package com.es.jd.config.mq;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

/**
 * Created by xiaoxudong on 2019/3/22
 */
@Data
@Component
@ConfigurationProperties(prefix = "rocketmq")
public class RocketMqProperties {
 

    private String namesrvAddr;
    private Integer sendMsgTimeoutMillis;

    /**
     * 失败重试次数
     */
    private Integer reconsumeTimes;

 
}

下面是常量类, 定义了Topic,Tag,和ConsumeGroup

package com.es.jd.config.mq;

/**
 * 功能描述:
 *
 * @Author: zhouzhou
 * @Date: 2020/8/7$ 16:09$
 */
public class MqConstant {


    /**
     * top
     */
    public static class Topic {

        /**
         * 稿件录入
         */
        public static final String ES_USER_IMPORT = "ES_USER_IMPORT";

    }


    /**
     * TAG
     */
    public static class Tag {

        public static final String ES_USER_IMPORT_TAG_INSERT = "ES_USER_IMPORT_TAG_INSERT";
        public static final String ES_USER_IMPORT_TAG_UPDATE = "ES_USER_IMPORT_TAG_UPDATE";
        public static final String ES_USER_IMPORT_TAG_DELETE = "ES_USER_IMPORT_TAG_DELETE";
    }


    /**
     * consumeGroup 消费者
     */
    public static class ConsumeGroup {

        public static final String ES_USER_IMPORT = "GID_ES_USER_IMPORT";

    }

}

5. 我封装了RocketMq的操作类(独家)

package com.es.jd.utils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.rocketmq.client.producer.DefaultMQProducer;
import org.apache.rocketmq.client.producer.SendResult;
import org.apache.rocketmq.common.message.Message;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

/**
 * Description:Mq 消息发送工具类
 *
 * @author: zhouzhou
 * Date: 2020-05-25
 * Time: 2:19 PM
 */
@Component
@Slf4j
public class MqMessageSendUtils {

    @Autowired(required = false)
    private DefaultMQProducer rocketMqProducer;


    /**
     * private RocketMqProperties mqProperties;
     * 发送普通消息
     *
     * @param topic
     * @param tag
     * @param body
     * @param <T>
     * @return
     */
    public <T> boolean sendNormalMessage(String topic, String tag, T body) {

        Message message = new Message(topic, tag, JSON.toJSONBytes(body));

        try {
            SendResult sendResult = rocketMqProducer.send(message);
            if (sendResult != null) {
                log.info("mq 消息发送成功" + sendResult);
                return true;
            } else {
                return false;
            }
        } catch (Exception e) {
            log.warn("mq 消息发送失败", e);
            return false;
        }

    }

    /**
     * 发送普通延时消息
     *
     * @param topic
     * @param tag
     * @param body
     * @param <T>
     * @param delay 毫秒
     * @return
     */
    public <T> boolean sendNormalDelayMessage(String topic, String tag, T body, Long delay) {

        Message message = new Message(topic, tag, JSON.toJSONBytes(body));
        // 默认延时 10s
        // 1s 5s 10s 30s 1m 2m 3m 4m 5m 6m 7m 8m 9m 10m 20m 30m 1h 2h
        message.setDelayTimeLevel(3);
        try {
            SendResult sendResult = rocketMqProducer.send(message);
            if (sendResult != null) {
                log.info("mq 延时消息发送成功" + sendResult);
                return true;
            } else {
                return false;
            }
        } catch (Exception e) {
            log.warn("mq 延时消息发送失败", e);
            return false;
        }
    }



}
}

6. DAO数据层(ES,MySql,实体类)略

7.核心业务类(service层, 订阅层)

package com.es.jd.service;

import com.es.jd.config.mq.MqConstant;
import com.es.jd.temp.dao.EsUserDao;
import com.es.jd.temp.entity.EsUserEntity;
import com.es.jd.utils.MqMessageSendUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * 功能描述: Es用户相关Service
 *
 * @Author: zhouzhou
 * @Date: 2020/8/10$ 11:09$
 */
@Service
public class EsUserService {

    @Autowired
    private EsUserDao esUserDao;

    @Autowired
    private MqMessageSendUtils mqMessageSendUtils;

    /**
     * 导入用户
     *
     * @param entity
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public Long importEsUser(EsUserEntity entity) {

        // 第一步首先导入数据库
        int insert = esUserDao.insert(entity);
        // 数据库落库成功后, 发送rocketMq请求,进行ES同步
        if (insert > 0) {
            boolean result = mqMessageSendUtils.sendNormalMessage(MqConstant.Topic.ES_USER_IMPORT, MqConstant.Tag.ES_USER_IMPORT_TAG_INSERT, entity);
            if (result) {
                return entity.getId();
            }
        }
        return null;
    }

    /**
     * 更新用户
     *
     * @param entity
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public Long updateEsUser(EsUserEntity entity) {

        // 第一步首先导入数据库
        int insert = esUserDao.update(entity);
        // 数据库落库成功后, 发送rocketMq请求,进行ES同步
        if (insert > 0) {
            EsUserEntity entity1 = esUserDao.queryById(entity.getId());
            boolean result = mqMessageSendUtils.sendNormalMessage(MqConstant.Topic.ES_USER_IMPORT, MqConstant.Tag.ES_USER_IMPORT_TAG_UPDATE, entity1);
            if (result) {
                return entity.getId();
            }
        }
        return null;
    }

    /**
     * 删除用户
     *
     * @param id
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public Boolean deleteEsUserById(Long id) {

        // 第一步首先导入数据库
        int delete = esUserDao.deleteById(id);
        // 数据库落库成功后, 发送rocketMq请求,进行ES同步
        if (delete > 0) {
            return mqMessageSendUtils.sendNormalMessage(MqConstant.Topic.ES_USER_IMPORT, MqConstant.Tag.ES_USER_IMPORT_TAG_DELETE, id);
        }
        return null;
    }

}

package com.es.jd.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.es.jd.bean.ESUser;
import com.es.jd.config.mq.MqConstant;
import com.es.jd.config.mq.RocketMqProperties;
import com.es.jd.repository.EsUserRepository;
import com.es.jd.temp.entity.EsUserEntity;
import com.es.jd.utils.BeanUtils;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.rocketmq.client.consumer.DefaultMQPushConsumer;
import org.apache.rocketmq.client.consumer.listener.ConsumeConcurrentlyStatus;
import org.apache.rocketmq.client.consumer.listener.MessageListenerConcurrently;
import org.apache.rocketmq.common.message.MessageExt;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import java.util.List;

/**
 * 功能描述: Mq 消费者
 *
 * @Author: zhouzhou
 * @Date: 2020/8/7$ 17:15$
 */
@Slf4j
@Service
public class MqConsumerService {

    @Autowired
    private RocketMqProperties mqProperties;

    @Autowired
    private EsUserRepository esUserRepository;


    @PostConstruct
    public void consumerTest() throws Exception {
        DefaultMQPushConsumer defaultMQPushConsumer = new DefaultMQPushConsumer(MqConstant.ConsumeGroup.ES_USER_IMPORT);
        defaultMQPushConsumer.setNamesrvAddr(mqProperties.getNamesrvAddr());
        // * 代表不过滤
        defaultMQPushConsumer.subscribe(MqConstant.Topic.ES_USER_IMPORT, "*");
        defaultMQPushConsumer.registerMessageListener((MessageListenerConcurrently) (msgs, context) -> {
            for (MessageExt msg : msgs) {
                try {
                    byte[] body = msg.getBody();
                    String tags = msg.getTags();
                    String msgId = msg.getMsgId();


                    // 根据标签tag来决定什么操作
                    executeByTags(tags, body);

                } catch (Exception e) {
                    // 对次数进行冲正并且落库 todo 发送告警信息
                    log.warn(String.format("即将导入ES库失败, 失败原因为{%s}", e.getMessage()), e);
                    return ConsumeConcurrentlyStatus.RECONSUME_LATER;
                }

            }

            return ConsumeConcurrentlyStatus.CONSUME_SUCCESS;

        });
        defaultMQPushConsumer.start();
        log.info(String.format("消费者{%s}启动了", MqConstant.Topic.ES_USER_IMPORT));
    }

    private void executeByTags(String tags, byte[] body) {
        switch (tags) {
            case MqConstant.Tag.ES_USER_IMPORT_TAG_INSERT:
                JSONObject insertObject = (JSONObject) JSON.parse(body);
                EsUserEntity insertEsUserRequest = insertObject.toJavaObject(EsUserEntity.class);

                if (insertEsUserRequest != null) {
                    ESUser esUser = BeanUtils.copy(insertEsUserRequest, ESUser.class);
                    List<String> tagList = Lists.newArrayList(insertEsUserRequest.getTags().split("\\|"));
                    esUser.setTags(tagList);
                    esUser.setDesc(insertEsUserRequest.getUserDesc());
                    ESUser save = esUserRepository.save(esUser);
                    log.info("导入es库成功" + save);
                }
                break;
            case MqConstant.Tag.ES_USER_IMPORT_TAG_UPDATE:
                JSONObject updateObject = (JSONObject) JSON.parse(body);
                EsUserEntity updateEsUser = updateObject.toJavaObject(EsUserEntity.class);
                if (updateEsUser != null) {
                    ESUser esUser = BeanUtils.copy(updateEsUser, ESUser.class);
                    List<String> tagList = Lists.newArrayList(updateEsUser.getTags().split("\\|"));
                    esUser.setTags(tagList);
                    esUser.setDesc(updateEsUser.getUserDesc());
                    ESUser save = esUserRepository.save(esUser);
                    log.info("更新es库成功" + save);
                }
                break;
            case MqConstant.Tag.ES_USER_IMPORT_TAG_DELETE:
                Integer id = (Integer) JSON.parse(body);
                esUserRepository.deleteById(new Long(id));
                log.info("删除es库成功id=" + id);

                break;

        }
    }


}

8.测试

这个数据库

ElasticSearch与Mysql同步数据解决方案

这是es, 完美同步,done

ElasticSearch与Mysql同步数据解决方案

上一篇： Java数据结构：List线性表的顺序存储结构（增，删，查，改，字符串输出）【泛型，迭代器】

下一篇： oracle 字符串转成行

ElasticSearch与Mysql同步数据解决方案

github地址:

1. 准备RocketMQ环境,

2. pom文件准备

3. 配置文件

4. 编写RocketMq的配置类, 常量类

5. 我封装了RocketMq的操作类(独家)

6. DAO数据层(ES,MySql,实体类)略

7.核心业务类(service层, 订阅层)

8.测试

php实现MySQL数据库备份与还原类实例

MySQL数据库中CAST与CONVERT函数实现类型转换的讲解

ubuntu 16.04 数据库mysql安装与管理

Mysql—数据库管理与表管理

从Web查询数据库之PHP与MySQL篇

Django+mysql配置与简单操作数据库实例代码

举例介绍mysql数据库与oracle数据库的区别

kafka2x-Elasticsearch 数据同步工具demo

mysql数据库的多实例与主从同步。

mysql插入表数据中文乱码问题解决方案