logstash同步mysql到elasticsearch
经过大量的测试发现,使用logstash的方法是最好的。
1.安装logstash
#下载logstash最新版本,由于我elasticsearch使用的版本是5x,所以我这里用5x版本
wget https://artifacts.elastic.co/downloads/logstash/logstash-5.6.9.tar.gz
#安装logstash并且下载mysql驱动,下载mysql驱动去官网下载最新版本即可,我这里省略
tar xvzf logstash-5.6.9.tar.gz
cd logstash-5.6.9
2.创建mapping
curl -XPUT 'localhost:9200/user' -H 'Content-Type: application/json' -d'
{
"mappings": {
"log": {
"properties": {
"user_id": { "type": "keyword" },
"user_name": { "type": "keyword" },
"login_date": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss" }
}
}
}
}'
3.创建配置文件
#logstash的配置文件需要自己创建,一下是我自己的配置文件
cat mysql_conf/mysql.conf
input {
stdin {
}
jdbc {
#数据库地址
jdbc_connection_string => "jdbc:mysql://localhost:3601/user"
jdbc_user => "admin"
jdbc_password => "passwd"
#数据库驱动路径
jdbc_driver_library => "/soft/logstash-5.6.9/mysql-connector-java-5.1.46/mysql-connector-java-5.1.46-bin.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
#sql路径,也可以是直接指定sql语句,字段需要换成 statement =>
statement_filepath => "/soft/logstash-5.6.9/pay_test.sql"
#是否开启记录追踪
record_last_run => "true"
#是否需要追踪字段,如果为true,则需要指定tracking_column,默认是timestamp
use_column_value => "true"
#指定追踪的字段,这里需要注意的是,建议选择主键字段,如果选择日期需要为实时表
tracking_column => "id"
#追踪字段的类型,目前只有数字和时间类型,默认是数字类型
#tracking_column_type => "number"
#设置时区
jdbc_default_timezone =>"Asia/Shanghai"
#是否每次清除last_run_metadata_path的内容
clean_run => "false"
#这里可以手动设置:sql_last_value的值,默认时间是1970-01-01,默认数字是0
last_run_metadata_path => "/soft/logstash-5.6.9/logstash_jdbc_last_run"
#多久同步一次
schedule => "*/5 * * * *"
#是否分页
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
}
}
filter {
#jdbc默认json,暂时没找到修改方法
json {
source => "message"
remove_field => ["message"]
}
mutate { #需要移除的字段
remove_field => "@timestamp"
remove_field => "type"
remove_field => "@version"
}
}
output {
elasticsearch {
hosts => "localhost:9200" #elasticsearch地址
index => "user" #elasticsearch索引
document_id => "%{id}" #elasticsearch的id,该值需要唯一,如果不唯一就不要加这个字段,默认生成
document_type => "log" #elasticsearch的type
}
}
4.sql文件
select * from user where id >:sql_last_value
插件将以sql_last_value存储在配置中的元数据文件的形式持久保存参数last_run_metadata_path。在查询执行后,该文件将被更新为当前值sql_last_value。下一次管道启动时,该值将通过从文件中读取来更新。如果 clean_run设置为true,则该值将被忽略,sql_last_value并将设置为1970年1月1日,如果use_column_value为true,则为0 ,就像没有执行任何查询一样。
5.启动logstash
./bin/logstash -f /mysql_conf/mysql.conf
当执行成功后,会观察到es有数据增加。
6.注意事项
1).建议只做增量备份,如果mysql做物理删除,需要手动删除es数据
2).默认情况下没分钟同步一次数据库,需要定义好更新条件
3).如果mysql表里的data类型没有时区则需要手动创建mapping,否则同步到es里时,时间会增加8小时,解决方法是将mysql表里的data类型改成字符串,当同步到es里时,会自动修改成时间类型。