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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

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

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

d47e62d2b349aca45e42305ed6714efbe5ed61d9老板要看大屏,每天 UV、PV 增长在哪里?
d47e62d2b349aca45e42305ed6714efbe5ed61d9安全要监控登录是否异常,现在用户账户是否遭到集体攻击?
d47e62d2b349aca45e42305ed6714efbe5ed61d9客户小二接到用户反馈,如何实时查询用户登录信息?
d47e62d2b349aca45e42305ed6714efbe5ed61d9BI需要分析用户行为,数据分析如何关联用户登录数据?

d47e62d2b349aca45e42305ed6714efbe5ed61d9审计上门了,请把您3年前用户的登录数据拿出来吧?

afc48c13259810d00df6b374321a3fde4ae99216

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

d47e62d2b349aca45e42305ed6714efbe5ed61d9MySQL Binlog 采集

d47e62d2b349aca45e42305ed6714efbe5ed61d9关键字段索引+统计设置

d47e62d2b349aca45e42305ed6714efbe5ed61d9对异常账号进行查询分析

d47e62d2b349aca45e42305ed6714efbe5ed61d9对异常登录进行告警

d47e62d2b349aca45e42305ed6714efbe5ed61d9配置可视化仪表盘

d47e62d2b349aca45e42305ed6714efbe5ed61d9对历史登录信息备份以备数据审计

环境准备

数据库

MySQL 类型数据库(使用 MySQL 协议,例如 RDS、DRDS 等),数据库开启 binlog,且配置 binlog 类型为 ROW 模式(RDS 默认开启)

用户登录表结构

4859cd5216915c6ea5256f5e1b7d6bcbf6aa407f

用户登录表中记录了登录 id、登录时间、登录 ip、登录设备、用户 id、登录结果、连续登录失败次数、下一次校验类型等信息。其中登录验证规则如下:

d47e62d2b349aca45e42305ed6714efbe5ed61d9正常情况只验证账号密码匹配

d47e62d2b349aca45e42305ed6714efbe5ed61d9若用户连续登录失败超过3次或者当前ip和上次登录ip不在同一省,下次登录将弹出验证码

d47e62d2b349aca45e42305ed6714efbe5ed61d9若用户连续登录失败超过5次,则下次登录将使用手机验证码

用户登录时表的更新方案

d47e62d2b349aca45e42305ed6714efbe5ed61d9方案1:

每次用户登录,在 user_login 中新增一条记录,记录登录的ip、设备类型、时间信息

d47e62d2b349aca45e42305ed6714efbe5ed61d9方案2:

考虑到用户数量非常多,如果每次用户登录都在 user_login 中新增一条记录,数据量会非常大,所以每次用户登录时,只会根据 user_id 更新 update 表中的数据

对于方案1,优点是数据库中保存了所有用户的登录信息,缺点是 user_login 表会存在爆掉的问题,需要定期删除历史的数据;对于方案2,优点是 user_login 表的大小可控,缺点是会丢失历史用户的登录信息。

这里我们推荐使用方案 2+logtail binlog 采集组成最优的方案3:用户最近一次登录信息依然保存在数据库中,通过 logtail 的 binlog 功能采集 user_login 表,logtail 会将表中的每次修改事件上传到日志服务,日志服务中的数据可设置保存时间,超时自动删除。同时在日志服务中,可以对实时采集上来的数据进行查询、统计、查看报表、监控报警,也支持将数据对接下游流计算、导入 Max Compute/OSS 等。

66e907f939047ba79877ddf4afa15bd802acbec1

数据采集

安装 logtail

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

采集配置

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

12fef8b8b8577c8f30876a0424ca1d09978ed9c3

d47e62d2b349aca45e42305ed6714efbe5ed61d9在配置页面中输入 binlog 采集配置,如下:

0d1c7c08960dbc697c054097528ae39ec6066dfe

d47e62d2b349aca45e42305ed6714efbe5ed61d9在配置页面中输入 binlog 采集配置,如下:注意:

d47e62d2b349aca45e42305ed6714efbe5ed61d9在配置页面中输入 binlog 采集配置,如下:数据库开启 binlog 且为 ROW 模式(RDS 默认支持),使用的账户具有 mysql slave 权限以及需要采集的数据表的 select 权限。

d47e62d2b349aca45e42305ed6714efbe5ed61d9在配置页面中输入 binlog 采集配置,如下:binlog 支持 IncludeTables 和 ExcludeTables 过滤,格式均为正则表达式

d47e62d2b349aca45e42305ed6714efbe5ed61d9在配置页面中输入 binlog 采集配置,如下:其他请参考 binlog 采集中使用限制

建立索引

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

53aef202afed4a98c3009c370f77bc293b39c411

数据预览

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

d47e62d2b349aca45e42305ed6714efbe5ed61d9在配置页面中输入 binlog 采集配置,如下:对于修改的事件,Logtail 会同时采集修改前和修改后的数据,修改前的数据以 old_开头。因此我们可以基于修改前后的数据对比查找登录ip变化的相关记录。

b8e48c7b00dcd755e38ba54f6d09410b0b54649e

d47e62d2b349aca45e42305ed6714efbe5ed61d9在配置页面中输入 binlog 采集配置,如下:注意: 若无数据,请检查配置是否为合法 json;若配置正常,请参考数据采集异常排查文档自助排查

自定义查询与分析

到这一步我们就可以满足客服和 BI 的需求了:查询/关联查询。例如:

d47e62d2b349aca45e42305ed6714efbe5ed61d9在配置页面中输入 binlog 采集配置,如下:用户反馈账号信息被篡改了,客服通过日志服务,查询该用户从上次登录到现在的登录信息:login_id : 256525,发现其中有一条登录日志;继续查询登录地址login_id : 256525 | select ip_tp_province(login_ip) as login_province, ip_tp_country(login_ip) as login_country,发现是在国外登录的,因此很有可能该用户账号泄漏或被攻破了。

d47e62d2b349aca45e42305ed6714efbe5ed61d9在配置页面中输入 binlog 采集配置,如下:用户反馈自己的账号被限制登录了,客服通过日志服务,查询该用户限制登录前的相关登录信息: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,发现该用户在多个省异常登录失败了很多次。

d47e62d2b349aca45e42305ed6714efbe5ed61d9在配置页面中输入 binlog 采集配置,如下:查询相关使用帮助参见日志服务查询

用户登录大盘

现在我们来搭建 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 汇报了。

d47e62d2b349aca45e42305ed6714efbe5ed61d9仪表盘搭建参见日志服务仪表盘设置

cdd3a771e7c1b4ce05971544d0a103bd29b121c6

异常登录告警

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

d47e62d2b349aca45e42305ed6714efbe5ed61d9告警设置参见日志服务告警设置

2d03f38728c5c9eca6df4d88056a723a03988836

数据备份

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

d4c6beb50d13a5cbb91c8e16e73af78c761c823f


原文发布时间为:2018-05-2

本文作者:元乙

本文来自云栖社区合作伙伴“数据和云”,了解相关信息可以关注“数据和云”。



相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
28天前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
223 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
28天前
|
存储 SQL 关系型数据库
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移?从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
140 14
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
|
2天前
|
SQL 关系型数据库 MySQL
MySQL 更新1000万条数据和DDL执行时间分析
MySQL 更新1000万条数据和DDL执行时间分析
14 4
|
12天前
|
消息中间件 canal 关系型数据库
Maxwell:binlog 解析器,轻松同步 MySQL 数据
Maxwell:binlog 解析器,轻松同步 MySQL 数据
89 11
|
12天前
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
40 2
|
28天前
|
存储 关系型数据库 MySQL
分析MySQL主从复制中AUTO_INCREMENT值不一致的问题
通过对 `AUTO_INCREMENT`不一致问题的深入分析和合理应对措施的实施,可以有效地维护MySQL主从复制环境中数据的一致性和完整性,确保数据库系统的稳定性和可靠性。
61 6
|
2天前
|
SQL 关系型数据库 MySQL
MySQL EXPLAIN该如何分析?
本文将详细介绍MySQL中`EXPLAIN`关键字的工作原理及结果字段解析,帮助优化查询性能。`EXPLAIN`可显示查询SQL的执行计划,其结果包括`id`、`select_type`、`table`等字段。通过具体示例和优化建议,帮助你理解和应用`EXPLAIN`,提升数据库查询效率。
13 0
|
2天前
|
SQL 关系型数据库 MySQL
mysql集群方案
mysql集群方案
11 0
|
4天前
|
NoSQL 关系型数据库 MySQL
MySQL替代方案
随着 MySQL 被 Oracle 收购,MySQL 的用户和开发者开始质疑开源数据库的命运,与此同时他们开始寻找替代品。
14 0
|
29天前
|
存储 关系型数据库 MySQL
分析MySQL主从复制中AUTO_INCREMENT值不一致的问题
通过对 `AUTO_INCREMENT`不一致问题的深入分析和合理应对措施的实施,可以有效地维护MySQL主从复制环境中数据的一致性和完整性,确保数据库系统的稳定性和可靠性。
47 1

热门文章

最新文章