最近在原有30+種數據采集渠道 基礎上，新增MySQL Binlog、MySQL select等數據庫方案，仍然主打快捷、實時、穩(wěn)定、所見即所得的特點。

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

• 老板要看大屏，每天UV、PV增長在哪里？

• 安全要監(jiān)控登錄是否異常，現在用戶賬戶是否遭到集體攻擊？

• 客戶小二接到用戶反饋，如何實時查詢用戶登錄信息？

• BI需要分析用戶行為，數據分析如何關聯用戶登錄數據？

• 審計上門了，請把您3年前用戶的登錄數據拿出來吧？

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

1. MySQL Binlog采集

2. 關鍵字段索引+統計設置

3. 對異常賬號進行查詢分析

4. 對異常登錄進行告警

5. 配置可視化儀表盤

6. 對歷史登錄信息備份以備數據審計

環(huán)境準備

數據庫

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

用戶登錄表結構

CREATE 
 TABLE `user_login` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT  
COMMENT 'id', `login_time` datetime NOT NULL, `login_ip` varchar(10) NOT 
 NULL DEFAULT '', `dev_type` varchar(10) NOT NULL, `usr_id` int(11)  
unsigned NOT NULL,`login_result` varchar(10) unsigned NOT  
NULL,`login_err_times` int(10) unsigned NOT NULL,`next_verify_type`  
varchar(10) NOT NULL,PRIMARY KEY (`id`), KEY `usr_id_index` (`usr_id`) ) 

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

• 正常情況只驗證賬號密碼匹配

• 若用戶連續(xù)登錄失敗超過3次或者當前ip和上次登錄ip不在同一省，下次登錄將彈出驗證碼

• 若用戶連續(xù)登錄失敗超過5次，則下次登錄將使用手機驗證碼

用戶登錄時表的更新方案

• 方案1：

  每次用戶登錄，在user_login中新增一條記錄，記錄登錄的ip、設備類型、時間信息

• 方案2：

  考慮到用戶數量非常多，如果每次用戶登錄都在user_login中新增一條記錄，數據量會非常大，所以每次用戶登錄時，只會根據usr_id更新update表中的數據

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

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

數據采集

安裝logtail

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

采集配置

    1. 在日志服務控制臺創(chuàng)建一個新的Logstore，采集向導中選擇自建軟件中的Mysql binlog

    2. 在配置頁面中輸入binlog采集配置，如下：

{ 
 "inputs": [ { "type": "service_canal", "detail": { "Host":  
"************.mysql.rds.aliyuncs.com", "User" : "root", "Password":  
"*******", "IncludeTables": [ "user_info\.user_login" ] } } ]} 

• 注意：

• 數據庫開啟binlog且為ROW模式（RDS默認支持），使用的賬戶具有mysql slave權限以及需要采集的數據表的select權限。

• binlog支持IncludeTables和ExcludeTables過濾，格式均為正則表達式

• 其他請參考binlog采集中使用限制

建立索引

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

數據預覽

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

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

• 

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

自定義查詢與分析

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

1. 用戶反饋賬號信息被篡改了，客服通過日志服務，查詢該用戶從上次登錄到現在的登錄信息：login_id : 256525，發(fā)現其中有一條登錄日志；繼續(xù)查詢登錄地址login_id : 256525 | select ip_tp_province(login_ip) as login_province, ip_tp_country(login_ip) as login_country，發(fā)現是在國外登錄的，因此很有可能該用戶賬號泄漏或被攻破了。

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，發(fā)現該用戶在多個省異常登錄失敗了很多次。

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

用戶登錄大盤

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

• 統計一天的UV&PV

select count(distinct(usr_id)) as uv, count(1) as pv 

• 查看登錄設備分布

select dev_type, count(1) as count group by dev_type 

• 每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 1440 

統計地理位置分布

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

• 統計top10的city（使用ip_to_city）

select ip_to_city(login_ip) as login_city, count(1) as count group by login_city order by count desc limit 10 

• 統計省份分布（使用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 100 

用戶登錄大盤搭建

根據上一節(jié)的統計結果，我們搭建出了用戶登錄信息的儀表盤，可以向CEO匯報了。

• 儀表盤搭建參見日志服務儀表盤設置

異常登錄告警

異常登錄都會有誤判的可能性，因此正常情況下會有少部分異常登錄的情況，但異常登錄占比要小于1%。這里我們?yōu)橛脩舻卿浽O置一個異常登錄的告警：若當異常登錄占總登錄的1%則觸發(fā)告警。

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_percentage 

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

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

數據備份

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