logstash 通过mysql 慢日志了解(?m)

<pre name="code" class="html"># User@Host: zjzc_app[zjzc_app] @  [10.171.243.55]  Id: 1621705
# Query_time: 10.666552  Lock_time: 0.000392 Rows_sent: 15  Rows_examined: 24829551
SET timestamp=1452566321;
SELECT 
    *
FROM
    (SELECT 
        c.sn,
            c.userNick,
            c.mobilePhone,
            DATE_FORMAT(c.registerTime, '%Y/%m/%d') registerTime,
            cp.personName,
            cp.personCardNo,
            cp.gender,
            cp.personAuthStatus,
            IF(1407 = asm.clientManagerSn
                OR 1407 IS NULL, cei.policyAreaSn, NULL) AS policyAreaSn,
            IF(1407 = asm.clientManagerSn
                OR 1407 IS NULL, area.areaName, NULL) AS policyAreaName,
            (SELECT 
                    IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0)
                FROM
                    ProductRepayment pr, Product p
                WHERE
                    pr.productSn = p.sn
                        AND pr.clientSn = c.sn
                        AND pr.status = '1'
                        AND DATEDIFF(p.realValueEndDate, '2016-01-12') <= 7
                        AND DATEDIFF(p.realValueEndDate, '2016-01-12') >= 0) investBalance,
            (SELECT 
                    IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0) + cai.availableBalance + cai.frozenWithDraw + cai.frozenPay
                FROM
                    ProductRepayment
                WHERE
                    clientSn = c.sn AND status = '1') totalBalance,
            (cai.frozenWithDraw + cai.frozenPay) frozenBalance,
            cai.availableBalance
    FROM
        Client c
    LEFT JOIN ClientExtraInfo cei ON c.sn = cei.clientSn
    LEFT JOIN Area area ON cei.policyAreaSn = area.sn
    LEFT JOIN AssignClientManager asm ON asm.clientSn = c.sn, ClientPersonalInfo cp, ClientAssetInfo cai
    WHERE
        c.sn = cp.clientSn AND asm.status = '1'
            AND c.sn = cai.clientSn
            AND c.sn IN (SELECT 
                asm.clientSn
            FROM
                AssignClientManager asm
            WHERE
                asm.clientManagerSn IN (1407)
                    AND asm.status = '1')) t
ORDER BY sn DESC
LIMIT 1020 , 15;




表达式:
(?m)\s*# User@Host:\s+\S+\[%{USER:user}\]\s+@\s+\[%{IP:clientip}\]\s+(?<id>(\S+:\s+\S+))\s*#\s+Query_time:\s+%{NUMBER:Query_time}\s+Lock_time: %{NUMBER:lock_time}\s+Rows_sent: %{NUMBER:rows_sent}\s+Rows_examined: %{NUMBER:rows_examined}\s*
\s*SET\s+timestamp=%{NUMBER:timestamp};\s*(?<query>(\s*\S+\s*).*)\s*



输出:
{
  "user": [
    [
      "zjzc_app"
    ]
  ],
  "clientip": [
    [
      "10.171.243.55"
    ]
  ],
  "id": [
    [
      "Id: 1621705"
    ]
  ],
  "Query_time": [
    [
      "10.666552"
    ]
  ],
  "lock_time": [
    [
      "0.000392"
    ]
  ],
  "rows_sent": [
    [
      "15"
    ]
  ],
  "rows_examined": [
    [
      "24829551"
    ]
  ],
  "timestamp": [
    [
      "1452566321"
    ]
  ],
  "query": [
    [
      "SELECT \n    *\nFROM\n    (SELECT \n        c.sn,\n            c.userNick,\n            c.mobilePhone,\n            DATE_FORMAT(c.registerTime, '%Y/%m/%d') registerTime,\n            cp.personName,\n            cp.personCardNo,\n            cp.gender,\n            cp.personAuthStatus,\n            IF(1407 = asm.clientManagerSn\n                OR 1407 IS NULL, cei.policyAreaSn, NULL) AS policyAreaSn,\n            IF(1407 = asm.clientManagerSn\n                OR 1407 IS NULL, area.areaName, NULL) AS policyAreaName,\n            (SELECT \n                    IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0)\n                FROM\n                    ProductRepayment pr, Product p\n                WHERE\n                    pr.productSn = p.sn\n                        AND pr.clientSn = c.sn\n                        AND pr.status = '1'\n                        AND DATEDIFF(p.realValueEndDate, '2016-01-12') <= 7\n                        AND DATEDIFF(p.realValueEndDate, '2016-01-12') >= 0) investBalance,\n            (SELECT \n                    IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0) + cai.availableBalance + cai.frozenWithDraw + cai.frozenPay\n                FROM\n                    ProductRepayment\n                WHERE\n                    clientSn = c.sn AND status = '1') totalBalance,\n            (cai.frozenWithDraw + cai.frozenPay) frozenBalance,\n            cai.availableBalance\n    FROM\n        Client c\n    LEFT JOIN ClientExtraInfo cei ON c.sn = cei.clientSn\n    LEFT JOIN Area area ON cei.policyAreaSn = area.sn\n    LEFT JOIN AssignClientManager asm ON asm.clientSn = c.sn, ClientPersonalInfo cp, ClientAssetInfo cai\n    WHERE\n        c.sn = cp.clientSn AND asm.status = '1'\n            AND c.sn = cai.clientSn\n            AND c.sn IN (SELECT \n                asm.clientSn\n            FROM\n                AssignClientManager asm\n            WHERE\n                asm.clientManagerSn IN (1407)\n                    AND asm.status = '1')) t\nORDER BY sn DESC\nLIMIT 1020 , 15;"
    ]
  ]
}

如果去掉(?m)

\s*# User@Host:\s+\S+\[%{USER:user}\]\s+@\s+\[%{IP:clientip}\]\s+(?<id>(\S+:\s+\S+))\s*#\s+Query_time:\s+%{NUMBER:Query_time}\s+Lock_time: %{NUMBER:lock_time}\s+Rows_sent: %{NUMBER:rows_sent}\s+Rows_examined: %{NUMBER:rows_examined}\s*
\s*SET\s+timestamp=%{NUMBER:timestamp};\s*(?<query>(\s*\S+\s*).*)\s*

此时匹配:
{
  "user": [
    [
      "zjzc_app"
    ]
  ],
  "USERNAME": [
    [
      "zjzc_app"
    ]
  ],
  "clientip": [
    [
      "10.171.243.55"
    ]
  ],
  "IPV6": [
    [
      null
    ]
  ],
  "IPV4": [
    [
      "10.171.243.55"
    ]
  ],
  "id": [
    [
      "Id: 1621705"
    ]
  ],
  "Query_time": [
    [
      "10.666552"
    ]
  ],
  "BASE10NUM": [
    [
      "10.666552",
      "0.000392",
      "15",
      "24829551",
      "1452566321"
    ]
  ],
  "lock_time": [
    [
      "0.000392"
    ]
  ],
  "rows_sent": [
    [
      "15"
    ]
  ],
  "rows_examined": [
    [
      "24829551"
    ]
  ],
  "timestamp": [
    [
      "1452566321"
    ]
  ],
  "query": [
    [
      "SELECT \n    *"
    ]
  ]
}


在和 codec/multiline 搭配使用的时候，需要注意一个问题，grok 正则和普通正则一样，

默认是不支持匹配回车换行的。就像你需要 =~ //m 一样也需要单独指定，具体写法是在表达式开始位置加 (?m) 标记。

转载于:https://www.cnblogs.com/zhaoyangjian724/p/6199123.html