ElasticSearch(五) Elasticsearch-jdbc实现MySQL同步到ElasticSearch详解程序员

#wget http://xbib.org/repository/org/xbib/elasticsearch/importer/elasticsearch-jdbc/2.3.4.0/elasticsearch-jdbc-2.3.4.0-dist.zip   //wget下载地址

# unzip elasticsearch-jdbc-2.3.4.0-dist.zip 
# mv elasticsearch-jdbc-2.3.4.0-dist.zip /usr/local/jdbc/

# vim /etc/profile //把下面两行代码放入底部 
export JDBC_HOME=/usr/local/jdbc 
export ES_HOME=/usr/local/elasticsearch 
# source /etc/profile  //生效

# cd /usr/local/ 
# mkdir shell shell/logs 
# vim mysql-elasticsearch.sh

#脚本内容 
#!/bin/sh 
bin=$JDBC_HOME/bin 
lib=$JDBC_HOME/lib 
echo '{ 
"type" : "jdbc", 
"jdbc": { 
　　　　　　"elasticsearch.autodiscover":true,  
　　　　　　"elasticsearch.cluster":"my-application",         #簇名，对应好配置 
　　　　　　"url":"jdbc:mysql://192.168.9.155:3306/hb",     #mysql地址/端口/库名 
　　　　　　"user":"root",   #Mysql账号 
　　　　　　"password":"wt000000",  #Mysql密码 
　　　　　　"sql":"select product_name,id as _id from hp_product where id >10 limit 10 ",  #SQL语句 .........注意的是，这里只是测试，在实际配置中需要对应好要存入文档的字段，同样表的主见id对应文档id 
　　　　　　"elasticsearch" : {
                "cluster":"my-application", 
  　　　　　　　　"host" : "192.168.9.155", 
  　　　　　　　　"port" : 9300 
　　　　　　 }, 
　　　　　　"index" : "myindex",     #新的index,这里测试的是商品表，用的index是myindex，其实可以规范命名~ 
　　　　　　"type" : "mytype"        #新的type，类型也可以规范命名 
　　    } 
}'| java / 
  -cp "${lib}/*" / 
  -Dlog4j.configurationFile=${bin}/log4j2.xml / 
  org.xbib.tools.Runner / 
  org.xbib.tools.JDBCImporter

# chmod a+x mysql-elasticsearch.sh   //赋予权限 
# ./mysql-elasticsearch.sh   //执行脚本

cat /usr/local/shell/logs/jdbc.log

# curl -XGET 'http://192.168.9.155:9200/myindex/mytype/_search?pretty'    

{ 
"took" : 14, 
"timed_out" : false, 
"_shards" : { 
"total" : 5, 
"successful" : 5, 
"failed" : 0 
}, 
"hits" : { 
"total" : 10, 
"max_score" : 1.0, 
"hits" : [ { 
"_index" : "myindex", 
"_type" : "mytype", 
"_id" : "14", 
"_score" : 1.0, 
"_source" : { 
"product_name" : "兑点茶 剪刀石头布原花混合茶 10包" 
} 
}, { 
"_index" : "myindex", 
"_type" : "mytype", 
"_id" : "19", 
"_score" : 1.0, 
"_source" : { 
"product_name" : "L'Occitane 欧舒丹 蜡菊赋颜精华液 30ml" 
} 
}, { 
"_index" : "myindex", 
"_type" : "mytype", 
"_id" : "12", 
"_score" : 1.0, 
"_source" : { 
"product_name" : "兑点茶 乌克丽丽原花混合茶 10包" 
} 
}, { 
"_index" : "myindex", 
"_type" : "mytype", 
"_id" : "15", 
"_score" : 1.0, 
"_source" : { 
"product_name" : "兑点茶 魔法精灵原花混合茶12包/盒" 
} 
}, { 
"_index" : "myindex", 
"_type" : "mytype", 
"_id" : "20", 
"_score" : 1.0, 
"_source" : { 
"product_name" : "Bobbi Brown波比布朗 弹力保湿精华液 30ml" 
} 
}, { 
"_index" : "myindex", 
"_type" : "mytype", 
"_id" : "13", 
"_score" : 1.0, 
"_source" : { 
"product_name" : "兑点茶 挪威森林原花混合茶 10包" 
} 
}, { 
"_index" : "myindex", 
"_type" : "mytype", 
"_id" : "16", 
"_score" : 1.0, 
"_source" : { 
"product_name" : "兑点茶 涌雪原花混合茶12包/盒" 
} 
}, { 
"_index" : "myindex", 
"_type" : "mytype", 
"_id" : "18", 
"_score" : 1.0, 
"_source" : { 
"product_name" : "L’Occitane 欧舒丹蜡菊亮白水凝精华液 30ml" 
} 
}, { 
"_index" : "myindex", 
"_type" : "mytype", 
"_id" : "11", 
"_score" : 1.0, 
"_source" : { 
"product_name" : "兑点茶 艾丽莎原花混合茶12包" 
} 
}, { 
"_index" : "myindex", 
"_type" : "mytype", 
"_id" : "17", 
"_score" : 1.0, 
"_source" : { 
"product_name" : "兑点茶 菩兰原花混合茶12包" 
} 
} ] 
} 
}

#!/bin/sh 
bin=$JDBC_HOME/bin 
lib=$JDBC_HOME/lib 
echo '{ 
"type" : "jdbc", 
"jdbc" : { 
"url" : "jdbc:mysql://172.16.10.XXX:3306/db_car", 
"user" : "write", 
"password" : "write", 
"sql" : "select id,title,fnStripTags(content) as content,click_num,keywords,UNIX_TIMESTAMP(updated) as update_time from t_knowledgenew", 
"elasticsearch" : { 
"cluster": "my-application", 
"host" : "172.16.170.XXX", 
"port" : 9300 
}, 
"index" : "knowledge_index", 
"type" : "knowledge" 
} 
}' | java / 
-cp "${lib}/*" / 
-Dlog4j.configurationFile=${bin}/log4j2.xml / 
org.xbib.tools.Runner / 
org.xbib.tools.JDBCImporter

curl -XGET 'http://XXX.XXX.XXX.XXX:9200/knowledge_index/knowledge/_search?pretty'   

# curl -XDELETE 'http://172.16.170.122:9200/index_name'