如何将ElasticSearch与MySQL集成? [英] How to integrate ElasticSearch with MySQL?

问题描述

在我的一个项目中，我计划将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.

要将ElasticSearch与Mysql一起使用，您将需要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，名称和电子邮件地址

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

这篇关于如何将ElasticSearch与MySQL集成?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！