社区微信群开通啦，扫一扫抢先加入社区官方微信群
社区微信群
服务器hostname 类型
mysqlAm mysql集群A主库
mysqlAs mysql集群A从库库
mysqlBm mysql集群B主库
mysqlBs mysql集群B从库库
es1 elasticseatch Master
es2 elasticseatch
使用filebeat 导入到es 收集所有集群的mysql slow log 和errorlog
mysqlAm, mysqlAs, mysqlBm, mysqlBs
这里下载: https://www.elastic.co/products/beats/filebeat
我们这里使用filebeat-6.5.4-x86_64.rpm
# rpm -ivh filebeat-6.5.4-x86_64.rpm
filebeat modules enable mysql
filebeat modules list
vi /etc/filebeat/filebeat.yml
#============================= Filebeat modules ===============================
filebeat.config.modules:
# Configure what output to use when sending the data collected by the beat.
#索引名称
setup.template.name: "mysql-slow"
setup.template.pattern: "mysql-slow*"
template.overwrite: true
#-------------------------- Elasticsearch output ------------------------------
output.elasticsearch:
# Array of hosts to connect to.
hosts: ["es1:9200","es2:9200"]
index: "mysql-slow-%{+yyyy.MM.dd}"
timeout: 180
# Optional protocol and basic auth credentials.
#如果开启认证请输入密码
#protocol: "http"
#username: "elastic"
#password: "changeme"
#================================ Procesors =====================================
# Configure processors to enhance or manipulate events generated by the beat.
processors:
- add_host_metadata: ~
- add_cloud_metadata: ~
#================================ Logging =====================================
# Sets log level. The default log level is info.
# Available log levels are: error, warning, info, debug
logging.level: info
初始化index template
filebeat setup -e
配是mysql module 信息
vi /etc/filebeat/modules.d/mysql.yml
- module: mysql
# Error logs
error:
enabled: true
# Set custom paths for the log files. If left empty,
# Filebeat will choose the paths depending on your OS.
#输入errorlog的日志位置
var.paths: ["/mysqldata/mysql3306/log/mysql-error.log*"]
# Slow logs
slowlog:
enabled: true
# Set custom paths for the log files. If left empty,
# Filebeat will choose the paths depending on your OS.
#slow的日志位置
var.paths: ["/mysqldata/mysql3306/log/mysql-slow.log*"]
可以看到 在/usr/share/filebeat/module/ 下为filebeat各个module的处理逻辑
在slowlog module下有两个处理程序 error和slwolog
我们拿slowlog为例 可以看到config 和 ingest两个文件夹
config定义了日志的爬取规则
ingest定义了日志存入es的预处理规则
由于percona版本的slow log 和mysql版本的slowlog不一样
我们来看下percona版本的
可以看到多出了很多信息
我们使用kibana的dev tool 之grok
more /usr/share/filebeat/module/mysql/slowlog/ingest/pipeline.json
{
"description": "Pipeline for parsing MySQL slow logs.",
"processors": [{
"grok": {
"field": "message",
"patterns":[
"^# User@Host: %{USER:mysql.slowlog.user}\[(%{WORD:mysql.slowlog.db})?\] @%{ANY}\[(%{IP:mysql.slowlog.ip})?\]%{SPACE}*Id: (%{NUMBER:mysql.slowlog.id})?n# Schema: (%{USER:mysql.slowlog.schema})?%{SPACE}Last_errno%{ANY}n# Query_time
: %{NUMBER:mysql.slowlog.query_time.sec}%{SPACE} Lock_time: %{NUMBER:mysql.slowlog.lock_time.sec}%{SPACE} Rows_sent: %{NUMBER:mysql.slowlog.rows_sent}%{SPACE} Rows_examined: %{NUMBER:mysql.slowlog.rows_examined}%{ANY}n(SET timestamp=%{
NUMBER:mysql.slowlog.timestamp};n)?(?<mysql.slowlog.query>(?<mysql.slowlog.action>\w+)%{SPACE}%{ANY})"
],
"pattern_definitions" : {
"GREEDYMULTILINE" : "(.|n)*",
"ANY": "([\w|\W]*)",
"SPACE": "([\s]*)"
},
"ignore_missing": true
}
}, {
"remove":{
"field": "message"
}
}, {
"date": {
"field": "mysql.slowlog.timestamp",
"target_field": "@timestamp",
"formats": ["UNIX"],
"ignore_failure": true
}
},{
"gsub": {
"field": "mysql.slowlog.query",
"pattern": "n# Time: .*",
"replacement": "",
"ignore_failure": true
}
}],
"on_failure" : [{
"set" : {
"field" : "error.w11",
"value" : "{{ _ingest.on_failure_message }}"
}
}]
}
添加了胡烈'^# Time:.*', '^ Time:.*'的信息
more /usr/share/filebeat/module/mysql/slowlog/config/slowlog.yml
type: log
paths:
{{ range $i, $path := .paths }}
- {{$path}}
{{ end }}
exclude_files: ['.gz$']
multiline:
pattern: '^# User@Host: '
negate: true
match: after
exclude_lines: ['^[/w.]+, Version: .* started with:.*', '^# Time:.*', '^ Time:.*' ] # Exclude the header and time
filebeat setup --modules mysql --pipelines
filebeat -e
vi /etc/logrotate.d/mysql
/mysqldata/mysql3306/log/mysql-error.log {
# create 600 mysql mysql
daily
rotate 3
missingok
delaycompress
postrotate
mysql -e 'select @@global.long_query_time into @lqt_save; set global long_query_time=2000; select sleep(2); FLUSH LOGS; select sleep(2); set global long_query_time=@lqt_save;'
endscript
}
/mysqldata/mysql3306/log/mysql-slow.log {
daily
rotate 30
missingok
delaycompress
postrotate
mysql -e 'select @@global.long_query_time into @lqt_save; set global long_query_time=2000; select sleep(2); FLUSH LOGS; select sleep(2); set global long_query_time=@lqt_save;'
endscript
}
不使用copytruncate
一是因为filebeat记录的是inode copytruncate会改变inode
二是因为copytruncate 会堵塞mysql
原因请查看
https://www.percona.com/blog/2013/04/18/rotating-mysql-slow-logs-safely/
logrotate /etc/logrotate.d/mysql
logrotate -f /etc/logrotate.d/mysql
more /var/lib/filebeat/registry
systemctl start filebeat
systemctl status filebeat
如果数据全是：
说明/usr/share/filebeat/module/mysql/slowlog/ingest/pipeline.json
中的patterns 不对请用grok测试
如果觉得我的文章对您有用，请随意打赏。你的支持将鼓励我继续创作！
