概述
服务器
服务器hostname 类型
mysqlAm mysql集群A主库
mysqlAs mysql集群A从库库
mysqlBm mysql集群B主库
mysqlBs mysql集群B从库库
es1 elasticseatch Master
es2 elasticseatch
目标
使用filebeat 导入到es 收集所有集群的mysql slow log 和errorlog
安装
以下在mysql服务器上进行 本例为：
mysqlAm, mysqlAs, mysqlBm, mysqlBs
下载filebeat
这里下载: https://www.elastic.co/products/beats/filebeat
我们这里使用filebeat-6.5.4-x86_64.rpm
安装filebeat
# rpm -ivh filebeat-6.5.4-x86_64.rpm
开启filebeat的mysql模块
filebeat modules enable mysql
filebeat modules list
配置filebeat
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*"]
改写filebeat mysql module的处理逻辑使用percona版本（可忽略）
可以看到 在/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
最后的的ingest为
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 }}"
}
}]
}
最后的config为
添加了胡烈'^# 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
更新pipeline
filebeat setup --modules mysql --pipelines
试启动filebeat
filebeat -e
配置logrotate
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
查看filebeat的记录
more /var/lib/filebeat/registry
后端启动filebeat
systemctl start filebeat
systemctl status filebeat
kibana查看
trouble shoot
如果数据全是：
说明/usr/share/filebeat/module/mysql/slowlog/ingest/pipeline.json
中的patterns 不对请用grok测试
