宜信开源|数据库审核软件Themis的规则解析与部署攻略
一、介绍
Themis是宜信公司DBA团队开发的一款数据库审核产品，可帮助DBA、开发人员快速发现数据库质量问题，提升工作效率。其名称源自希腊神话中的正义与法律女神。项目取此名称，寓意此平台对数据库质量公平判断，明察秋毫。
此平台可实现对Oracle、MySQL数据库进行多维度（对象结构、SQL文本、执行计划及执行特征）的审核，用以评估对象结构设计质量及SQL运行效率。可帮助DBA及开发人员，快速发现定位问题；并提供部分辅助诊断能力，提升优化工作效率。全部操作均可通过WEB界面进行，简单便捷。此外，为了更好满足个性化需求，平台还提供了扩展能力，用户可根据需要自行扩展。
Themis，是希腊正义与法律女神，以头脑清晰见称。项目采用此名称，寓意着平台可对数据库质量公平判断，明察秋毫之意。
1.1 功能概述
事后审核，自主优化部分放在二期实现。亦可在项目设计阶段引入，起到一部分事前审核的作用。
通过WEB界面完成全部工作，主要使用者是DBA和有一定数据库基础的研发人员。
可针对某个用户审核，可审核包括数据结构、SQL文本、SQL执行特征、SQL执行计划等多个维度。
审核结果通过WEB页面或导出文件的形式提供。
平台支持主流的Oracle、MySQL数据库，其他数据库放在二期实现。
尽量提供灵活定制的能力，便于日后扩展功能。
1.2 支持的数据库
MySQL（5.6及以上）
Oracle（10g及以上)
1.3 审核维度
数据库结果（对象）=》指数据库对象，常见的表、分区、索引、视图、触发器等。
SQL文本（语句）=》指SQL语句文本本身。
SQL执行计划=》指数据库中SQL的执行计划。
SQL执行特征=》指语句在数据库上的真实执行情况。
1.4 实现原理
整个平台的基本实现原理很简单，就是将我们的审核对象（目前支持四种），通过规则集进行筛选。符合规则的审核对象，都是疑似有问题的。平台会将这些问题及关联信息提供出来，供人工甄别使用。由此可见，平台的功能强大与否，主要取决于规则集的丰富程度。平台也提供了部分扩展能力，方便扩展规则集。
1.5 平台架构
图中的方框部分，为平台的主要模块。底色不同的模块，表示当前的进度状态不同。虚线代表数据流，实线代表控制流。其核心为这几个模块：
数据采集模块。它是负责从数据源抓取审核需要的基础数据。目前支持从Oracle、MySQL抓取。
OBJ/SQL存储库。这是系统的共同存储部分，采集的数据和处理过程中的中间数据、结果数据都保存在这里。其核心数据分为对象类和SQL类。物理是采用的MongoDB。
核心管理模块。图中右侧虚线部分包含的两个模块：SQL管理和OBJ管理就是这部分。它主要是完成对象的全生命周期管理。目前只做了简单的对象过滤功能，因此还是白色底色，核心的功能尚未完成。
审核规则和审核引擎模块。这部分是平台一期的核心组件。审核规则模块是完成规则的定义、配置工作。审核引擎模块是完成具体规则的审核执行部分。
优化规则和优化引擎模块。这部分是平台二期的核心组件。目前尚未开发，因此为白色底色。
系统管理模块。这部分是完成平台基础功能，例如任务调度、空间管理、审核报告生成、导出等功能。
1.6 操作流程
二、环境搭建
本项目中会使用到mysql，mongo和redis，python支持2.6、2.7，暂不支持python3。
mysql用来存储pt-query-digest抓取的mysql的慢查询，mongo存储我们的规则、oracle的采集结果、执行job，解析结果集等，redis作为任务调度celery的队列。
在mysql的数据采集部分我们使用的是pt-query-digest工具。
2.1 依赖安装
新建用户
为了减少后面对supervisord.conf配置文件的修改，我们建议使用统一的用户进行安装
后面的操作除了virtualenv安装需要切换到root用户，其他的都默认在themis-test用户下安装
安装cx_Oracle依赖
由于在审核过程中需要连接oracle数据库，因此需要先安装cx_Oracle的依赖，参考：http://www.jianshu.com/p/pKz5K7
安装python依赖
首先安装virtualenv，参考链接：https://pypi.python.org/simple/virtualenv/，建议安装13.0.3或更新版本
如果联网不方便，或者在公司内网，可以从https://pan.baidu.com/s/1o7AIWlG下载压缩包，提取码：3sy3
压缩包里包括所有需要用到的依赖包
安装virtualenv
关于virtualenv的使用请参考：https://virtualenv.pypa.io/en/stable/
安装其他依赖
首先初始化虚拟环境
解释一下上面的命令：virtualenv的第二个参数python-project是我们建立的虚拟环境的名称，这个名称我们虽然可以随便定义，但是后面supervisor的配置中使用了此名称，建议使用默认的，大家如果对python比较熟悉，可以随意定义。后面我们指定了python的版本,--python可以不加，默认会使用系统自带的python版本构建虚拟环境，当有多个版本的python时，可以使用此命令指定版本。
下面使用source初始化虚拟环境，以后安装的包依赖等都会被装到/home/themis-test/python-project/home/themis-test/python2.7/lib/python2.7/site-packages这里。
file:///home/themis-test/software是压缩包解压的位置
2.2 配置文件介绍
下面以配置文件settings.py为例子说明需要的一些依赖
ORACLE_ACCOUNT和MYSQL_ACCOUNT是我们需要审核的目标机器的帐号和密码，主要是在数据采集部分和对象类审核以及mysql的执行计划类审核部分会用到，因此该帐号因该具有较高的权限，为了安全在生产环境应该设置专有的帐号并设置专有的权限，或者加上一些ip的限制等。
PT_QUERY_USER、PT_QUERY_PORT、PT_QUERY_SERVER、PT_QUERY_PASSWD、PT_QUERY_DB是我们pt-query-digest工具解析目标机器的慢sql后需要存储到的mysql数据库的一些配置。
REDIS_BROKER、REDIS_BACKEND、CELERY_CONF是任务调度工具celery的配置选项。
MONGO_SERVER、MONGO_PORT、MONGO_USER、MONGO_PASSWORD、MONGO_DB是需要存储结果集的mongo的配置选项。
SERVER_PORT是web管理端监听的端口，不要使用9000和5555端口，这两个被分配给了文件下载服务器和flower管理工具。
CAPTURE_OBJ_HOUR、CAPTURE_OBJ_MINUTE、CAPTURE_OTHER_HOUR、CAPTURE_OTHER_MINUTE是针对oracle的数据采集模块需要设置的采集时间，根据自己的实际情况设置不同的时间即可，避开业务高峰期。
请按照相关说明配置该文件
2.3 规则导入
进入源代码目录，使用如下命令进行规则初始化
三、数据采集
数据采集分为oracle部分和mysql部分，oracle部分使用的是自己开发的一些脚本，mysql使用的是pt-query-digest工具。
数据采集的频率默认是一天一次，可以根据自己的需要进行修改。
oracle部分依赖于celery的任务调度，会用supervisor托管，pt-query-digest可以加到crontab里。
3.1 oracle部分
手动数据采集
手动采集oracle obj信息
配置data/capture_obj.json文件
只需要配置db_server和dbport选项，oracle的端口要求是1521，capture_date指定采集数据的日期，现在只支持按天采集
执行命令
配置方式同上面的obj
执行命令
如果不对oracle数据库审核，可以不用配置
3.2 mysql部分
pt-query-digest使用
可以将慢日志集中到一个地方，再集中入库
也可以在每台mysql机器上安装pt-query-digest,再将解析结果推送到存储机器上。
本平台采用第二种方案
从 https://www.percona.com/get/pt-query-digest 下载并安装pt-query-digest，如果缺少依赖使用yum安装。
使用scirpt/pt_query_digest.sql初始化表结构,不要使用默认的表结构。
在目标机器上配置好script/pt-query-digest.sh脚本:
$event->{hostname}='127.0.0.1:3306' 为被搜集慢日志的机器的ip地址和端口号。
主要是配置存储解析结果的mysql机器的帐号，密码，机器ip，端口号，以及慢日志的位置等。
运行pt-query-digest.sh脚本开始搜集mysql慢查询数据，后面可以将其加入定时任务，按固定时间段搜集。
四、规则解析
规则解析分为四块：对象类规则解析、文本类规则解析、执行计划类规则解析、统计信息类规则解析。每个模块都可以使用手动或自动的方式进行。
4.1 对象类规则解析
手动解析oracle对象类信息
配置data/analysis_o_obj.json文件
使用上面的命令开始采集obj数据
手动解析mysql对象类数据
配置data/analysis_m_obj.json文件
oracle和mysql对象类规则是不需要依赖于采集的数据的，它是直接连接到数据库里进行查询的，由于有的库较大可能时间会比较久，建议在业务低峰期进行。
4.2 文本类规则解析
手动解析oracle文本类规则
配置data/analysis_o_text.json文件
手动解析mysql文本类规则
配置data/oracle_m_text.json文件
上面两步中的username为需要审核的对象。
4.3 执行计划类规则解析
oracle plan类型规则解析
配置data/analysis_o_plan.json文件
运行上面的命令即可生成解析结果。
mysql plan规则解析
配置data/analysis_m_plan.json文件
然后运行上面的命令进行mysql的plan的规则解析。
4.4 执行特征类规则解析
oracle stat类型规则解析
配置data/analysis_o_stat.json文件
进行数据采集。
mysql stat类型规则解析
配置文件data/analysis_m_text.json
进行数据采集。
4.5 自动规则解析
上面介绍的手动规则解析都是可以进行测试，或者在一些特殊情况下使用，大部分情况我们会使用自动规则解析。
自动规则解析我们使用celery来完成，关于celery 的使用，请参考http://docs.celeryproject.org/en/master/getting-started/first-steps-with-celery.html。
下面是常用的一些关于celery的命令:
最后我们会将规则解析都加入到supervisor托管，然后通过web界面生成任务，然后用celery进行调度，通过flower查看任务执行状态。
关于具体使用请参考supervisor的配置。
五、任务导出
5.1 手动任务导出
配置data/export.json文件
进行手动任务导出，会生成离线的html压缩包，保存在task_export/downloads下，可以直接解压，然后通过浏览器打开查看报告。
5.2 自动任务导出
通过在celery配合supervisor托管来实现，具体可参考supervisor的配置。
六、web管理端
6.1 手动开启web管理端
执行如下命令
访问 http://127.0.0.1:7000 即可打开管理端
七、supervisor配置
7.1 supervisor配置
注意：如果前边建立的用户不同或者使用了不同的目录，需要将这个文件里的/home/themis-test/python-project/替换为自己的路径。
supervisor常用命令
参考：http://www.supervisord.org/
八、内置规则说明
平台的核心就是规则。规则是一组过滤条件的定义及实现。规则集的丰富程度，代表了平台的能力。平台也提供了扩展能力，用户可自行定义规则。 从分类来看，规则可大致分为几类。
8.1 规则分类
从数据库类型来区分，规则可分为Oracle、MySQL。不是所有规则都区分数据库，文本类的规则就不区分。
从复杂程度来区分，规则可分为简单规则和复杂规则。这里的简单和复杂，实际是指规则审核的实现部分。简单规则是可以描述为mongodb或关系数据库的一组查询语句；而复杂规则是需要在外部通过程序体实现的。
从审核对象角度来区分，规则可分为对象类、文本类、执行计划类和执行特征类。
8.2 规则参数
规则可以包含参数。例如:执行计划规则中，有个是大表扫描。这里就需要通过参数来限定大表的定义，可通过物理大小来指定。
8.3 规则权重及阀值
权重 权重，代表违反规则，一次扣几分。可根据自身情况进行调节。
阀值 阀值，代表违反规则的扣分上限。这里主要是为了避免违反单一规则过多，导致忽略了其他规则。
规则权重及扣分，最终会累积为一个总的扣分，平台会按百分制进行折算。通过这种方式，可起到一定的量化作用。
8.4 规则_对象类（Oracle部分）
8.5 规则_对象类（MySQL部分）
8.6 规则_执行计划类（Oracle部分）
8.7 规则_执行计划类（MySQL部分）
8.8 规则_执行特征类（Oracle部分）
8.9 规则_执行特征类（MySQL部分）
8.10 规则_文本类
九、常见问题
主机名称不一致，导致cx_Oracle出错。
celery与flower版本不一致，导致flower不能启动，升级flower到0.8.1以上。
mysql5.7不能初始化datetime默认类型为(DEFAULT '0000-00-00 00:00:00)。
mongodb文档最大插入数据有限制，导致生成结果时插入文档失败。
在oracle获取用户的时候，有的系统可能会将用户建到users下，因此需要将 NOT IN ('USERS', 'SYSAUX'))改成 NOT IN ('SYSAUX'))
文件位置：capture/sql.py webui/utils/f_priv_db_user_list.py
有些情况下需要安装python-devel，centos安装 yum install python-devel
mysqldb安装问题参考：http://blog.csdn.net/wklken/article/details/7271019
十、异常处理
程序中间出了差错可以通过开启flower来查看，或者通过手工执行的方式进行查看代码。
flower的开启可以通过在supervisor中配置或者
不过都需要配置redis认证选项。
十一、加入开发
有问题可以直接在 https://github.com/CreditEaseDBA/Themis/issues 提出。
来源：宜信技术学院
本文转载自异步社区。
原文链接：https://www.epubit.com/articleDetails?id=Nebc0f0c4-4391-4dcd-adea-4dc48cfb9ba6
- 点赞
- 收藏
- 关注作者
评论（0）