Logstash 处理 Mysql Slow Log

franket

发布于 2022-01-20 11:30:53

82200

代码可运行

文章被收录于专栏：技术杂记技术杂记

运行总次数：0

代码可运行

前言

logstash 可以处理各类日志，对于Apache和Nginx的访问日志，由于遵循统一标准，在 grok patterns 中已经有现成定义，一条 COMBINEDAPACHELOG 就可以匹配

但是对于 Mysql 的 Slow Log 来说，情况就要复杂得多，主要表现在格式不统一，字段比较随意，有些字段会偶尔出现，偶尔消失，sql内容也长段不一行数不定，所以目前也没有一个成熟的预定好的 patterns 可以拿来就用

可见统一接口，统一规范的重要性，在我看来，统一标准后，可以为更大规模更大范围的协作带来可能，规避很多不必要的重复劳动，节省下来的宝贵时间可以用来做更有挑战和更有价值的事情

下面是不同版本mysql慢日志的格式

mysql 5.1.36的slowlog：

# Time: 151202 17:29:24
# User@Host: root[root] @  [192.168.35.89]
# Query_time: 6.578696  Lock_time: 0.000039 Rows_sent: 999424  Rows_examined: 999424
SET timestamp=1449048564;
select * from users_test;

percona server 5.5.34 的slowlog：

# User@Host: root[root] @  [192.168.35.62]
# Thread_id: 1164217  Schema: mgr  Last_errno: 0  Killed: 0
# Query_time: 0.371185  Lock_time: 0.000056  Rows_sent: 0  Rows_examined: 0  Rows_affected: 2  Rows_read: 0
# Bytes_sent: 11
SET timestamp=1449105655;
REPLACE INTO  edgemgr_dbcache(id, type, data, expire_time)  VALUES(UNHEX('ec124ee5766c4a31819719c645dab895'), 'sermap', '{\"storages\":{\"sg1-s1\":[{\"download_port\":9083,\"p2p_port\":9035,\"rtmp_port\":9035,\"addr\":\"{\\\"l\\\":{\\\"https://192.168.35.40:9184/storage\\\":\\\"\\\"},\\\"m\\\":{},\\\"i\\\":{\\\"https://192.168.35.40:9184/storage\\\":\\\"\\\"}}\",\"cpu\":6,\"mem\":100,\"bandwidth\":0,\"disk\":0,\"dead\":0}]},\"lives\":{}}', '2016-01-02 09:20:55');

mysql 5.6.17的slowlog：

# Time: 151202 16:09:54
# User@Host: root[root] @  [192.168.35.89]  Id: 84589
# Query_time: 7.089324  Lock_time: 0.000112 Rows_sent: 1  Rows_examined: 33554432
SET timestamp=1449043794;
select count(*) from t1;

percona server 5.6.27 的slowlog：

# Time: 151217  2:00:56
# User@Host: taobao[taobao] @ regular_exp [192.168.35.23]  Id:  1235
# Schema: bat_db  Last_errno: 0  Killed: 0
# Query_time: 3.101086  Lock_time: 0.181175  Rows_sent: 0  Rows_examined: 360321  Rows_affected: 103560
# Bytes_sent: 58
SET timestamp=1450288856;
create table just_for_temp_case as
  select '2015-12-16' as the_date,
         t2.user_id,
         t1.stuff_no,
         count(*) as buy_times
  from stuff_entries as t1
  join bill as t2
  on t1.orderserino = t2.id
  where t2.notification_ts < '2015-12-17 00:00:00'
    and t2.notification_ts >= '2015-09-18 00:00:00'
  group by t2.user_id,
           t1.stuff_no;
# Time: 151217 18:03:47
# User@Host: taobao[taobao] @ regular_exp [192.168.35.23]  Id:  1667
# Schema: bat_db  Last_errno: 0  Killed: 0
# Query_time: 2.857983  Lock_time: 0.000095  Rows_sent: 1  Rows_examined: 452253  Rows_affected: 0
# Bytes_sent: 64
use bat_db;
SET timestamp=1450346627;
SELECT COUNT(*) FROM `bill`  WHERE `bill`.`type` IN ('manu_title') AND ( bill.real_name = 'testuser');

本文系转载，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

sql