CODEWITHSUNDEEP
searchelasticsearch
Mohit Jain
Mohit Jain

Reputation: 337

Indexing Mysql Database with elasticsearch

I recently started looking ElasticSearch to implement search in my application. I have my database in Mysql which have approx. >2 mn records. I know in sphinx we could create an index directly on any mysql table column. I wanted to know if its possible in Elasticsearch, if not directly how we could implement that?

Thanks Mohit

Upvotes: 10

Views: 10595

Answers (4)

Ameer Salah Aldeen
Ameer Salah Aldeen

Reputation: 289

You have two options in this case:-

  1. Event based , so you can trigger an event on api level and listen to the event to re-sync the changes.
  2. Orm Level, most of the Orms have hooks, so you can listen to the hooks and re-sync Elastic search.

Upvotes: 0

Debabrata Nayak
Debabrata Nayak

Reputation: 455

  • What is Logstash? Logstash is a data collection engine with real-time pipelining capabilities. You can use Logstash to collect your logs, parse them into your desirable format and store them for searching or monitoring for your application.

Step-1: Download latest version of elastic search

Step-2: After downloading logstasch need to install jdbc plugin

bin/plugin install logstash-input-jdbc

Step-3: Download mysql-jdbc driver...as i downloaded and the file name is mysql-connector-java-5.1.36.jar. put this file inside logstash/drivers/mysql-connector-java-5.1.36.jar ....you can put it anywhere you want. but remember the path.

Step-4: Now create a db.conf file and put it inside logstash directory...in my case logstash-2.3.2/logstash-jdbc.conf here is the content:

input { jdbc { jdbc_driver_library => "/opt/logstash-2.3.2/drivers/mysql-connector-java-5.1.36.jar" jdbc_driver_class => "com.mysql.jdbc.Driver" jdbc_connection_string => "jdbc:mysql://localhost/myapp_development" jdbc_user => "root" jdbc_password => "admin" parameters => { "user_list" => "users" } schedule => "* * * * *" statement => "select name,email from users" } } output { elasticsearch { index => "users" document_type => "users" document_id => "%{123}" hosts => "localhost:9200" } }

Jdbc Input Parameter Description

jdbc_driver_library Full path of jdbc library jdbc_driver_class JDBC driver class to load, for MySQL it is com.mysql.jdbc.Driver jdbc_connection_string JDBC mysql connection string jdbc_user MySQL user jdbc_password MySQL user password schedule Run input statement every minutes. Available scheduling option statement MySQL statement parameters Parameter to pass in MySQL statement

Jdbc Output Parameters Description

Parameter Description
index Elasticsearch Index name document_type Elasticsearch Index type. document_id Id of elastic search doc

Step-5: Now set up is complete. we need to start logstart. before that start elasicsearch

then start logstsh and give it jadbc-db.conf file for configuration $ cd logstash-2.3.2 $ bin/logstash agent -f logstash-jdbc.conf

Now test your data quick in elastic search http://localhost:9200/users/users/_search

Thank You Debs

Upvotes: 0

catalinux
catalinux

Reputation: 1452

https://github.com/jprante/elasticsearch-river-jdbc does that. You can syncronize data from mysql source, using an "operations table".

It,s a little bit tricky first time installing, but it works.

First you have to install the plugin Second, you have to set up ( configure) the imported data

Upvotes: 4

Andy
Andy

Reputation: 8949

There is no native support (at this time) for mysql in Elastic Search like there is in Sphinx.

Elastic Search is a great option for search, but you'll have probably to do some custom work to trigger reindexing of items when they change in your database. Exactly how that is accomplished will depend alot on your application. Elastic Search has great near real time search functionality, and has been proven to perform well under heavy indexing load so triggering reindexing shouldn't a concern for most applications.

The NoSQL movement seems to gaining steam as well. Some applications use Elastic Search as the only data store.

Upvotes: 4

Related Questions