# MySQL to Elasticsearch

**URL:** <https://discuss.elastic.co/t/mysql-to-elasticsearch/41367>\
**Category:** Logstash\
**Created:** [February 10, 2016, 11:00am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367 "2016-02-10T11:00:01Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\
**Post date:** [February 10, 2016, 11:00am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/1 "2016-02-10T11:00:01Z")

</div>

Please propose a better way to import selected MySQL tables to Elasticsearch using Logstash or similar latest way with examples.

I already did the same successfully using my Own PHP script directly without more issue. My sample methods are following,

```
public function createIndex($index){  
    $params = [
      'index' => $index,
        'body' => [
        'settings' => [
            'max_result_window' =>2147483647
          ]
        ]        
    ];
    
    $client= $this->getClient();
    if(!$this->exists($index)){
      $client->indices()->create($params);
    }
  }

  function getDataType($type){
    $temp=array();
    switch($type){
      case 'int':{
        $temp['type']= 'integer';
        break;
      }      
      case 'bigint':{
        $temp['type']= 'long';
        break;
      }      
      case 'varchar':{
        $temp['type']= 'string';
        $temp['analyzer']= 'standard';
        break;
      }      
      case 'double':{
        $temp['type']= 'double';
        break;
      }      
      case 'datetime':{
        $temp['type']= 'date';
        $temp['format']= 'yyyy-MM-dd HH:mm:ss';
        break;
      }
      case 'timestamp':{
        $temp['type']= 'date';
        $temp['format']= 'yyyy-MM-dd HH:mm:ss';
        break;
      }
      case 'time':{
        $temp['type']= 'date';
        $temp['format']= 'HH:mm:ss';
        break;
      }
      case 'date':{
        $temp['type']= 'date';
        $temp['format']= 'yyyy-MM-dd';
        break;
      }
      default :{
        $temp['type']= 'string';
        $temp['analyzer']= 'standard';
        break;
      }
    }
    return $temp;
  }
  
  function sinzTable($index,$table){
    $reuarray=$this->moData->descTable($table);
    $myField1=array(); 
    $tempField=array(); 
    foreach ($reuarray as $key=>$val){
      $tempField=$this->getDataType(strtok($val['Type'],'('));
      $myField1[$val['Field']]=$tempField;
    }   
    $this->createType($index, $table, $myField1);      
  }
  
  function sinzData($index,$table){
    $client= $this->getClient();
    $data=$this->moData->selectAll($table);
   
    foreach($data as $row){
      $client->index([      
            'index'=>$index,
            'type'=>$table,
            'body'=>$row
      ]);
    }
  }
```

---

<div class="post-metadata">

**Author:** ![ebuildy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ebuildy/32/6070_2.png) [@ebuildy](https://discuss.elastic.co/u/ebuildy)\
**Post date:** [February 10, 2016, 12:15pm UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/2 "2016-02-10T12:15:51Z")

</div>

You could use bulk API instead to insert data.

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [February 10, 2016, 1:10pm UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/3 "2016-02-10T13:10:04Z")

</div>

Why not use the [jdbc input plugin](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html)?

---

<div class="post-metadata">

**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\
**Post date:** [February 10, 2016, 3:51pm UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/4 "2016-02-10T15:51:33Z")

</div>

@ebuildy, I already used one REST API that is [Postman](http://www.getpostman.com/) from Google. bulk API is like that or it have more features than postman? I have to update the server everyday automatically. Is it possible in bulk API?

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [February 10, 2016, 6:11pm UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/5 "2016-02-10T18:11:26Z")

</div>

> @ebuildy, I already used one REST API that is Postman from Google. bulk API is like that or it have more features than postman?

Postman is a tool to make HTTP requests, including REST APIs. It's not an API in itself. You are comparing apples and oranges.

> I have to update the server everyday automatically. Is it possible in bulk API?

You can call the bulk API any time you like.

Again, I _do_ suggest you look into the jdbc input.

---

<div class="post-metadata">

**Author:** ![ebuildy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ebuildy/32/6070_2.png) [@ebuildy](https://discuss.elastic.co/u/ebuildy)\
**Post date:** [February 10, 2016, 9:15pm UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/6 "2016-02-10T21:15:24Z")

</div>

As @magnusbaeck said, you are typically doing the job of logstash, a tool perfectly fit to ingest data from mysql (via JDBC input plugin [https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html)), process data (mutate fields...), and output to elasticsearch ( [https://www.elastic.co/guide/en/logstash/current/plugins-outputs-elasticsearch.html](https://www.elastic.co/guide/en/logstash/current/plugins-outputs-elasticsearch.html) ) .

Bulk API I am talking about is an elasticsearch API allows to insert several documents via a single API call:

[https://www.elastic.co/guide/en/elasticsearch/reference/current/docs-bulk.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/docs-bulk.html).

Give a try to LogStash!

---

<div class="post-metadata">

**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\
**Post date:** [February 12, 2016, 6:19am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/7 "2016-02-12T06:19:58Z")

</div>

Actually I'm new to logstash. As you said, I'm reading for logstash with jdbc input plugin. Would you please suggest me any example with demo about the same.

Regards,  
Croos.

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [February 12, 2016, 6:28am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/8 "2016-02-12T06:28:46Z")

</div>

The [jdbc input documentation](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html) contains an example that should get you started. If it's unclear please ask a specific question, including your current configuration, what happens, and what you expected to happen.

---

<div class="post-metadata">

**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\
**Post date:** [February 12, 2016, 6:38am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/9 "2016-02-12T06:38:19Z")

</div>

Ok @magnusbaeck, I will try and let you know.

---

<div class="post-metadata">

**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\
**Post date:** [February 12, 2016, 7:08am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/10 "2016-02-12T07:08:54Z")

</div>

When I start the logstash.bat getting the following error

```
io/console not supported; tty will not be manipulated

```

I'm using:  
**elasticsearch-2.1.1** and **logstash-2.1.1**

My system info:

```
C:\Users\Croos>systeminfo
OS Name: Microsoft Windows 8.1 Pro
OS Version: 6.3.9600 N/A Build 9600
OS Manufacturer: Microsoft Corporation
OS Configuration: Standalone Workstation 

```

my java version:

```
C:\Users\Croos>java -version
java version "1.8.0_66"
Java(TM) SE Runtime Environment (build 1.8.0_66-b18)
Java HotSpot(TM) 64-Bit Server VM (build 25.66-b18, mixed mode)
```

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [February 12, 2016, 7:14am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/11 "2016-02-12T07:14:30Z")

</div>

> io/console not supported; tty will not be manipulated

You can ignore that message.

---

<div class="post-metadata">

**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\
**Post date:** [February 12, 2016, 7:17am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/12 "2016-02-12T07:17:32Z")

</div>

After that message the cmd is suddenly close. What can I do?

---

<div class="post-metadata">

**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\
**Post date:** [February 12, 2016, 7:24am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/13 "2016-02-12T07:24:42Z")

</div>

Ok now I have started successfully..!

```
C:\ES_PACK\logstash-2.1.1\bin>logstash -e 'input { stdin { } } output { stdout {} }'
io/console not supported; tty will not be manipulated
Settings: Default filter workers: 1
Logstash startup completed
hello world
2016-02-12T07:20:32.414Z Lenovo-PC hello world
```

---

<div class="post-metadata">

**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\
**Post date:** [February 12, 2016, 11:39am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/14 "2016-02-12T11:39:20Z")

</div>

@magnusbaeck,  
I have successfully export the MySql table to Elasticsearch. Now my problem is,  
Frequently increasing the number of documents after every update by the jdbc-logstash, Even the the database have no changes. How to control it, or Over right the previous record by new, or Delete the previous document form the elasticsearch, avoid the duplication.. Give me some idea..

```
input {
  jdbc {
    jdbc_driver_library => "mysql-connector-java-5.1.38-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://192.168.10.6/asterisk?user=cron&password=1234"
    jdbc_user => "Croos"
    parameters => {
     }
    schedule => "* * * * *"
    statement => "SELECT * from vicidial_log"
  }
}

output {
  elasticsearch {
    hosts => ["localhost:9200"]
  }
}
```

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [February 12, 2016, 12:31pm UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/15 "2016-02-12T12:31:40Z")

</div>

Have a look at the `use_column_value` and `tracking_column` options and the `sql_last_value` query parameter.

[https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#\_predefined\_parameters](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#_predefined_parameters)  
[https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#\_state](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#_state)

---

<div class="post-metadata">

**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\
**Post date:** [February 13, 2016, 6:39am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/16 "2016-02-13T06:39:08Z")

</div>

```
statement => "SELECT * from vicidial_log WHERE start_epoch > :sql_last_value"
use_column_value => true
tracking_column => start_epoch

```

Actually our main db is in mariaDB not MySQL. So when I run the logstash with above config, The following error occur.

```
io/console not supported; tty will not be manipulated
←[31mUnknown setting 'use_column_value' for jdbc {:level=>:error}←[0m
←[31mUnknown setting 'tracking_column' for jdbc {:level=>:error}←[0m
Error: Something is wrong with your configuration.
You may be interested in the '--configtest' flag which you can
use to validate logstash's configuration before you choose
to restart a running system.

```

If I run with only first line,

`statement => "SELECT * from vicidial_log WHERE start_epoch > :sql_last_value"`

I show the following exception:

```
C:\xampp\htdocs\ES_PACK\logstash-2.1.1\bin>logstash -f logstash.conf
io/console not supported; tty will not be manipulated
Settings: Default filter workers: 1
Logstash startup completed
←[33mException when executing JDBC query {:exception=>#<Sequel::DatabaseError: J
ava::ComMysqlJdbcExceptionsJdbc4::MySQLSyntaxErrorException: You have an error i
n your SQL syntax; check the manual that corresponds to your MariaDB server vers
ion for the right syntax to use near ':sql_last_value' at line 1>, :level=>:warn
}←[0m
←[33mException when executing JDBC query {:exception=>#<Sequel::DatabaseError: J
ava::ComMysqlJdbcExceptionsJdbc4::MySQLSyntaxErrorException: You have an error i
n your SQL syntax; check the manual that corresponds to your MariaDB server vers
ion for the right syntax to use near ':sql_last_value' at line 1>, :level=>:warn
}←[0m 

```

How can I rewrite the config?

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [February 13, 2016, 11:10am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/17 "2016-02-13T11:10:36Z")

</div>

Those parameters are apparently only available in recent releases of the jdbc plugin. Upgrade the plugin (preferred) or consult the documentation of the plugin you have.

---

<div class="post-metadata">

**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\
**Post date:** [February 13, 2016, 3:59pm UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/18 "2016-02-13T15:59:21Z")

</div>

where is it available for download? or I have to download latest version of logstash?

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [February 13, 2016, 5:06pm UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/19 "2016-02-13T17:06:28Z")

</div>

Please consult the documentation: [https://www.elastic.co/guide/en/logstash/current/working-with-plugins.html#updating-plugins](https://www.elastic.co/guide/en/logstash/current/working-with-plugins.html#updating-plugins)

(But you might as well upgrade to Logstash 2.2.1. There's no reason to run anything older.)

---

<div class="post-metadata">

**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\
**Post date:** [February 14, 2016, 7:07am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367/20 "2016-02-14T07:07:46Z")

</div>

As you said I updated the logstash. Now that is running without any error but import nothing to elsatic..?

```
input {
  jdbc {
    jdbc_driver_library => "mysql-connector-java-5.1.38-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost/student?user=root&password="
    jdbc_user => "Croos"
    parameters => {
    }
    
    schedule => "* * * * *"
    statement => "SELECT * from subject WHERE id > :sql_last_value"
    use_column_value => true
    tracking_column => id
  }
}

output {
  elasticsearch {
    hosts => ["localhost:9200"]
  }
}
```

[Next page](https://discuss.elastic.co/t/mysql-to-elasticsearch/41367.md?page=2)