让ElasticSearch连接mysql数据库

最新推荐文章于 2024-05-15 17:21:24 发布

转载最新推荐文章于 2024-05-15 17:21:24 发布 · 5.3k 阅读

elasticsearch 专栏收录该内容

2 篇文章

订阅专栏

本文提供了一个简明指南，展示了如何通过Elasticsearch的JDBC插件将MySQL数据库中的数据实时同步到Elasticsearch中。包括下载安装Elasticsearch、JDBC驱动、配置JDBC插件及创建数据同步河流等关键步骤。

https://github.com/jprante/elasticsearch-river-jdbc/wiki/Quickstart

Quickstart

A short guide for the impatient!

Prerequisites:

A running MySQL database test, with user test and password test, a table orders

A terminal / console with commands curl and unzip

Internet access (of course)

Let's go!

Download elasticsearch (current version is 0.19.8, check for the most recent version on http://elasticsearch.org) from

curl -OL https://github.com/downloads/elasticsearch/elasticsearch/elasticsearch-0.19.10.zip
Unpack zip file into you favorite elasticsearch directory, we call it $ES_HOME

cd $ES_HOME

unzip path/to/elasticsearch-0.19.10.zip
Install JDBC river plugin (current version is 1.3.2, check for the most recent version, and if you have the file permissions to write into the plugins folder). If you have installed a JDBC river plugin before, it is recommended to remove the folder plugins/river-jdbc before installing a new version. (**Note**: If you're using a version of elasticsearch >= 0.90.0, you need to use at least version 2.2.0 of the river-jdbc plugin).

./bin/plugin --url http://bit.ly/10FJhEd -install river-jdbc
Download MySQL JDBC driver (current version is 5.1.21, check for the most recent version)

curl -OL http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.21.zip/from/http://cdn.mysql.com/
Add MySQL JDBC driver jar to JDBC river plugin directory

cp mysql-connector-java-5.1.21-bin.jar $ES_HOME/plugins/river-jdbc/
Start elasticsearch in terminal window with logging on the console

./bin/elasticsearch -f

Start another terminal window, and create a new JDBC river with name my_jdbc_river with this curl command

curl -XPUT 'localhost:9200/_river/my_jdbc_river/_meta' -d '{
    "type" : "jdbc",
    "jdbc" : {
        "driver" : "com.mysql.jdbc.Driver",
        "url" : "jdbc:mysql://localhost:3306/test",
        "user" : "test",
        "password" : "test",
        "sql" : "select * from orders"
    },
    "index" : {
        "index" : "jdbc",
        "type" : "jdbc"
    }
}'

The river runs! Watch the log on the elasticsearch console for the river activity. When the river fetched the data and is going to wait for the next cycle (which is by default 60 minutes ahead), you can query your elasticsearch node for the data you just indexed with the following curl command

curl -XGET 'localhost:9200/jdbc/jdbc/_search?pretty&q=*'
Enjoy the result!
If you want to stop the my_jdbc_river river fetching data from the orders table after the quick demonstration, use this curl command:

curl -XDELETE 'localhost:9200/_river/my_jdbc_river'

What next?

Now, if you want more fine-tuning, you can drop the index jdbc, change the mapping, change the index name or type name, change the river creation settings, play with the data fetch interval - whatever you like.