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 "