mysql【mycat】作为数据源同步至Elasticsearch
1、准备工作
(1)elasticsearch(eg:https://artifacts.elastic.co/downloads/elasticsearch/elasticsearch-6.1.1.zip)
(2)logstash (eg:https://artifacts.elastic.co/downloads/logstash/logstash-6.1.1.zip)
注意点:对应工具的版本号最好一致,可以避免一些不必要的“坑”
2、启动Elasticsearch
【yourPath】\elasticsearch-6.1.1\bin\elasticsearch.bat
3、配置logstash
(1)在【yourPath】\logstash-6.1.1创建文件夹(eg:mysql_conf)
(2)将对应版本的mysql驱动包放置到上述目录下(eg:mysql-connector-java-5.1.46-bin.jar)
(3)准备同步mysql脚本(eg:mysql_xxxx.sql)
select * from table
(4)在上述文件夹中创建文件(eg:mysql_xxxx.conf)
文件中的内容如下:
input {
jdbc {
#数据库
jdbc_connection_string => "jdbc:mysql://xxx.xxx.xxx.xxx:3306/database?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true"
#用户名密码
jdbc_user => "root"
jdbc_password => "xxxxxxxxxx"
#jar包的位置
jdbc_driver_library => "【yourPath】\logstash-6.1.1\mysql_config\mysql-connector-java-5.1.46-bin.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
#sql语句的位置
statement_filepath => "【yourPath】\logstash-6.1.1\mysql_config\jdbc_xxx.sql"
schedule => "* * * * *"
type => "jdbc"
}
}
filter {
json {
source => "message"
remove_field => ["message"]
}
}
output {
elasticsearch {
hosts => ["localhost:9200"]
# index名
index => "myindex"
# 需要关联的数据库中有有一个id字段,对应索引的id号
document_id => "%{id}"
}
stdout {
codec => json_lines
}
}
4、启动logstash
【yourPath】\logstash-6.1.1\bin\logstash.bat -f 【yourPath】\logstash-6.1.1\mysql_config\mysql_xxx.conf
5、稍后即可查看Elasticsearch中的数据结果