# Indexing Mysql Database with elasticsearch - Newbie

**URL:** <https://discuss.elastic.co/t/indexing-mysql-database-with-elasticsearch-newbie/6229>\
**Category:** Elasticsearch\
**Created:** [December 23, 2011, 7:00am UTC](https://discuss.elastic.co/t/indexing-mysql-database-with-elasticsearch-newbie/6229 "2011-12-23T07:00:54Z")\
**Posts on this page:** 1\
**Showing post:** 6

<div class="post-metadata">

**Author:** ![sowmyak](https://avatars.discourse-cdn.com/v4/letter/s/f19dbf/32.png) [@sowmyak](https://discuss.elastic.co/u/sowmyak)\
**Post date:** [August 7, 2012, 1:34pm UTC](https://discuss.elastic.co/t/indexing-mysql-database-with-elasticsearch-newbie/6229/6 "2012-08-07T13:34:27Z")

</div>

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 [joergprante@gmail.com](mailto:joergprante@gmail.com) 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](https://github.com/ **jprante/elasticsearch-river-** jdbc)[https://github.com/jprante/elasticsearch-river-jdbc](https://github.com/jprante/elasticsearch-river-jdbc)
> > 
> > >

---

_[View the full topic](https://discuss.elastic.co/t/indexing-mysql-database-with-elasticsearch-newbie/6229)._