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

以下我們以用戶登錄數(shù)據(jù)庫(kù)作為案例。公司內(nèi)非常多的人員依賴于用戶登錄數(shù)據(jù)以及其衍生出來的相關(guān)數(shù)據(jù)：

• 老板要看大屏，每天UV、PV增長(zhǎng)在哪里？

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

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

• BI需要分析用戶行為，數(shù)據(jù)分析如何關(guān)聯(lián)用戶登錄數(shù)據(jù)？

• 審計(jì)上門了，請(qǐng)把您3年前用戶的登錄數(shù)據(jù)拿出來吧？

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

1. MySQL Binlog采集

2. 關(guān)鍵字段索引+統(tǒng)計(jì)設(shè)置

3. 對(duì)異常賬號(hào)進(jìn)行查詢分析

4. 對(duì)異常登錄進(jìn)行告警

5. 配置可視化儀表盤

6. 對(duì)歷史登錄信息備份以備數(shù)據(jù)審計(jì)

環(huán)境準(zhǔn)備

數(shù)據(jù)庫(kù)

mysql類型數(shù)據(jù)庫(kù)（使用mysql協(xié)議，例如RDS、DRDS等），數(shù)據(jù)庫(kù)開啟binlog，且配置binlog類型為ROW模式（RDS默認(rèn)開啟）

用戶登錄表結(jié)構(gòu)

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、登錄時(shí)間、登錄ip、登錄設(shè)備、用戶id、登錄結(jié)果、連續(xù)登錄失敗次數(shù)、下一次校驗(yàn)類型等信息。其中登錄驗(yàn)證規(guī)則如下：

• 正常情況只驗(yàn)證賬號(hào)密碼匹配

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

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

用戶登錄時(shí)表的更新方案

• 方案1：

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

• 方案2：

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

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

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

數(shù)據(jù)采集

安裝logtail

根據(jù)文檔安裝logtail，確認(rèn)版本號(hào)在0.16.0及以上。若低于0.16.0版本請(qǐng)根據(jù)文檔提示升級(jí)到最新版本。

采集配置

    1. 在日志服務(wù)控制臺(tái)創(chuàng)建一個(gè)新的Logstore，采集向?qū)е羞x擇自建軟件中的Mysql binlog

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

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

• 注意：

• 數(shù)據(jù)庫(kù)開啟binlog且為ROW模式（RDS默認(rèn)支持），使用的賬戶具有mysql slave權(quán)限以及需要采集的數(shù)據(jù)表的select權(quán)限。

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

• 其他請(qǐng)參考binlog采集中使用限制

建立索引

配置應(yīng)用到機(jī)器組后，進(jìn)入索引查詢配置頁面。在鍵值索引屬性中配置以下索引項(xiàng)：

數(shù)據(jù)預(yù)覽

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

• 對(duì)于修改的事件，Logtail會(huì)同時(shí)采集修改前和修改后的數(shù)據(jù)，修改前的數(shù)據(jù)以old_開頭。因此我們可以基于修改前后的數(shù)據(jù)對(duì)比查找登錄ip變化的相關(guān)記錄。

• 

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

自定義查詢與分析

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

1. 用戶反饋賬號(hào)信息被篡改了，客服通過日志服務(wù)，查詢?cè)撚脩魪纳洗蔚卿浀浆F(xiàn)在的登錄信息：login_id : 256525，發(fā)現(xiàn)其中有一條登錄日志；繼續(xù)查詢登錄地址login_id : 256525 | select ip_tp_province(login_ip) as login_province, ip_tp_country(login_ip) as login_country，發(fā)現(xiàn)是在國(guó)外登錄的，因此很有可能該用戶賬號(hào)泄漏或被攻破了。

2. 用戶反饋?zhàn)约旱馁~號(hào)被限制登錄了，客服通過日志服務(wù)，查詢?cè)撚脩粝拗频卿浨暗南嚓P(guān)登錄信息：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ā)現(xiàn)該用戶在多個(gè)省異常登錄失敗了很多次。

• 查詢相關(guān)使用幫助參見日志服務(wù)查詢

用戶登錄大盤

現(xiàn)在我們來搭建CEO要的大盤，先準(zhǔn)備一些基礎(chǔ)的統(tǒng)計(jì)信息：

• 統(tǒng)計(jì)一天的UV&PV

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

• 查看登錄設(shè)備分布

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

• 每5分鐘統(tǒng)計(jì)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 

統(tǒng)計(jì)地理位置分布

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

• 統(tǒng)計(jì)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 

• 統(tǒng)計(jì)省份分布（使用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 

用戶登錄大盤搭建

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

• 儀表盤搭建參見日志服務(wù)儀表盤設(shè)置

異常登錄告警

異常登錄都會(huì)有誤判的可能性，因此正常情況下會(huì)有少部分異常登錄的情況，但異常登錄占比要小于1%。這里我們?yōu)橛脩舻卿浽O(shè)置一個(gè)異常登錄的告警：若當(dāng)異常登錄占總登錄的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，并設(shè)置告警。

• 告警設(shè)置參見日志服務(wù)告警設(shè)置

數(shù)據(jù)備份

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