日志服务最近在原有 30+ 种数据采集渠道 基础上,新增 MySQL Binlog、MySQL select 等数据库方案,仍然主打快捷、实时、稳定、所见即所得的特点。
以下我们以用户登录数据库作为案例。公司内非常多的人员依赖于用户登录数据以及其衍生出来的相关数据:
接下来我们将演示如何在10分钟内手把手完成从 binlog 采集到查询、告警、搭建报表等全过程,满足各个老板们的需求:
环境准备
数据库
MySQL 类型数据库(使用 MySQL 协议,例如 RDS、DRDS 等),数据库开启 binlog,且配置 binlog 类型为 ROW 模式(RDS 默认开启)
用户登录表结构
用户登录表中记录了登录 id、登录时间、登录 ip、登录设备、用户 id、登录结果、连续登录失败次数、下一次校验类型等信息。其中登录验证规则如下:
用户登录时表的更新方案
对于方案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分钟后,点击预览可以看到状态数据已经采集上来(logtail 的 binlog 采集会额外上传数据操作类型、GTID 等信息):
自定义查询与分析
到这一步我们就可以满足客服和 BI 的需求了:查询/关联查询。例如:
用户登录大盘
现在我们来搭建 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识别函数章节)
统计 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
用户登录大盘搭建
根据上一节的统计结果,我们搭建出了用户登录信息的仪表盘,可以向 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_percentage
将该查询存为快速查询 abnormal_login,并设置告警。
数据备份
用户登录数据,一般建议在日志服务存储一段时间(30天、半年、1年等)用于实时的查询和分析,但对于历史数据还需要保存下来,便于后续的审计、大数据挖掘与分析等。这里我们使用日志服务的投递功能,将数据投递到 OSS 进行长期的归档存储。审计员来了想看多少年前的数据都有!
转自:『云栖社区』公众号