MySQL ElasticSearch如何与Mysql集成
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36152152/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ElasticSearch how to integrate with Mysql
提问by Yaxita Shah
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.
在我的一个项目中，我计划将 ElasticSearch 与 mysql 一起使用。我已经成功安装了 ElasticSearch。我可以单独管理 ES 中的索引。但我不知道如何用 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.
提前致谢。
采纳答案by Yaxita Shah
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.
要将 ElasticSearch 与 Mysql 一起使用，您将需要 Java 数据库连接 ( JDBC) 导入器。使用 JDBC 驱动程序，您可以将 mysql 数据同步到 elasticsearch。
I am using ubuntu 14.04 LTS and you will require to install Java8 to run elasticsearch as it is written in Java
我使用的是 ubuntu 14.04 LTS，你需要安装 Java8 才能运行 elasticsearch，因为它是用 Java 编写的
following are steps to install ElasticSearch 2.2.0 and ElasticSearch-jdbc 2.2.0and please note both the versions has to be same
以下是安装ElasticSearch 2.2.0 和 ElasticSearch-jdbc 2.2.0 的步骤，请注意两个版本必须相同
after installing Java8 ..... install elasticsearch 2.2.0 as follows
安装Java8后……安装elasticsearch 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
This installation procedure will install Elasticsearch in /usr/share/elasticsearch/ whose configuration files will be placed in /etc/elasticsearch .
此安装过程会将 Elasticsearch 安装在 /usr/share/elasticsearch/ 中，其配置文件将放置在 /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
现在让我们在配置文件中做一些基本的配置。这里 /etc/elasticsearch/elasticsearch.yml 是我们的配置文件，您可以打开文件进行更改
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
现在保存文件并启动elasticsearch
/etc/init.d/elasticsearch start
to test ES installed or not run following
测试 ES 安装或不运行以下
curl -XGET 'http://localhost:9200/?pretty'
If you get following then your elasticsearch is installed now :)
如果您得到关注，那么您的 elasticsearch 现已安装:)
{
"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
现在让我们安装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.zipand extract the same in /etc/elasticsearch/ and create "logs" folder also there ( path of logs should be /etc/elasticsearch/logs)
从http://xbib.org/repository/org/xbib/elasticsearch/importer/elasticsearch-jdbc/2.3.3.1/elasticsearch-jdbc-2.3.3.1-dist.zip/etc/elasticsearch/ 中下载并提取相同的文件，并在那里创建“日志”文件夹（日志路径应该是 /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
我在 mysql 中创建了一个名为“ ElasticSearchDatabase”的数据库，并在名为“test”的表中包含字段 id、name 和 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
现在检查是否在 ES 中导入了 mysql 数据
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
如果一切顺利，您将能够以 json 格式查看所有 mysql 数据，如果有任何错误，您将能够在 /etc/elasticsearch/logs/jdbc.log 文件中查看它们
Caution :
注意：
In older versions of ES ... plugin Elasticsearch-river-jdbcwas used which is completely deprecated in latest version so do not use it.
在旧版本的 ES 中……使用了插件Elasticsearch-river-jdbc，它在最新版本中已完全弃用，所以不要使用它。
I hope i could save your time :)
我希望我能节省你的时间:)
Any further thoughts are appreciated
任何进一步的想法表示赞赏
Reference url : https://github.com/jprante/elasticsearch-jdbc
回答by Nikhil Sahu
As of ES 5.x , they have given this feature out of the box with logstashplugin.
从 ES 5.x 开始，他们使用logstash插件提供了开箱即用的功能。
This will periodically import data from database and push to ES server.
这将定期从数据库导入数据并推送到 ES 服务器。
One has to create a simple import file given below (which is also described here) and use logstash to run the script. Logstash supports running this script on a schedule.
必须创建一个下面给出的简单导入文件（也在此处描述）并使用 logstash 来运行脚本。Logstash 支持按计划运行此脚本。
# file: contacts-index-logstash.conf
input {
jdbc {
jdbc_connection_string => "jdbc:mysql://localhost:3306/mydb"
jdbc_user => "user"
jdbc_password => "pswd"
schedule => "* * * * *"
jdbc_validate_connection => true
jdbc_driver_library => "/path/to/latest/mysql-connector-java-jar"
jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
statement => "SELECT * from contacts where updatedAt > :sql_last_value"
}
}
output {
elasticsearch {
protocol => http
index => "contacts"
document_type => "contact"
document_id => "%{id}"
host => "ES_NODE_HOST"
}
}
# "* * * * *" -> run every minute
# sql_last_value is a built in parameter whose value is set to Thursday, 1 January 1970,
# or 0 if use_column_value is true and tracking_column is set
You can download the mysql jar from maven here.
你可以在这里从 maven 下载 mysql jar 。
In case indexes do not exist in ES when this script is executed, they will be created automatically. Just like a normal post call to elasticsearch
如果执行此脚本时 ES 中不存在索引，则会自动创建它们。就像对elasticsearch的普通post调用一样
回答by Ijaz Ahmad Khan
The logstash JDBC plugin will do the job:
logstash JDBC 插件将完成这项工作：
input {
jdbc {
jdbc_connection_string => "jdbc:mysql://localhost:3306/testdb"
jdbc_user => "root"
jdbc_password => "factweavers"
# The path to our downloaded jdbc driver
jdbc_driver_library => "/home/comp/Downloads/mysql-connector-java-5.1.38.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
# our query
schedule => "* * * *"
statement => "SELECT" * FROM testtable where Date > :sql_last_value order by Date"
use_column_value => true
tracking_column => Date
}
output {
stdout { codec => json_lines }
elasticsearch {
"hosts" => "localhost:9200"
"index" => "test-migrate"
"document_type" => "data"
"document_id" => "%{personid}"
}
}
回答by Ahmed Khan
To make it more simple I have created a PHP class to Setup MySQL with Elasticsearch. Using my Class you can sync your MySQL data in elasticsearch and also perform full-text search. You just need to set your SQL query and class will do the rest for you.
为了使它更简单，我创建了一个 PHP 类来使用 Elasticsearch设置MySQL。使用我的类，您可以在 elasticsearch 中同步您的 MySQL 数据，还可以执行全文搜索。您只需要设置您的 SQL 查询，类将为您完成剩下的工作。
回答by Arno
The official Elasticsearch php client can be found at:
官方 Elasticsearch php 客户端可以在以下位置找到：
https://github.com/elastic/elasticsearch-php
https://github.com/elastic/elasticsearch-php
If you want more information on Elasticsearch in PHP this is a good read:
如果您想了解有关 PHP 中 Elasticsearch 的更多信息，这是一个很好的阅读：
https://www.elastic.co/guide/en/elasticsearch/client/php-api/2.0/index.html
https://www.elastic.co/guide/en/elasticsearch/client/php-api/2.0/index.html
