10分钟搭建MySQL Binlog分析+可视化方案

本文涉及的产品
对象存储 OSS,20GB 3个月
日志服务 SLS,月写入数据量 50GB 1个月
文件存储 NAS,50GB 3个月
简介: 日志服务 最近在原有30+种数据采集渠道 基础上,新增MySQL Binlog、MySQL select等数据库方案,仍然主打快捷、实时、稳定、所见即所得的特点。 以下我们以用户登录数据库作为案例,演示如何在10分钟内手把手完成从binlog采集到查询、告警、搭建报表等全过程,满足各个老板们的需求。

日志服务 最近在原有30+种数据采集渠道 基础上,新增MySQL BinlogMySQL select等数据库方案,仍然主打快捷、实时、稳定、所见即所得的特点。


以下我们以用户登录数据库作为案例。公司内非常多的人员依赖于用户登录数据以及其衍生出来的相关数据:


  • 老板要看大屏,每天UV、PV增长在哪里?
  • 安全要监控登录是否异常,现在用户账户是否遭到集体攻击?
  • 客户小二接到用户反馈,如何实时查询用户登录信息?
  • BI需要分析用户行为,数据分析如何关联用户登录数据?
  • 审计上门了,请把您3年前用户的登录数据拿出来吧?

467da918-42be-4abe-b6c5-bc2d87464d7a.png

接下来我们将演示如何在10分钟内手把手完成从binlog采集到查询、告警、搭建报表等全过程,满足各个老板们的需求:


  1. MySQL Binlog采集
  2. 关键字段索引+统计设置
  3. 对异常账号进行查询分析
  4. 对异常登录进行告警
  5. 配置可视化仪表盘
  6. 对历史登录信息备份以备数据审计


环境准备


数据库


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、登录结果、连续登录失败次数、下一次校验类型等信息。其中登录验证规则如下:


  • 正常情况只验证账号密码匹配
  • 若用户连续登录失败超过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等。

93e5ac81-275a-43c4-a1d0-077f21cfc7fb.png

方案1

方案2

方案3

数据库数据量

用户数 * 运行时间 / 登录率

用户数

用户数

数据库压力

支撑写入以及分析,压力大

只更新,压力最小

更新+binlog采集,压力较小

分析能力

基于sql进行分析,数据量大时对数据库影响大

无历史数据,基本不能分析

使用日志服务分析,TB级数据实时查询分析无压力,支持众多分析扩展函数

报表&监控

手动搭建&运维

手动搭建&运维

基于日志服务快速创建仪表盘、配置自定义报警

上下游对接扩展性

手动对接上下游

手动对接上下游

对接流计算实时处理、导入OSS归档存储、对接Max Compute离线分析等


数据采集


安装logtail


根据文档安装logtail,确认版本号在0.16.0及以上。若低于0.16.0版本请根据文档提示升级到最新版本。


采集配置


  1. 在日志服务控制台创建一个新的Logstore,采集向导中选择自建软件中的Mysql binlog

5c667fc5-42a9-4c4b-bcfb-84f341286aa7.png


{
    "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支持IncludeTablesExcludeTables过滤,格式均为正则表达式
  • 其他请参考binlog采集中使用限制


建立索引


配置应用到机器组后,进入索引查询配置页面。在键值索引属性中配置以下索引项:

字段名

类型

别名

分词符

开启统计

_event_

text

dev_type

text

login_ip

text

usr_id

text

next_verify_type

text

login_err_times

long

login_result

text

old_dev_type

text

old_login_ip

text

old_usr_id

text

old_next_verify_type

text

old_login_err_times

long

old_login_result

text


数据预览


应用配置1分钟后,点击预览可以看到状态数据已经采集上来(logtail的binlog采集会额外上传数据操作类型、GTID等信息):


  • 对于修改的事件,Logtail会同时采集修改前和修改后的数据,修改前的数据以old_开头。因此我们可以基于修改前后的数据对比查找登录ip变化的相关记录。


__source__:  11.18.2346.187  
__topic__:    
_db_:  user_info  
_event_:  row_update  
_gtid_:  40684541-b23b-11e7-a198-6c92de20e4c5:32693  
_host_:  ****************mysql.rds.aliyuncs.com  
_id_:  2132923  
_table_:  user_login  
dev_type:  web  
id:  7234226  
login_ip:  101.85.245.155  
login_time:  1511855787
usr_id:  23568755  
login_result:  error
login_err_times:  5
next_verify_type:  sms
old_dev_type:  web  
old_id:  7234226  
old_login_ip:  101.85.245.155  
old_login_time:  1511855781
old_usr_id:  23568755  
old_login_result:  error
old_login_err_times:  4
old_next_verify_type:  id_code


自定义查询与分析


到这一步我们就可以满足客服和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


* | 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汇报了。


d36a43ae-cb4a-4e10-a21a-f548e56f9058.png

异常登录告警


异常登录都会有误判的可能性,因此正常情况下会有少部分异常登录的情况,但异常登录占比要小于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,并设置告警。


配置项

内容

报警规则名称

abnormal_login_alarm

快速查询名称

abnormal_login

数据查询时间(分钟)

5

检查间隔(分钟)

5

触发次数

1

字段名称

abnormal_login_percentage

比较符

大于

检查阈值

0.01

通知类型

通知中心

通知内容

user abnormal login percentage exceed limit.


数据备份


用户登录数据,一般建议在日志服务存储一段时间(30天、半年、1年等)用于实时的查询和分析,但对于历史数据还需要保存下来,便于后续的审计、大数据挖掘与分析等。这里我们使用日志服务的投递功能,将数据投递到OSS进行长期的归档存储。审计员来了想看多少年前的数据都有!

19dcf7d7-f7c6-47a3-b4fe-7666017ccefa.png

相关文章


使用EMR来进行mysqlbinlog日志准实时处理
如何基于MYSQL做实时计算


同志们有更多输入源的需求或其他问题请加钉钉群11775223联系:

59e270ab-cce1-4f3a-9fd5-1d0f860028f1 (1).png

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
3
79059
分享
相关文章
MySQL日志详解——日志分类、二进制日志bin log、回滚日志undo log、重做日志redo log
MySQL日志详解——日志分类、二进制日志bin log、回滚日志undo log、重做日志redo log、原理、写入过程;binlog与redolog区别、update语句的执行流程、两阶段提交、主从复制、三种日志的使用场景;查询日志、慢查询日志、错误日志等其他几类日志
MySQL日志详解——日志分类、二进制日志bin log、回滚日志undo log、重做日志redo log
Aurora MySQL负载突增应对策略与优化方案
通过以上策略,企业可以有效应对 Aurora MySQL 的负载突增,确保数据库在高负载情况下依然保持高性能和稳定性。这些优化方案涵盖了从架构设计到具体配置和监控的各个方面,能够全面提升数据库的响应速度和处理能力。在实际应用中,应根据具体的业务需求和负载特征,灵活调整和应用这些优化策略。
53 22
MySQL 分库分表方案
本文总结了数据库分库分表的相关概念和实践,针对单张表数据量过大及增长迅速的问题,介绍了垂直和水平切分的方式及其适用场景。文章分析了分库分表后可能面临的事务支持、多库结果集合并、跨库join等问题,并列举了几种常见的开源分库分表中间件。最后强调了不建议水平分库分表的原因,帮助读者在规划时规避潜在问题。
149 20
MySQL事务日志-Undo Log工作原理分析
事务的持久性是交由Redo Log来保证,原子性则是交由Undo Log来保证。如果事务中的SQL执行到一半出现错误,需要把前面已经执行过的SQL撤销以达到原子性的目的,这个过程也叫做"回滚",所以Undo Log也叫回滚日志。
MySQL事务日志-Undo Log工作原理分析
mysql慢查询每日汇报与分析
通过启用慢查询日志、提取和分析慢查询日志,可以有效识别和优化数据库中的性能瓶颈。结合适当的自动化工具和优化措施,可以显著提高MySQL数据库的性能和稳定性。希望本文的详解和示例能够为数据库管理人员提供有价值的参考,帮助实现高效的数据库管理。
43 11
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL原理简介—4.深入分析Buffer Pool
本文介绍了MySQL的Buffer Pool机制,包括其作用、配置方法及内部结构。Buffer Pool是MySQL用于缓存磁盘数据页的关键组件,能显著提升数据库读写性能。默认大小为128MB,可根据服务器配置调整(如32GB内存可设为2GB)。它通过free链表管理空闲缓存页,flush链表记录脏页,并用LRU链表区分冷热数据以优化淘汰策略。此外,还探讨了多Buffer Pool实例、chunk动态调整等优化并发性能的方法,以及如何通过`show engine innodb status`查看Buffer Pool状态。关键词:MySQL内存数据更新机制。
MySQL 窗口函数详解:分析性查询的强大工具
MySQL 窗口函数从 8.0 版本开始支持,提供了一种灵活的方式处理 SQL 查询中的数据。无需分组即可对行集进行分析,常用于计算排名、累计和、移动平均值等。基本语法包括 `function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])`,常见函数有 `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()` 等。窗口框架定义了计算聚合值时应包含的行。适用于复杂数据操作和分析报告。
150 11
数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog
《数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog》介绍了如何利用MySQL的二进制日志(Binlog)恢复误删除的数据。主要内容包括: 1. **启用二进制日志**:在`my.cnf`中配置`log-bin`并重启MySQL服务。 2. **查看二进制日志文件**:使用`SHOW VARIABLES LIKE 'log_%';`和`SHOW MASTER STATUS;`命令获取当前日志文件及位置。 3. **创建数据备份**:确保在恢复前已有备份,以防意外。 4. **导出二进制日志为SQL语句**:使用`mysqlbinlog`
121 2
数据库数据恢复—Mysql数据库表记录丢失的数据恢复方案
Mysql数据库故障: Mysql数据库表记录丢失。 Mysql数据库故障表现: 1、Mysql数据库表中无任何数据或只有部分数据。 2、客户端无法查询到完整的信息。

云存储

+关注