mysql不对称复制数据_实例详解MySQL与Elasticsearch数据不对称问题

jdbc-input-plugin 只能实现数据库的追加&＃xff0c;对于 elasticsearch 增量写入&＃xff0c;但经常jdbc源一端的数据库可能会做数据库删除或者更新操作。这样一来数据库与搜索引擎的数据库就出现了不对称的情况。本文主要介绍了MySQL 与 Elasticsearch 数据不对称问题解决办法的相关资料,对于 elasticsearch 增量写入&＃xff0c;但经常jdbc源一端的数据库可能会做数据库删除或者更新操作&＃xff0c;这里提供解决办法&＃xff0c;需要的朋友可以参考下&＃xff0c;希望能帮助到大家。

当然你如果有开发团队可以写程序在删除或者更新的时候同步对搜索引擎操作。如果你没有这个能力&＃xff0c;可以尝试下面的方法。

这里有一个数据表 article , mtime 字段定义了 ON UPDATE CURRENT_TIMESTAMP 所以每次更新mtime的时间都会变化

mysql> desc article;

&＃43;-------------&＃43;--------------&＃43;------&＃43;-----&＃43;--------------------------------&＃43;-------&＃43;

| Field | Type | Null | Key | Default | Extra |

&＃43;-------------&＃43;--------------&＃43;------&＃43;-----&＃43;--------------------------------&＃43;-------&＃43;

| id | int(11) | NO | | 0 | |

| title | mediumtext | NO | | NULL | |

| description | mediumtext | YES | | NULL | |

| author | varchar(100) | YES | | NULL | |

| source | varchar(100) | YES | | NULL | |

| content | longtext | YES | | NULL | |

| status | enum(&＃39;Y&＃39;,&＃39;N&＃39;)| NO | | &＃39;N&＃39; | |

| ctime | timestamp | NO | | CURRENT_TIMESTAMP | |

| mtime | timestamp | YES | | ON UPDATE CURRENT_TIMESTAMP | |

&＃43;-------------&＃43;--------------&＃43;------&＃43;-----&＃43;--------------------------------&＃43;-------&＃43;

7 rows in set (0.00 sec)

logstash 增加 mtime 的查询规则

jdbc {

jdbc_driver_library &＃61;> "/usr/share/java/mysql-connector-java.jar"

jdbc_driver_class &＃61;> "com.mysql.jdbc.Driver"

jdbc_connection_string &＃61;> "jdbc:mysql://localhost:3306/cms"

jdbc_user &＃61;> "cms"

jdbc_password &＃61;> "password"

schedule &＃61;> "* * * * *" #定时cron的表达式,这里是每分钟执行一次

statement &＃61;> "select * from article where mtime > :sql_last_value"

use_column_value &＃61;> true

tracking_column &＃61;> "mtime"

tracking_column_type &＃61;> "timestamp"

record_last_run &＃61;> true

last_run_metadata_path &＃61;> "/var/tmp/article-mtime.last"

}

创建回收站表&＃xff0c;这个事用于解决数据库删除&＃xff0c;或者禁用 status &＃61; &＃39;N&＃39; 这种情况的。

CREATE TABLE &＃96;elasticsearch_trash&＃96; (

&＃96;id&＃96; int(11) NOT NULL,

&＃96;ctime&＃96; timestamp NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (&＃96;id&＃96;)

) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;utf8

为 article 表创建触发器

CREATE DEFINER&＃61;&＃96;dba&＃96;&＃64;&＃96;%&＃96; TRIGGER &＃96;article_BEFORE_UPDATE&＃96; BEFORE UPDATE ON &＃96;article&＃96; FOR EACH ROW

BEGIN

-- 此处的逻辑是解决文章状态变为 N 的时候&＃xff0c;需要将搜索引擎中对应的数据删除。

IF NEW.status &＃61; &＃39;N&＃39; THEN

insert into elasticsearch_trash(id) values(OLD.id);

END IF;

-- 此处逻辑是修改状态到 Y 的时候&＃xff0c;方式elasticsearch_trash仍然存在该文章ID&＃xff0c;导致误删除。所以需要删除回收站中得回收记录。

IF NEW.status &＃61; &＃39;Y&＃39; THEN

delete from elasticsearch_trash where id &＃61; OLD.id;

END IF;

END

CREATE DEFINER&＃61;&＃96;dba&＃96;&＃64;&＃96;%&＃96; TRIGGER &＃96;article_BEFORE_DELETE&＃96; BEFORE DELETE ON &＃96;article&＃96; FOR EACH ROW

BEGIN

-- 此处逻辑是文章被删除同事将改文章放入搜索引擎回收站。

insert into elasticsearch_trash(id) values(OLD.id);

END

接下来我们需要写一个简单地 Shell 每分钟运行一次&＃xff0c;从 elasticsearch_trash 数据表中取出数据&＃xff0c;然后使用 curl 命令调用 elasticsearch restful 接口&＃xff0c;删除被收回的数据。

你还可以开发相关的程序&＃xff0c;这里提供一个 Spring boot 定时任务例子。

实体

package cn.netkiller.api.domain.elasticsearch;

import java.util.Date;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.Id;

import javax.persistence.Table;

&＃64;Entity

&＃64;Table

public class ElasticsearchTrash {

&＃64;Id

private int id;

&＃64;Column(columnDefinition &＃61; "TIMESTAMP DEFAULT CURRENT_TIMESTAMP")

private Date ctime;

public int getId() {

return id;

}

public void setId(int id) {

this.id &＃61; id;

}

public Date getCtime() {

return ctime;

}

public void setCtime(Date ctime) {

this.ctime &＃61; ctime;

}

}

仓库

package cn.netkiller.api.repository.elasticsearch;

import org.springframework.data.repository.CrudRepository;

import com.example.api.domain.elasticsearch.ElasticsearchTrash;

public interface ElasticsearchTrashRepository extends CrudRepository{

}

定时任务

package cn.netkiller.api.schedule;

import org.elasticsearch.action.delete.DeleteResponse;

import org.elasticsearch.client.transport.TransportClient;

import org.elasticsearch.rest.RestStatus;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.scheduling.annotation.Scheduled;

import org.springframework.stereotype.Component;

import com.example.api.domain.elasticsearch.ElasticsearchTrash;

import com.example.api.repository.elasticsearch.ElasticsearchTrashRepository;

&＃64;Component

public class ScheduledTasks {

private static final Logger logger &＃61; LoggerFactory.getLogger(ScheduledTasks.class);

&＃64;Autowired

private TransportClient client;

&＃64;Autowired

private ElasticsearchTrashRepository alasticsearchTrashRepository;

public ScheduledTasks() {

}

&＃64;Scheduled(fixedRate &＃61; 1000 * 60) // 60秒运行一次调度任务

public void cleanTrash() {

for (ElasticsearchTrash elasticsearchTrash : alasticsearchTrashRepository.findAll()) {

DeleteResponse response &＃61; client.prepareDelete("information", "article", elasticsearchTrash.getId() &＃43; "").get();

RestStatus status &＃61; response.status();

logger.info("delete {} {}", elasticsearchTrash.getId(), status.toString());

if (status &＃61;&＃61; RestStatus.OK || status &＃61;&＃61; RestStatus.NOT_FOUND) {

alasticsearchTrashRepository.delete(elasticsearchTrash);

}

}

}

}

Spring boot 启动主程序。

package cn.netkiller.api;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

import org.springframework.scheduling.annotation.EnableScheduling;

&＃64;SpringBootApplication

&＃64;EnableScheduling

public class Application {

public static void main(String[] args) {

SpringApplication.run(Application.class, args);

}

}

相关推荐&＃xff1a;

Elasticsearch是什么&＃xff1f;Elasticsearch 能够被用在什么地方&＃xff1f;

Elasticsearch索引和文档操作实例教程

详解spring中使用Elasticsearch的实例教程