如何将 ElasticSearch 与 MySQL 集成?

2021-12-13 00:00:00 elasticsearch mysql logstash elasticsearch-5 elk

在我的一个项目中，我计划将 ElasticSearch 与 MySQL 结合使用.我已经成功安装了 ElasticSearch.我可以单独管理 ES 中的索引.但我不知道如何在 MySQL 中实现相同的功能.

In one of my project, I am planning to use ElasticSearch with MySQL. I have successfully installed ElasticSearch. I am able to manage index in ES separately. but I don't know how to implement the same with MySQL.

我已经阅读了一些文档，但我有点困惑，没有一个清晰的想法.

I have read a couple of documents but I am a bit confused and not having a clear idea.

推荐答案

我终于找到了答案.分享我的发现.

Finally i was able to find the answer. sharing my findings.

要在 Mysql 中使用 ElasticSearch，您需要 Java 数据库连接 (JDBC) 导入器.使用 JDBC 驱动程序，您可以将 mysql 数据同步到 elasticsearch.

To use ElasticSearch with Mysql you will require The Java Database Connection (JDBC) importer. with JDBC drivers you can sync your mysql data into elasticsearch.

我使用的是 ubuntu 14.04 LTS，您需要安装 Java8 才能运行用 Java 编写的 elasticsearch

I am using ubuntu 14.04 LTS and you will require to install Java8 to run elasticsearch as it is written in Java

以下是安装ElasticSearch 2.2.0和ElasticSearch-jdbc 2.2.0的步骤，请注意两个版本必须相同

following are steps to install ElasticSearch 2.2.0 and ElasticSearch-jdbc 2.2.0 and please note both the versions has to be same

安装Java8后.....如下安装elasticsearch 2.2.0

after installing Java8 ..... install elasticsearch 2.2.0 as follows

# cd /opt

# wget https://download.elasticsearch.org/elasticsearch/release/org/elasticsearch/distribution/deb/elasticsearch/2.2.0/elasticsearch-2.2.0.deb

# sudo dpkg -i elasticsearch-2.2.0.deb

此安装过程会将 Elasticsearch 安装在/usr/share/elasticsearch/中，其配置文件将放置在/etc/elasticsearch 中.

This installation procedure will install Elasticsearch in /usr/share/elasticsearch/ whose configuration files will be placed in /etc/elasticsearch .

现在让我们在配置文件中做一些基本的配置.这里/etc/elasticsearch/elasticsearch.yml 是我们的配置文件您可以通过

Now lets do some basic configuration in config file. here /etc/elasticsearch/elasticsearch.yml is our config file you can open file to change by 

nano /etc/elasticsearch/elasticsearch.yml

并更改集群名称和节点名称

and change cluster name and node name

例如:

# ---------------------------------- Cluster -----------------------------------
#
# Use a descriptive name for your cluster:
#
 cluster.name: servercluster
#
# ------------------------------------ Node ------------------------------------
#
# Use a descriptive name for the node:
#
 node.name: vps.server.com
#
# Add custom attributes to the node:
#
# node.rack: r1

现在保存文件并启动elasticsearch

Now save the file and start elasticsearch

 /etc/init.d/elasticsearch start

测试ES安装或不运行以下

to test ES installed or not run following

 curl -XGET 'http://localhost:9200/?pretty'

如果你得到关注，那么你的elasticsearch现在已经安装:)

If you get following then your elasticsearch is installed now :)

{
  "name" : "vps.server.com",
  "cluster_name" : "servercluster",
  "version" : {
    "number" : "2.2.0",
    "build_hash" : "8ff36d139e16f8720f2947ef62c8167a888992fe",
    "build_timestamp" : "2016-01-27T13:32:39Z",
    "build_snapshot" : false,
    "lucene_version" : "5.4.1"
  },
  "tagline" : "You Know, for Search"
}

现在让我们安装 elasticsearch-JDBC

从 http://xbib.org/repository/org/xbib/elasticsearch/importer 下载/elasticsearch-jdbc/2.3.3.1/elasticsearch-jdbc-2.3.3.1-dist.zip 并将其解压到/etc/elasticsearch/并在那里创建logs"文件夹(日志路径应该是/etc/elasticsearch/logs)

download it from http://xbib.org/repository/org/xbib/elasticsearch/importer/elasticsearch-jdbc/2.3.3.1/elasticsearch-jdbc-2.3.3.1-dist.zip and extract the same in /etc/elasticsearch/ and create "logs" folder also there ( path of logs should be /etc/elasticsearch/logs)

我在 mysql 中创建了一个名为ElasticSearchDatabase"的数据库，并在名为test"的表中创建了一个数据库，其中包含字段 id、name 和 email

I have one database created in mysql having name "ElasticSearchDatabase" and inside that table named "test" with fields id,name and email

cd /etc/elasticsearch

并运行以下

echo '{
"type":"jdbc",
"jdbc":{

"url":"jdbc:mysql://localhost:3306/ElasticSearchDatabase",
"user":"root",
"password":"",
"sql":"SELECT id as _id, id, name,email FROM test",
"index":"users",
"type":"users",
"autocommit":"true",
"metrics": {
            "enabled" : true
        },
        "elasticsearch" : {
             "cluster" : "servercluster",
             "host" : "localhost",
             "port" : 9300 
        } 
}
}' | java -cp "/etc/elasticsearch/elasticsearch-jdbc-2.2.0.0/lib/*" -"Dlog4j.configurationFile=file:////etc/elasticsearch/elasticsearch-jdbc-2.2.0.0/bin/log4j2.xml" "org.xbib.tools.Runner" "org.xbib.tools.JDBCImporter"

现在检查是否在ES中导入mysql数据

now check if mysql data imported in ES or not

curl -XGET http://localhost:9200/users/_search/?pretty

如果一切顺利，你将可以看到你所有的 json 格式的 mysql 数据如果有任何错误，您将能够在/etc/elasticsearch/logs/jdbc.log 文件中看到它们

If all goes well, you will be able to see all your mysql data in json format and if any error is there you will be able to see them in /etc/elasticsearch/logs/jdbc.log file

注意:

在旧版本的 ES ... 插件中使用了 Elasticsearch-river-jdbc，但在最新版本中已完全弃用，因此请勿使用.

In older versions of ES ... plugin Elasticsearch-river-jdbc was used which is completely deprecated in latest version so do not use it.

我希望我能节省你的时间 :)

I hope i could save your time :)

如果有任何进一步的想法，我们将不胜感激

Any further thoughts are appreciated

参考网址:https://github.com/jprante/elasticsearch-jdbc

相关文章