乐趣区

宜信开源数据库审核软件Themis部署攻略

jiezi

一、介绍

Themis 是宜信公司 DBA 团队开发的一款数据库审核产品，可帮助 DBA、开发人员快速发现数据库质量问题，提升工作效率。其名称源自希腊神话中的正义与法律女神。项目取此名称，寓意此平台对数据库质量公平判断，明察秋毫。

此平台可实现对 Oracle、MySQL 数据库进行多维度（对象结构、SQL 文本、执行计划及执行特征）的审核，用以评估对象结构设计质量及 SQL 运行效率。可帮助 DBA 及开发人员，快速发现定位问题；并提供部分辅助诊断能力，提升优化工作效率。全部操作均可通过 WEB 界面进行，简单便捷。此外，为了更好满足个性化需求，平台还提供了扩展能力，用户可根据需要自行扩展。

开源地址：https://github.com/CreditEaseDBA

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 配置文件的修改，我们建议使用统一的用户进行安装 

    adduser themis-test
    su - themis-test

后面的操作除了 virtualenv 安装需要切换到 root 用户，其他的都默认在 themis-test 用户下安装

安装 cx_Oracle 依赖

由于在审核过程中需要连接 oracle 数据库，因此需要先安装 cx_Oracle 的依赖，参考：http://www.jianshu.com/p/pKz5K7

安装 python 依赖

首先安装 virtualenv，参考链接：https://pypi.python.org/simpl…，建议安装 13.0.3 或更新版本

如果联网不方便，或者在公司内网，可以从 https://pan.baidu.com/s/1o7AI…，提取码：3sy3

压缩包里包括所有需要用到的依赖包

安装 virtualenv

tar -zxvf virtualenv-13.0.3.tar.gz
cd virtualenv-13.1.0
python setup.py install

关于 virtualenv 的使用请参考：https://virtualenv.pypa.io/en…

安装其他依赖

首先初始化虚拟环境 

virtualenv python-project --python=python2.7
source /home/themis-test/python-project/bin/activate

解释一下上面的命令：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 这里。

如果可以联网，进入源代码目录使用如下命令 

pip install -r requirement.txt

单独安装 Pyh, 下载地址：https://github.com/hanxiaomax…

unzip pyh-master.zip
cd pyh-master
python setup.py install

如果在局域网环境不方便联网请利用的上面网盘里提供的压缩包 

pip install --no-index -f file:///home/themis-test/software -r requirement.txt

file:///home/themis-test/software 是压缩包解压的位置

2.2 配置文件介绍

下面以配置文件 settings.py 为例子说明需要的一些依赖 

# # set oracle ipaddress, port, sid, account, password
# ipaddres : port -> key
ORACLE_ACCOUNT = {
    # oracle
    "127.0.0.1:1521": ["cedb", "system", "password"]
}

# set mysql ipaddress, port, account, password
MYSQL_ACCOUNT = {"127.0.0.1:3307": ["mysql", "user", "password"]
}

# pt-query save data for mysql account, password
PT_QUERY_USER = "user"
PT_QUERY_PORT = 3306
PT_QUERY_SERVER = "127.0.0.1"
PT_QUERY_PASSWD = "password"
PT_QUERY_DB = "slow_query_log"

# celery setting
REDIS_BROKER = 'redis://:password@127.0.0.1:6379/0'
# REDIS_BROKER = 'redis://:@127.0.0.1:6379/0'


REDIS_BACKEND = 'redis://:password@127.0.0.1:6379/0'
# REDIS_BACKEND = 'redis://:@127.0.0.1:6379/0'


CELERY_CONF = {"CELERYD_POOL_RESTARTS": True}

# mongo server settings
MONGO_SERVER = "127.0.0.1"
MONGO_PORT = 27017
# MONGO_USER = "sqlreview"
MONGO_USER = "sqlreview"
# MONGO_PASSWORD = ""MONGO_PASSWORD ="sqlreview"MONGO_DB ="sqlreview"

# server port setting
SERVER_PORT = 7000

# capture time setting
CAPTURE_OBJ_HOUR = "18"
CAPTURE_OBJ_MINUTE = 15
CAPTURE_OTHER_HOUR = "18"
CAPTURE_OTHER_MINUTE = 30

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 规则导入

进入源代码目录，使用如下命令进行规则初始化 

mongoimport -h 127.0.0.1 --port 27017 -u sqlreview -p password -d sqlreview -c rule --file script/rule.json

三、数据采集

数据采集分为 oracle 部分和 mysql 部分，oracle 部分使用的是自己开发的一些脚本，mysql 使用的是 pt-query-digest 工具。

数据采集的频率默认是一天一次，可以根据自己的需要进行修改。

oracle 部分依赖于 celery 的任务调度，会用 supervisor 托管，pt-query-digest 可以加到 crontab 里。

3.1 oracle 部分

手动数据采集

手动采集 oracle obj 信息

配置 data/capture_obj.json 文件 

{
    "module": "capture",   
    "type": "OBJ",    
    "db_type": "O",    
    "db_server": "127.0.0.1",    
    "db_port": 1521,   
    "capture_date": "2017-02-28"
}

只需要配置 db_server 和 dbport 选项，oracle 的端口要求是 1521，capture_date 指定采集数据的日期，现在只支持按天采集。

执行命令 

python command.py -m capture_obj -c data/capture_obj.json

手动采集 oracle other 信息，包括 plan、stat、text 信息。

配置 data/capture_other.json 文件。

{
    "module": "capture",   
    "type": "OTHER",    
    "db_type": "O",    
    "db_server": "127.0.0.1",    
    "db_port": 1521,    
    "capture_date": "2017-02-28"
}

配置方式同上面的 obj

执行命令 

python command.py -m capture_obj -c data/capture_obj.json

手动采集数据一般用于初次采集，后面一般都会通过自动采集完成。

自动数据采集

配置 settings.py 文件里的 ORACLE_ACCOUNT，该账号需要具有查询所有表的权限，即 select any table。

ORACLE_ACCOUNT = {    
    # oracle    
    "127.0.0.1:1521": ["cedb", "system", "password"]
}

配置调度时间 

# capture time setting
CAPTURE_OBJ_HOUR = "18"
CAPTURE_OBJ_MINUTE = 15
CAPTURE_OTHER_HOUR = "18"
CAPTURE_OTHER_MINUTE = 30

如果不对 oracle 数据库审核，可以不用配置。

3.2 mysql 部分

pt-query-digest 使用

  • 可以将慢日志集中到一个地方，再集中入库。
  • 也可以在每台 mysql 机器上安装 pt-query-digest, 再将解析结果推送到存储机器上。

本平台采用第二种方案

从 https://www.percona.com/get/p… 下载并安装 pt-query-digest，如果缺少依赖使用 yum 安装。

使用 scirpt/pt_query_digest.sql 初始化表结构, 不要使用默认的表结构。

在目标机器上配置好 script/pt-query-digest.sh 脚本:

pt-query-digest --user=root --password=password --review h=127.0.0.1,D=slow_query_log,t=global_query_review --history h=127.0.0.1,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter="\$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}='127.0.0.1:3306'and \$event->{client}=\$event->{ip}" slow.log

$event->{hostname}=’127.0.0.1:3306′ 为被搜集慢日志的机器的 ip 地址和端口号。

主要是配置存储解析结果的 mysql 机器的帐号，密码，机器 ip，端口号，以及慢日志的位置等。

运行 pt-query-digest.sh 脚本开始搜集 mysql 慢查询数据，后面可以将其加入定时任务，按固定时间段搜集。

四、任务导出

4.1 手动任务导出

配置 data/export.json 文件 

{
    "module": "export",
    "type": "export",
    "task_uuid": "08d03ec6-f80a-11e6-adbc-005056a30561",
    "file_id": "08d03ec6-f80a-11e6-adbc-005056a30561"
}

配置 task_uuid 和 file_id 选项，这是任务的唯一标志，可以通过从 mongo 中 sqlreview 库中 job 集合查看，然后运行：

python command.py -m export -c data/export.json

进行手动任务导出，会生成离线的 html 压缩包，保存在 task_export/downloads 下，可以直接解压，然后通过浏览器打开查看报告。

4.2 自动任务导出

通过在 celery 配合 supervisor 托管来实现，具体可参考 supervisor 的配置。

五、web 管理端

5.1 手动开启 web 管理端

执行如下命令 

python command.py -m web -c data/web.json

访问 http://127.0.0.1:7000 即可打开管理端

六、supervisor 配置

6.1 supervisor 配置 

;web 管理端开启
[program:themis-web]
command=/home/themis-test/python-project/bin/python command.py -m web -c data/web.json
autostart=true
redirect_stderr=true
stdout_logfile=tmp/themis_web.log
loglevel=info

; 开启文件下载服务器
[program:themis-download]
command=/home/themis-test/python-project/bin/python task_export/file_download.py
autostart=true
redirect_stderr=true
stdout_logfile=tmp/themis_download.log
loglevel=info

; 开启任务导出模块
[program:themis-export]
command=/home/themis-test/python-project/bin/celery -A task_exports worker -E -l info
autostart=true
redirect_stderr=true
stdout_logfile=tmp/themis_export.log
loglevel=info
; 开启规则解析模块
[program:themis-analysis]
command=/home/themis-test/python-project/bin/celery -A task_other worker -E -Q sqlreview_analysis -l info
autostart=true
redirect_stderr=true
stdout_logfile=tmp/themis_analysis.log
loglevel=info
; 开启 obj 信息抓取模块
[program:themis-capture-obj]
command=/home/themis-test/python-project/bin/celery -A task_capture worker -E -Q sqlreview_obj -l debug -B -n celery-capture-obj
autostart=true
redirect_stderr=true
stdout_logfile=tmp/themis_capture_obj.log
loglevel=info
; 开启 plan、stat、text 信息抓取模块
[program:themis-capture-other]
command=/home/themis-test/python-project/bin/celery -A task_capture worker -E -Q sqlreview_other -l info -B -n celery-capture-other
autostart=true
redirect_stderr=true
stdout_logfile=tmp/themis_capture_other.log
loglevel=info
;celery 的任务管理模块, 去掉前边的 ";" 即可开启，需要配置 redis 的连接方式
;[program:themis-flower]
;command=/home/themis-test/python-project/bin/celery flower --address=0.0.0.0 --broker=redis://:password@127.0.0.1:6379/0
;autostart=true
;redirect_stderr=true
;stdout_logfile=tmp/themis_flower.log
;loglevel=info

注意：如果前边建立的用户不同或者使用了不同的目录，需要将这个文件里的 /home/themis-test/python-project/ 替换为自己的路径。

supervisor 常用命令 

 开启 supervisor
supervisord -c script/supervisord.conf
重载 supervisor
supervisorctl -u sqlreview -p sqlreview.themis reload
进入 supervisor 管理控制台, 这里的 -u,- p 代表 supervisorctl 的用户名和密码，在 supervisord.conf 中配置
supervisorctl -u username -p password

参考：http://www.supervisord.org/

七、常见问题

  • 主机名称不一致，导致 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/a…

八、异常处理

程序中间出了差错可以通过开启 flower 来查看，或者通过手工执行的方式进行查看代码。

flower 的开启可以通过在 supervisor 中配置或者 

;celery 的任务管理模块, 去掉前边的 ";" 即可开启，需要配置 redis 的连接方式
;[program:themis-flower]
;command=/home/themis-test/python-project/bin/celery flower --address=0.0.0.0 --broker=redis://:password@127.0.0.1:6379/0
;autostart=true
;redirect_stderr=true
;stdout_logfile=tmp/themis_flower.log
;loglevel=info

也可以手工开启：

celery flower --address=0.0.0.0 --broker=redis://:password@127.0.0.1:6379/0

不过都需要配置 redis 认证选项。

九、加入开发

有问题可以直接在 https://github.com/CreditEase… 提出。

本文选自：wiki。由于篇幅关系内容有所调整，请点击链接查看原文。

来源：宜信技术学院

退出移动版