Logstash同步500GMySQL数据到ElasticSearch
前言
记录一次Mysql500G日志表数据同步到ElasticSearch
一、下载Logstash
到官网去下载ElasticSearch相同版本的Logstash，我使用的ElasticSeash版本是elasticsearch-7.6.2，所以下载logstash-7.6.2
下载地址
https://www.elastic.co/cn/downloads/past-releases#logstash
文档地址
https://www.elastic.co/guide/en/logstash/current/index.html
二、配置
1，sql语句优化
首先编写sql，由于数据量过大，这里不采用Logstash的默认分页，当偏移量过大会严重影响Mysql的查询性能，所以采用预编译的方式注入参数，根据日志表的自增主键过滤后再取出数据。
创建文件log_pre.sql，内容如下：
SELECT
LOGID AS id,
LOGTIME AS logTime,
LOGTYPE AS logType,
LOGUSER AS userName,
COUSERNAME AS coUserName,
ELAPSEDTIME AS elapsedTime,
NODEKEY AS nodeKey,
LOGDESC AS msg,
HOSTIP AS hostIP,
COAPP AS coApp,
COSESS AS coSess,
LOGRESULT AS result,
SIGNATURE AS signature,
CALLER AS caller,
DETAIL AS detail,
PROXYIPS AS proxyIPs,
USERAGENT AS userAgent,
REGFROM AS regFrom,
REGCOAPP AS regCoApp
FROM
log
WHERE
LOGID > ?
limit 40000
2，编写logstash启动配置文件
创建文件db2es.conf，内容如下：
jdbc详细配置参数参考后面的四、JDBC配置说明
input {
jdbc {
#数据库连接
jdbc_connection_string => "jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=false"
#用户名
jdbc_user => "root"
#密码
jdbc_password => "root"
#mysql驱动包
jdbc_driver_library => "/data1/logstash-7.6.2/config/mysql-connector-java-5.1.46.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
#查询的sql文件路径
statement_filepath => "/data1/logstash-7.6.2/config/log_pre.sql"
#准备好的语句的绑定值数组，以下配置是记录每次执行完成后的id这一列的值，放入下次预编译的sql语句中，第一次是没有值的，所以需要把初始值放入last_run_metadata_path对应的文件中
prepared_statement_bind_values => [":sql_last_value"]
#取个名字
prepared_statement_name => "log"
#是否打开取个名字
use_prepared_statements => true
#一秒同步一次，这里的秒级表达式和cron表达式有点不一样，是6个*
schedule => "* * * * * *"
lowercase_column_names => false
clean_run => false
use_column_value => true
#记录的id这一列
tracking_column => id
#列的数据类型
tracking_column_type => "numeric"
#是否记录上一次执行的位置
record_last_run => true
#记录上一次执行位置的文件
last_run_metadata_path => "/data1/logstash-7.6.2/config/log_metadata"
}
}
filter {
mutate {
remove_field => ["@version", "@timestamp"]
}
}
output {
elasticsearch {
#es的hosts，集群部署改为["ip:port","ip:port"]
hosts => "192.168.1.55:9200"
#索引名称
index => "log_2017-2020"
#这里注释掉是不填充es文档的id，让es自动生成
#document_id => "%{id}"
document_type => "_doc"
#索引模板文件路径
template => "/data1/logstash-7.6.2/config/log_2017-2020.json"
#索引模板名称
template_name => "log_2017-2020"
#如果存在是否重写索引模板
template_overwrite => "true"
#用户名密码，没有则不配置
user => "elastic"
password => "pwd"
}
stdout {
#codec => json_lines
#调试完成关闭该选项，这个会把数据详情也打印出来，日志量非常大，要注意关闭该项
#codec =>rubydebug {}
}
}
附带索引模板，内容如下
创建log_2017-2020.json文件
{
"aliases": {
"log": {}
},
"index_patterns": "log_2017-2020",
"mappings": {
"properties": {
"caller": {
"type": "text"
},
"coApp": {
"type": "keyword"
},
"coSess": {
"type": "keyword"
},
"coUserName": {
"type": "keyword"
},
"detail": {
"type": "text"
},
"elapsedTime": {
"type": "long"
},
"hostIP": {
"type": "keyword"
}
"logTime": {
"type": "date"
},
"logType": {
"type": "byte"
},
"msg": {
"type": "keyword"
},
"nodeKey": {
"type": "keyword"
},
"proxyIPs": {
"type": "keyword"
},
"regCoApp": {
"type": "keyword"
},
"regFrom": {
"type": "keyword"
},
"result": {
"type": "integer"
},
"signature": {
"type": "keyword"
},
"userAgent": {
"type": "text"
},
"userName": {
"type": "keyword"
}
}
},
"order": 0,
"settings": {
"number_of_replicas": 0,
"number_of_shards": 12,
"translog": {
"durability": "async",
"flush_threshold_size": "512m",
"sync_interval": "120s"
}
}
}
把启动配置文件db2es.conf，索引模板文件log_2017-2020.json，mysql驱动包mysql-connector-java-5.1.46.jar放到/data1/logstash-7.6.2/config/目录下
3，优化配置
1，设置堆大小
在jvm.options 配置文件中设置jvm堆大小。
对于典型的摄取方案，建议的堆大小应不小于4GB且不大于8GB。
以下是一些有关调整JVM堆大小的其他技巧：
- 如果堆大小太低，CPU利用率可能会不必要地增加，从而导致JVM不断进行垃圾回收。您可以通过加倍堆大小来检查此问题，以查看性能是否有所提高。
- 不要增加堆大小超过物理内存量。为操作系统和其他进程留出至少1GB的可用空间。
- 将最小（Xms）和最大（Xmx）堆分配大小设置为相同的值，以防止在运行时调整堆大小，这是一个非常昂贵的过程。
2，设置管道
修改logstash.yml
# 工作线程数
pipeline.workers: 8
#管道批次大小
pipeline.batch.size: 10000
#延迟时间
pipeline.batch.delay: 50
3，队列持久化到磁盘
修改logstash.yml
当发生异常情况，比如logstash重启，有可能发生数据丢失，可以选择logstash持久化到磁盘，修改之前重启logstash数据丢失，修改之后重启logstash数据不丢失。以下是具体操作：
queue.type: persisted
path.queue: /data1/logstash-7.6.2/data #队列存储路径；如果队列类型为persisted，则生效
queue.page_capacity: 250mb #队列为持久化，单个队列大小
queue.max_events: 0 #当启用持久化队列时，队列中未读事件的最大数量，0为不限制
queue.max_bytes: 1024mb #队列最大容量
queue.checkpoint.acks: 1024 #在启用持久队列时强制执行检查点的最大数量,0为不限制
queue.checkpoint.writes: 1024 #在启用持久队列时强制执行检查点之前的最大数量的写入事件，0为不限制
queue.checkpoint.interval: 1000 #当启用持久队列时，在头页面上强制一个检查点的时间间隔
修改完后，重启logstash
三、启动
#!/bin/sh
nohup bin/logstash -f config/db2es.conf &
1，动态加载配置文件
新增参数 --config.reload.automatic
注意：当在命令行中使用-e来传入配置信息的时候，–config.reload.automatic就不能起作用。
默认情况下，logstash每个3s会检查一次配置信息是否更改。如果要修改这个间隔时间，可以使用--config.reload.interval <interval>选项。其中，interval规定了logstash检查配置文件的时间间隔，以秒为单位。
nohup bin/logstash -f config/db2es.conf --config.reload.automatic &
四、JDBC配置说明
JDBC可配置的参数说明
| 参数 | 作用 | 参数类型 |
|---|---|---|
| clean_run | 是否应保留先前的运行状态 | boolean |
| columns_charset | 特定列的字符编码 | hash |
| connection_retry_attempts | 尝试连接数据库的最大次数 | number |
| connection_retry_attempts_wait_time | 两次尝试之间休眠的秒数 | number |
| jdbc_connection_string | JDBC连接字符串 | string |
| jdbc_default_timezone | 时区转换。 | string |
| jdbc_driver_class | JDBC驱动程序类 | string |
| jdbc_driver_library | 第三方驱动程序库的JDBC驱动程序库路径。 | string |
| jdbc_fetch_size | JDBC提取大小。 | number |
| jdbc_page_size | JDBC页大小 | number |
| jdbc_paging_enabled | JDBC启用分页 | boolean |
| jdbc_password | JDBC密码 | password |
| jdbc_password_filepath | JDBC密码文件名 | |
| jdbc_pool_timeout | PoolTimeoutError之前等待获取连接的秒数 | number |
| jdbc_user | JDBC用户 | string |
| jdbc_validate_connection | 使用前验证连接。 | boolean |
| jdbc_validation_timeout | 验证连接的频率（以秒为单位） | number |
| last_run_metadata_path | 上次运行时间的文件路径 | string |
| lowercase_column_names | 是否强制使用标识符字段的小写 | boolean |
| parameters | 查询参数的哈希，例如 { “target_id” => “321” } | hash |
| plugin_timezone | 将时间戳偏移到UTC以外的时区，则可以将此设置设置为local，插件将使用OS时区进行偏移调整。 | string 可选参数 [“local”, “utc”] |
| prepared_statement_bind_values | 准备好的语句的绑定值数组。 | array |
| prepared_statement_name | 准备好的语句的名称 | string |
| record_last_run | 是否保存状态 | boolean |
| schedule | 定期运行语句的时间表，例如Cron格式：“ * * * * *”（每分钟，每分钟执行一次查询） | string |
| sequel_opts | 连接池的最大连接数 | hash |
| sql_log_level | 记录SQL查询的日志级别 | string 可选参数[“fatal”, “error”, “warn”, “info”, “debug”] |
| statement | 执行的语句的内容 | string |
| statement_filepath | 执行的语句的文件的路径 | |
| tracking_column | 要跟踪的列use_column_value | string |
| tracking_column_type | 跟踪列的类型 | |
| use_column_value | 设置为时true，将定义的 tracking_column值用作:sql_last_value。设置为时false，:sql_last_value反映上一次执行查询的时间。 | string 可选参数[“numeric”, “timestamp”] |
| use_prepared_statements | 设置为时true，启用prepare语句用法 | boolean |
552
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
