10分鐘搭建MySQL Binlog分析+可視化方案

這裡我們推薦使用方案2+logtail binl這裡我們推薦使用方案2+logtail binl這裡我們推薦使用方案2+logtail binl日誌服務 最近在原有30+種數據採集渠道 基礎上，新增MySQL Binlog、MySQL select等資料庫方案，仍然主打快捷、實時、穩定、所見即所得的特點。

以下我們以用戶登錄資料庫作為案例。公司內非常多的人員依賴於用戶登錄數據以及其衍生出來的相關數據：

• 老闆要看大屏，每天UV、PV增長在哪裡？
• 安全要監控登錄是否異常，現在用戶賬戶是否遭到集體攻擊？
• 客戶小二接到用戶反饋，如何實時查詢用戶登錄信息？
• BI需要分析用戶行為，數據分析如何關聯用戶登錄數據？
• 審計上門了，請把您3年前用戶的登錄數據拿出來吧？

接下來我們將演示如何在10分鐘內手把手完成從binlog採集到查詢、告警、搭建報表等全過程，滿足各個老闆們的需求：

1. MySQL Binlog採集
2. 關鍵欄位索引+統計設置
3. 對異常賬號進行查詢分析
4. 對異常登錄進行告警
5. 配置可視化儀錶盤
6. 對歷史登錄信息備份以備數據審計

環境準備

資料庫

mysql類型資料庫（使用mysql協議，例如RDS、DRDS等），資料庫開啟binlog，且配置binlog類型為ROW模式（RDS默認開啟）

用戶登錄表結構

用戶登錄表中記錄了登錄id、登錄時間、登錄ip、登錄設備、用戶id、登錄結果、連續登錄失敗次數、下一次校驗類型等信息。其中登錄驗證規則如下：

• 正常情況只驗證賬號密碼匹配
• 若用戶連續登錄失敗超過3次或者當前ip和上次登錄ip不在同一省，下次登錄將彈出驗證碼
• 若用戶連續登錄失敗超過5次，則下次登錄將使用手機驗證碼

用戶登錄時表的更新方案

• 方案1：
  每次用戶登錄，在user_login中新增一條記錄，記錄登錄的ip、設備類型、時間信息
• 方案2：
  考慮到用戶數量非常多，如果每次用戶登錄都在user_login中新增一條記錄，數據量會非常大，所以每次用戶登錄時，只會根據usr_id更新update表中的數據

對於方案1，優點是資料庫中保存了所有用戶的登錄信息，缺點是user_login表會存在爆掉的問題，需要定期刪除歷史的數據；對於方案2，優點是user_login表的大小可控，缺點是會丟失歷史用戶的登錄信息。

這裡我們推薦使用方案2+logtail binlog採集組成最優的方案3：用戶最近一次登錄信息依然保存在資料庫中，通過logtail的binlog功能採集user_login表，logtail會將表中的每次修改事件上傳到日誌服務，日誌服務中的數據可設置保存時間，超時自動刪除。同時在日誌服務中，可以對實時採集上來的數據進行查詢、統計、查看報表、監控報警，也支持將數據對接下游流計算、導入Max Compute/OSS等。

數據採集

安裝logtail

根據文檔安裝logtail，確認版本號在0.16.0及以上。若低於0.16.0版本請根據文檔提示升級到最新版本。

採集配置

1. 在日誌服務控制台創建一個新的Logstore，採集嚮導中選擇自建軟體中的Mysql binlog

1. 在配置頁面中輸入binlog採集配置，如下：

• 注意：
• 資料庫開啟binlog且為ROW模式（RDS默認支持），使用的賬戶具有mysql slave許可權以及需要採集的數據表的select許可權。
• binlog支持IncludeTables和ExcludeTables過濾，格式均為正則表達式
• 其他請參考binlog採集中使用限制

建立索引

配置應用到機器組後，進入索引查詢配置頁面。在鍵值索引屬性中配置以下索引項：

數據預覽

應用配置1分鐘後，點擊預覽可以看到狀態數據已經採集上來（logtail的binlog採集會額外上傳數據操作類型、GTID等信息）：

• 對於修改的事件，Logtail會同時採集修改前和修改後的數據，修改前的數據以old_開頭。因此我們可以基於修改前後的數據對比查找登錄ip變化的相關記錄。

• 注意： 若無數據，請檢查配置是否為合法json；若配置正常，請參考數據採集異常排查文檔自助排查

自定義查詢與分析

到這一步我們就可以滿足客服和BI的需求了：查詢/關聯查詢。例如：

1. 用戶反饋賬號信息被篡改了，客服通過日誌服務，查詢該用戶從上次登錄到現在的登錄信息：login_id : 256525，發現其中有一條登錄日誌；繼續查詢登錄地址login_id : 256525 | select ip_tp_province(login_ip) as login_province, ip_tp_country(login_ip) as login_country，發現是在國外登錄的，因此很有可能該用戶賬號泄漏或被攻破了。
2. 用戶反饋自己的賬號被限制登錄了，客服通過日誌服務，查詢該用戶限制登錄前的相關登錄信息：login_id : 256525 | select ip_tp_province(login_ip) as login_province, login_result， count(1) as total group by (login_province,login_result) order by total desc limit 100，發現該用戶在多個省異常登錄失敗了很多次。

• 查詢相關使用幫助參見日誌服務查詢

用戶登錄大盤

現在我們來搭建CEO要的大盤，先準備一些基礎的統計信息：

• 統計一天的UV&PV

• 查看登錄設備分布

• 每5分鐘統計UV&PV分布

* | select count(1) as uv, count(distinct(usr_id)) as pv, from_unixtime( __time__ - __time__ % 300) as time group by __time__ - __time__ % 300 order by time limit 1440n

統計地理位置分布

由於原始的數據中沒有用戶登錄的地理位置分布信息，但我們可以通過ip地址定位到用戶登錄的省市，這裡我們使用日誌服務自帶的ip地址轉換函數（具體參見分析語法IP識別函數章節）

• 統計top10的city（使用ip_to_city）

• 統計省份分布（使用ip_tp_province）

* | select ip_tp_province(login_ip) as login_province, count(1) as count group by login_province order by count desc limit 100n

用戶登錄大盤搭建

根據上一節的統計結果，我們搭建出了用戶登錄信息的儀錶盤，可以向CEO彙報了。

• 儀錶盤搭建參見日誌服務儀錶盤設置

異常登錄告警

異常登錄都會有誤判的可能性，因此正常情況下會有少部分異常登錄的情況，但異常登錄佔比要小於1%。這裡我們為用戶登錄設置一個異常登錄的告警：若當異常登錄佔總登錄的1%則觸發告警。

* | SELECT sum( CASE WHEN ip_tp_province(login_ip)!=ip_tp_province(old_login_ip) then 1 ELSE 0 end ) *1.0 / count(1) as abnormal_login_percentagen

將該查詢存為快速查詢abnormal_login，並設置告警。

• 告警設置參見日誌服務告警設置

數據備份

用戶登錄數據，一般建議在日誌服務存儲一段時間（30天、半年、1年等）用於實時的查詢和分析，但對於歷史數據還需要保存下來，便於後續的審計、大數據挖掘與分析等。這裡我們使用日誌服務的投遞功能，將數據投遞到OSS進行長期的歸檔存儲。審計員來了想看多少年前的數據都有！

相關文章

使用EMR來進行mysqlbinlog日誌准實時處理

如何基於MYSQL做實時計算

原文

作者：元乙，阿里雲日誌服務數據採集客戶端負責人

更多技術乾貨敬請關注云棲社區知乎機構號：阿里云云棲社區 - 知乎