ELASTICSEARCH-JDBC sync MYSLQ data to Elasticsearch

First, Linux on the use of

Premise:
1) Elasticsearch 2.3.2 installed successfully, test OK.
2 MySQL installation is successful, can achieve increase, delete, change, check.
The database available for testing is test and the table is CC with the following details:

Mysql> select * from CC;
+----+------------+
| id | name |
+----+------------+
| 1 | laoyang |
| 2 | Dluzhang |
| 3 | Dlulaoyang |
+----+------------+
3 rows in Set (0.00 sec)

First step: Download the tool.
Address: http://xbib.org/repository/org/xbib/elasticsearch/importer/elasticsearch-jdbc/2.3.2.0/ Elasticsearch-jdbc-2.3.2.0-dist.zip
Step two: Import CentOS. The path set itself, the author put to the root directory, decompression. Unzip Elasticsearch-jdbc-2.3.2.0-dist.zip
Step three: Set the environment variable.

[root@5b9dbaaa148a/]# Vi/etc/profile
Export jdbc_importer_home=/elasticsearch-jdbc-2.3.2.0

Make environment variables effective:
[root@5b9dbaaa148a/]# source/etc/profile
Step Fourth: Configuration usage. Detailed reference: HTTPS://GITHUB.COM/JPRANTE/ELASTICSEARCH-JDBC
1), the root directory under the new folder Odbc_es as follows:

[root@5b9dbaaa148a/]# ll/ Odbc_es/
Drwxr-xr-x 2 root 4096 June 03:11 logs
-rwxrwxrwx 1 root root 542 June 04:03 MYSQL_IMPORT_ES.S H

2), new script mysql_import_es.sh, and the following:

[root@5b9dbaaa148a odbc_es]# cat mysql_import_es.sh
' #!/bin/sh
bin= $JDBC _importer_home/bin lib=
$JDBC _ Importer_home/lib
Echo ' {'
type ': ' jdbc ',
' jdbc ': {
' elasticsearch.autodiscover ': true,
' Elasticsearch.cluster ":" My-application ", #簇名, see:/usr/local/elasticsearch/config/elasticsearch.yml
" url ":" Jdbc:mysql://10.8.5.101:3306/test ", #mysql数据库地址
" user ":" root ", #mysql用户名
" password ":" 123456 ", #mysql密码
"SQL": "SELECT * from cc",
"Elasticsearch": {
  "host": "10.8.5.101",
  "port": 9300
},
" Index ":" Myindex ", #新的index
" type ":" MyType "#新的type
}
} ' | java \
  -CP" ${lib}/* "\
  - Dlog4j.configurationfile=${bin}/log4j2.xml \
  org.xbib.tools.Runner \
  org.xbib.tools.JDBCImporter

3), add executable permissions for mysql_import_es.sh.
[root@5b9dbaaa148a odbc_es]# chmod a+x mysql_import_es.sh
4) Execute Script mysql_import_es.sh
[root@5b9dbaaa148a odbc_es]#./mysql_import_es.sh

Step Fifth: Test whether the data synchronization was successful.
To retrieve a query using Elasticsearch:

[root@5b9dbaaa148a odbc_es]# curl-xget ' Http://10.8.5.101:9200/myindex/mytype/_search?pretty '
{
  "took ': 4,
  "Timed_out": false,
  "_shards": {
  "total": 8,
  "successful": 8,
  "failed": 0
  },
  "hits": {  c9/> "Total": 3,
  "Max_score": 1.0,
  "hits": [{"
  _index": "Myindex",
  "_type": "MyType",
  "_id" : "Avvxkgeeun6ksbtikowh",
  "_score": 1.0,
  "_source": {
  "id": 1,
  "name": "Laoyang"
  }
  }, {"
  _index": "Myindex", "
  _type": "MyType",
  "_id": "Avvxkgeeun6ksbtikowi",
  "_score": 1.0,
  " _source ": {
  " id ": 2,
  " name ":" Dluzhang "
  }
  }, {
  " _index ":" Myindex ",
  " _type ":" MyType ",
  "_id": "Avvxkgeeun6ksbtikowj", "
  _score": 1.0,
  "_source": {
  "id": 3,
  "name": "Dlulaoyang" c37/>}}}}

The above information that contains the MySQL data field appears to be a successful synchronization. 4, Elasticsearch-jdbc synchronization method two

[root@5b9dbaaa148a odbc_es]# cat mysql_import_es_simple.sh
#!/bin/sh
bin= $JDBC _importer_home/bin
lib= $JDBC _importer_home/lib
  java \
  -cp "${lib}/*" \
  -dlog4j.configurationfile=${bin}/log4j2.xml \
  Org.xbib.tools.Runner \
  org.xbib.tools.JDBCImporter statefile.json

[root@5b9dbaaa148a odbc_es]# Cat Statefile.json
{
"type": "JDBC",
"jdbc": {
"elasticsearch.autodiscover": True,
" Elasticsearch.cluster ":" My-application ","
url ":" Jdbc:mysql://10.8.5.101:3306/test ",
" user ":" Root ",
"password": "123456", "
SQL": "SELECT * from cc",
"Elasticsearch": {
  "host": "10.8.5.101",
  " Port ": 9300
},
" index ":" Myindex_2 ",
" type ":" Mytype_2 "
}
}

Script is separated from the JSON file, and the JSON file is loaded before the script executes.
How to execute: Run the script directly./mysql_import_es_simple.sh. 5, MySQL and elasticsearch equivalent query

Objective: To implement name information for querying id=3 from the table cc.
1 SQL statement query in MySQL:

Mysql> SELECT * from cc where id=3;
+----+------------+
| id | name |
+----+------------+
| 3 | Dlulaoyang
| +----+------------+
1 row in Set (0.00 sec)

2) Elasticsearch Search:

[root@5b9dbaaa148a odbc_es]# Curl http://10.8.5.101:9200/myindex/mytype/_search?pretty-d '
{'
filter ': {' Term ': {' id ': ' 3 '}}
'
{'
  took ': 3,
  ' timed_out ': false,
  ' _shards ': {' total ':
  8,
  
    "successful": 8,
  "failed": 0
  },
  "hits": {
  "total": 1,
  "Max_score": 1.0,
  "hits": [{
    "_index": "Myindex",
  "_type": "MyType",
  "_id": "Avvxkgeeun6ksbtikowj",
  "_score": 1.0,
  "_ Source ': {
  ' id ': 3,
  ' name ': ' Dlulaoyang '
  }
  }}}

  

second, the use of Windoes

Script configuration:

@echo off

set lib=%jdbc_importer_home%\lib\*
set Bin=%jdbc_importer_home%\bin


echo {^
    "type": "JDBC ", ^
    " jdbc ": {^
        " url ":" Jdbc:mysql://localhost:3306/test ", ^
        " user ":" root ", ^
        " password ":" Esri ", ^
        "SQL":  "select * from Mysql2es_test", ^
        "treat_binary_as_string": true,^
        "Elasticsearch": {^
             "Cluster": "Application", ^
             "host": "localhost", ^
             "port": 9300^
        },^
        "index": "Test" ^
      }^
}^ | "%java_home%\bin\java"-CP "%lib%"-dlog4j.configurationfile= "%bin%\log4j2.xml" "Org.xbib.tools.Runner" " Org.xbib.tools.JDBCImporter "