# Indexing Mysql Database with elasticsearch - Newbie **URL:** \ **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:** 7\ **Page:** 1
**Author:** ![Mohit](https://avatars.discourse-cdn.com/v4/letter/m/c68b51/32.png) [@Mohit](https://discuss.elastic.co/u/Mohit)\ **Post date:** [December 23, 2011, 7:00am UTC](https://discuss.elastic.co/t/indexing-mysql-database-with-elasticsearch-newbie/6229/1 "2011-12-23T07:00:54Z")
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 ---
**Author:** ![Karussell1](https://avatars.discourse-cdn.com/v4/letter/k/50afbb/32.png) [@Karussell1](https://discuss.elastic.co/u/Karussell1)\ **Post date:** [December 23, 2011, 10:30am UTC](https://discuss.elastic.co/t/indexing-mysql-database-with-elasticsearch-newbie/6229/2 "2011-12-23T10:30:18Z")
There is an external projects which could help: [https://github.com/Aconex/scrutineer](https://github.com/Aconex/scrutineer) Peter. On 23 Dez., 08:00, Mohit [mnj...@gmail.com](mailto:mnj...@gmail.com) 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 ---
**Author:** ![sowmyak](https://avatars.discourse-cdn.com/v4/letter/s/f19dbf/32.png) [@sowmyak](https://discuss.elastic.co/u/sowmyak)\ **Post date:** [August 6, 2012, 4:17pm UTC](https://discuss.elastic.co/t/indexing-mysql-database-with-elasticsearch-newbie/6229/3 "2012-08-06T16:17:31Z")
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 ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [August 6, 2012, 11:57pm UTC](https://discuss.elastic.co/t/indexing-mysql-database-with-elasticsearch-newbie/6229/4 "2012-08-06T23:57:29Z")
Hi Mohit, welcome to elasticsearch! You can try the JDBC river here: [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](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 ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [August 6, 2012, 11:59pm UTC](https://discuss.elastic.co/t/indexing-mysql-database-with-elasticsearch-newbie/6229/5 "2012-08-06T23:59:08Z")
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: > [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc) > > > ---
**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")
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) > > > > > ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 3:17am UTC](https://discuss.elastic.co/t/indexing-mysql-database-with-elasticsearch-newbie/6229/7 "2017-07-06T03:17:28Z")