ELASTICSEARCH-JDBC sync MYSLQ data to Elasticsearch

Source: Internet
Author: User
Tags curl

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 "



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.