ElasticSearch如何与Mysql集成 [英] ElasticSearch how to integrate with Mysql

问题描述

在我的一个项目中，我打算使用ElasticSearch和mysql。
我已成功安装ElasticSearch。我能够分别管理ES中的索引。但是我不知道如何使用mysql实现相同的方法。

我已经阅读了几个文档，但我有点困惑，没有清楚的想法。任何人都可以帮助我吗？

提前感谢

解决方案

最后我找到答案。分享我的发现。

要使用ElasticSearch与Mysql，您将需要Java数据库连接（ JDBC ）导入器。使用JDBC驱动程序，您可以将您的mysql数据同步到弹性搜索。

我正在使用ubuntu 14.04 LTS，您将需要安装Java8来运行弹性搜索， / p>

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

安装Java8后安装弹性搜索2.2.0如下

 ＃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 
  

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

现在可以在配置文件中进行一些基本配置。这里/etc/elasticsearch/elasticsearch.yml是我们的配置文件
您可以打开文件以更改

  nano / etc / elasticsearch / elasticsearch.yml 
  

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

例如：

 ＃-------------- --------------------群集----------------------------- ------ 
＃
＃为集群使用描述性名称：
＃
 cluster.name：servercluster 
＃
＃ ----------------------------------节点--------------- --------------------- 
＃
＃为节点使用描述性名称：
＃
节点。名称：vps.server.com 
＃
＃向节点添加自定义属性：
＃
＃node.rack：r1 
  

现在保存文件并启动弹性搜索

  etc / init.d / elasticsearch start 
  

测试ES安装或不运行以下

  curl -XGET'http：// lo calhost：9200 /？pretty'
  

如果你接下来，那么你的弹性搜索现在安装了：

  {
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
}，
标语：你知道， 
} 
  

现在让我们安装 elasticsearch-JDBC >

从 http：// xbib .org / repository / org / xbib / elasticsearch / import / elasticsearch-jdbc / 2.3.3.1 / elasticsearch-jdbc-2.3.3.1-dist.zip ，并在/ etc中解压缩/ elasticsearch /并在其中创建logs文件夹（日志路径应为/ etc / elasticsearch / logs）

我有一个数据库在mysql中创建，名称为 ElasticSearchDatabase ，而名为test的表格中包含字段id，名称和电子邮件 p>

  cd / etc / elasticsearch 
  

并运行以下

  echo'{
type：jdbc，
jdbc：
 
url：jdbc：mysql：// localhost：3306 / ElasticSearchDatabase，
user：root，
密码：
sql：SELECT id as _id，id，name，email FROM test，
index：users，
type用户，
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.Runnerorg.xbib.tools.JDBCImporter
  

现在检查如果在ES中导入的mysql数据不是

  curl -XGET http：// localhost：9200 / users / _search /？pretty 
  

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

注意： / p>

在旧版本的ES ...插件 使用了Elasticsearch-river-jdbc ，这是最新的版本不要使用它。

我希望我可以节省你的时间:)

任何进一步的想法赞赏

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

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 couple of documents but i am a bit confused and not having clear idea. can anyone please help me?

Thanks in advance.

解决方案

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

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.

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

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

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

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

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

For example :

# ---------------------------------- 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

Now save the file and start elasticsearch

 /etc/init.d/elasticsearch start

to test ES installed or not run following

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

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"
}

Now let's install elasticsearch-JDBC

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)

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

and run following

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"

now check if mysql data imported in ES or not

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

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

Caution :

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

Reference url : https://github.com/jprante/elasticsearch-jdbc

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