Indexing Mysql Database with elasticsearch - Newbie

[Mohit-2]

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

[Karussell]

There is an external projects which could help:

https://github.com/Aconex/scrutineer

Peter.

On 23 Dez., 08:00, Mohit <[hidden email]> wrote:
> 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

[sowmyak]

Hi,

Have you got it working?

If so, can you please let me know the procedure?

Thanks!

On Friday, December 23, 2011 1:00:54 AM UTC-6, Mohit wrote:

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

Hi Mohit,

welcome to elasticsearch! You can try the JDBC river here: https://github.com/jprante/elasticsearch-river-jdbc

Best regards,

Jörg

On Friday, December 23, 2011 8:00:54 AM UTC+1, Mohit wrote:

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

Hi Sowmya,

On Tuesday, August 7, 2012 1:57:29 AM UTC+2, Jörg Prante wrote:

welcome to elasticsearch! You can try the JDBC river here: https://github.com/jprante/elasticsearch-river-jdbc

[sowmyak]

Hi,

Thanks! I have seen the link and followed to index my mysql data in elasticsearch.
I could index the data of the tables.
But, I couldn't index the data which is updated in the mysql database tables ( either because of insertion or deletion of the rows on the tables) into the elasticsearch.

I have followed the procedure described below to update the indexes. Can you please let me know the correct procedure if I have understood the concept by mistake.


Step 1: create table class_info(rno int, name varchar(100));  //table in mysql database whose data has to be indexed.


Step 2 : create table my_jdbc_river(_index varchar(64), _type varchar(64), _id varchar(64), source_timestamp timestamp not null default current_timestamp, source_operation varchar(8), source_sql varchar(255), target_timestamp timestamp, target_operation varchar(8) default 'n/a', target_failed boolean, target_message varchar(255), primary key(_index, _type, _id, source_timestamp, source_operation));  

//create a river table in mysql database

Step 3: Create a trigger on class_info table to insert the rows into my_jdbc_river on insert operation
delimiter $$

create trigger check_root after insert on class_info for each row begin insert into my_jdbc_river values("index", "type", "id", null, 'create', 'select * from check_es', null, null, true, null);  end$$

delimiter ;

I am not sure whether the parameters to insert statement are correct or not.

Step 4: Create a river on ElasticSearch
curl -XPUT 'localhost:9200/_river/my_jdbc_river/_meta' -d '{  "type" : "jdbc", "jdbc" : { "driver" : "com.mysql.jdbc.Driver", "url" : "jdbc:mysql://localhost:3306/testes", "user" : "root",           "password" : "p00ph34d",  "poll" : "300s","rivertable" : true,       "interval" : "305s" }, "index" : { "index" : "jdbc","type" : "jdbc",  "bulk_size" : 100,"max_bulk_requests" : 30, "bulk_timeout" : "60s"} }'

And, when i am inserting the values into class_info they are not being indexed in elasticsearch.

Please let me know the correct procedure I am missing anything or misunderstood the concept.

Thanks!

On Mon, Aug 6, 2012 at 6:59 PM, Jörg Prante <[hidden email]> wrote:

Hi Sowmya,

On Tuesday, August 7, 2012 1:57:29 AM UTC+2, Jörg Prante wrote:

welcome to elasticsearch! You can try the JDBC river here: https://github.com/jprante/elasticsearch-river-jdbc

[oliverusselldev]

You will need to connect MySQL database with Elasticsearch. Create a custom cluster and node, then create a CRUD function. To Create CRUD function you will have to use elasticsearch API library. You will need to create a function that will gather all the data from MySQL database and save it in Elasticsearch database. You can find the codes here: https://www.cloudways.com/blog/setup-elasticsearch-with-mysql/