# How do i import data from mysql to elastic search 1.6

**URL:** <https://discuss.elastic.co/t/how-do-i-import-data-from-mysql-to-elastic-search-1-6/2658>\
**Category:** Elasticsearch\
**Created:** [June 15, 2015, 4:49am UTC](https://discuss.elastic.co/t/how-do-i-import-data-from-mysql-to-elastic-search-1-6/2658 "2015-06-15T04:49:54Z")\
**Posts on this page:** 8\
**Page:** 2

<div class="post-metadata">

**Author:** ![MrReda](https://avatars.discourse-cdn.com/v4/letter/m/ed655f/32.png) [@MrReda](https://discuss.elastic.co/u/MrReda)\
**Post date:** [August 24, 2015, 6:02am UTC](https://discuss.elastic.co/t/how-do-i-import-data-from-mysql-to-elastic-search-1-6/2658/22 "2015-08-24T06:02:14Z")

</div>

Good Morning too

1-You can change the working directory from the command line by typing cd c: or cd d: ,once inside it you can choose the working directory you want to work with.  
2-Copy the following script and paste it 😄  
DIR="$( cd "$( dirname "${BASH\_SOURCE[0]}" )" && pwd )"  
bin=${DIR}/bin  
lib=${DIR}/lib  
echo $lib  
echo $bin

echo '{  
"type" : "jdbc",  
"jdbc" : {  
"driver": "com.mysql.jdbc.Driver",  
"url" : "jdbc:mysql://localhost:3306/world",  
"user" : "root",  
"password" : "root",  
"sql" : "select \* from city",  
"treat\_binary\_as\_string" : true,  
"elasticsearch" : {  
"cluster" : "elasticsearch",  
"host" : "localhost",  
"port" : 9300  
},  
"max\_bulk\_actions" : 20000,  
"max\_concurrent\_bulk\_requests" : 10,  
"index" : "gemalto",  
"type":"gem"  
}  
}  
' | java   
-cp "elasticsearch-jdbc-1.6.0.0-uberjar.jar;mysql-connector-java-5.1.33.jar"   
-Dlog4j.configurationFile="file://localhost/C:/elasticsearch-jdbc-1.6.0.0/bin/log4j2.xml"   
org.xbib.tools.Runner   
org.xbib.tools.JDBCImporter

Here you go don't forget to change the values for your database user and files required for logging plus the putting the jars inside the lib directory of elastic search jdbc 1.6

Thanks  
MrReda

---

<div class="post-metadata">

**Author:** ![Elastic\_POC](https://avatars.discourse-cdn.com/v4/letter/e/13edae/32.png) [@Elastic\_POC](https://discuss.elastic.co/u/Elastic_POC)\
**Post date:** [August 27, 2015, 6:24am UTC](https://discuss.elastic.co/t/how-do-i-import-data-from-mysql-to-elastic-search-1-6/2658/23 "2015-08-27T06:24:55Z")

</div>

I followed your steps MrReda but still getting same issue.I am using ES 1.5.2 and importer 1.5.2 with SQL.

---

<div class="post-metadata">

**Author:** ![Elastic\_POC](https://avatars.discourse-cdn.com/v4/letter/e/13edae/32.png) [@Elastic\_POC](https://discuss.elastic.co/u/Elastic_POC)\
**Post date:** [August 27, 2015, 6:25am UTC](https://discuss.elastic.co/t/how-do-i-import-data-from-mysql-to-elastic-search-1-6/2658/24 "2015-08-27T06:25:20Z")

</div>

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter  
$ DIR="$( cd "$( dirname "${BASH\_SOURCE[0]}" )" && pwd )"  
bin=${DIR}/bin

lib=${DIR}/lib

echo $lib

echo $bin

echo '{

"type" : "jdbc",

"jdbc" : {

"driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",

"url" : "jdbc:sqlserver://usalvwaldbqa1:1433;databaseName=es"",

"user" : "es",

"password" : "Answ3r21",

"sql" : "SELECT \* FROM es.es.g\_fast\_documentation",

"treat\_binary\_as\_string" : true,

```
"elasticsearch" : {

    "cluster" : "elasticsearch_dev",

    "host" : "localhost",

    "port" : 9200

},

"max_bulk_actions" : 20000,

"max_concurrent_bulk_requests" : 10,

"index" : "my_index",

```

"type":"my\_type"  
}

}

' | java -cp "elasticsearch-jdbc-1.5.2.0-uberjar.jar;hsqldb-2.3.2.jar" \

-Dlog4j.configurationFile=${bin}/log4j2.xml \

org.xbib.tools.Runner \

org.xbib.tools.JDBCImporter  
svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter  
$

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter  
$ bin=${DIR}/bin

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter  
$

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter  
$ lib=${DIR}/lib

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter  
$

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter  
$ echo $lib  
/cygdrive/d/elastic/jdbcimporter/lib

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter  
$

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter  
$ echo $bin  
/cygdrive/d/elastic/jdbcimporter/bin

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter  
$

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter  
$ echo '{

> "type" : "jdbc",
> 
> "jdbc" : {
> 
> "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
> 
> "url" : "jdbc:sqlserver://usalvwaldbqa1:1433;databaseName=es"",
> 
> "user" : "es",
> 
> "password" : "Answ3r21",
> 
> "sql" : "SELECT \* FROM es.es.g\_fast\_documentation",
> 
> "treat\_binary\_as\_string" : true,
> 
> ```
> "elasticsearch" : {
> 
> "cluster" : "elasticsearch_dev",
> 
> "host" : "localhost",
> 
> "port" : 9200
> 
> },
> 
> "max_bulk_actions" : 20000,
> 
> "max_concurrent_bulk_requests" : 10,
> 
> "index" : "my_index",
> 
> ```
> 
> "type":"my\_type"  
> }
> 
> }
> 
> ' | java -cp "elasticsearch-jdbc-1.5.2.0-uberjar.jar;hsqldb-2.3.2.jar" \

Usage: java [-options] class [args...]  
(to execute a class)  
or java [-options] -jar jarfile [args...]  
(to execute a jar file)  
where options include:  
-d32 use a 32-bit data model if available  
-d64 use a 64-bit data model if available  
-server to select the "server" VM  
The default VM is server.

```
-cp <class search path of directories and zip/jar files>
-classpath <class search path of directories and zip/jar files>
              A ; separated list of directories, JAR archives,
              and ZIP archives to search for class files.
-D<name>=<value>
              set a system property
-verbose:[class|gc|jni]
              enable verbose output
-version print product version and exit
-version:<value>
              require the specified version to run
-showversion print product version and continue
-jre-restrict-search | -no-jre-restrict-search
              include/exclude user private JREs in the version search
-? -help print this help message
-X print help on non-standard options
-ea[:<packagename>...|:<classname>]
-enableassertions[:<packagename>...|:<classname>]
              enable assertions with specified granularity
-da[:<packagename>...|:<classname>]
-disableassertions[:<packagename>...|:<classname>]
              disable assertions with specified granularity
-esa | -enablesystemassertions
              enable system assertions
-dsa | -disablesystemassertions
              disable system assertions
-agentlib:<libname>[=<options>]
              load native agent library <libname>, e.g. -agentlib:hprof
              see also, -agentlib:jdwp=help and -agentlib:hprof=help

```

See [Java SE - Documentation](http://www.oracle.com/technetwork/java/javase/documentation/index.html) for more details.

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter  
$ -Dlog4j.configurationFile=${bin}/log4j2.xml \

> 

-bash: -Dlog4j.configurationFile=/cygdrive/d/elastic/jdbcimporter/bin/log4j2.xml: No such file or directory

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter  
$ org.xbib.tools.Runner \

> 

-bash: org.xbib.tools.Runner: command not found

svc-msfast@usalvwdevmsfast1 /cygdrive/d/elastic/jdbcimporter  
$ org.xbib.tools.JDBCImporter  
-bash: org.xbib.tools.JDBCImporter: command not found

---

<div class="post-metadata">

**Author:** ![chocomuesli](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/chocomuesli/32/4152_2.png) [@chocomuesli](https://discuss.elastic.co/u/chocomuesli)\
**Post date:** [August 27, 2015, 6:57am UTC](https://discuss.elastic.co/t/how-do-i-import-data-from-mysql-to-elastic-search-1-6/2658/25 "2015-08-27T06:57:09Z")

</div>

Hi @Deepak_Kumar,

you may try Pentaho PDI ([Link](http://www.pentaho.com/download)). We use it to extract and convert data, as its necessary. Unfortunately its Elasticsearch Bulk Insert-function is outdated and not working, but it also provides a REST client, which we use to directly POST data to our cluster.

Cheers, Dominik

---

<div class="post-metadata">

**Author:** ![vaniaravinda](https://avatars.discourse-cdn.com/v4/letter/v/7993a0/32.png) [@vaniaravinda](https://discuss.elastic.co/u/vaniaravinda)\
**Post date:** [August 27, 2015, 9:01am UTC](https://discuss.elastic.co/t/how-do-i-import-data-from-mysql-to-elastic-search-1-6/2658/26 "2015-08-27T09:01:27Z")

</div>

Hi @MrReda,

It is working fine after downloading Cygwin. But I want to schedule the script through service. Can u please let me know how can I schedule it?

Thanks,  
Vani Aravinda

---

<div class="post-metadata">

**Author:** ![MrReda](https://avatars.discourse-cdn.com/v4/letter/m/ed655f/32.png) [@MrReda](https://discuss.elastic.co/u/MrReda)\
**Post date:** [September 2, 2015, 5:36pm UTC](https://discuss.elastic.co/t/how-do-i-import-data-from-mysql-to-elastic-search-1-6/2658/27 "2015-09-02T17:36:04Z")

</div>

Please check out and verify the url for the db connection and pulling the data.It seems to be wrong (may be).  
Thanks

---

<div class="post-metadata">

**Author:** ![Swina\_Allen](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/swina_allen/32/6598_2.png) [@Swina\_Allen](https://discuss.elastic.co/u/Swina_Allen)\
**Post date:** [November 9, 2016, 9:38pm UTC](https://discuss.elastic.co/t/how-do-i-import-data-from-mysql-to-elastic-search-1-6/2658/28 "2016-11-09T21:38:08Z")

</div>

Hi, maybe late ... but why you don't try to use **logstash** with jdbc input plugin and elasticsearch output?  
It worked for me (about 1 million rows) on a local laptop test environment (WIN10-elasticsearch5-logstash5-mysql5.4).

Example **mysqlimport.cfg**

```
input {
  jdbc {
    jdbc_driver_library => "your_path_to/mysql-connector-java-5.1.40-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/your_database"
    jdbc_user => "user"
    jdbc_password => "password"
    statement_filepath => "your_sql_query_file" //you can create your query to extract data in a separated file
  }
}
output {
  elasticsearch {
    action => "index"
    hosts => ["your_host"]
    index => "your_index"
    document_type => "your_type"
   }
}

```

After created mysqlimport.cfg (you can name as you want) run :  
`logstash -f mysqlimport.cfg`

Antonio

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 5, 2017, 10:05pm UTC](https://discuss.elastic.co/t/how-do-i-import-data-from-mysql-to-elastic-search-1-6/2658/29 "2017-07-05T22:05:59Z")

</div>

Closed on July 5, 2017, 10:05pm UTC



[Previous page](https://discuss.elastic.co/t/how-do-i-import-data-from-mysql-to-elastic-search-1-6/2658.md?page=1)
