规则采集文章软件(宜信公司DBA团队开发的一款数据库审核产品，提升工作效率)

　　规则采集文章软件(宜信公司DBA团队开发的一款数据库审核产品，提升工作效率)

　　一、简介

　　Themis是宜信DBA团队开发的数据库审计产品，可以帮助DBA和开发人员快速发现数据库质量问题，提高工作效率。它的名字来源于希腊神话中的正义与法律女神。项目的名称意味着这个平台对数据库质量的判断是公正而清晰的。

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

　　开源地址：

　　1.1 功能概述1.2 支持的数据库1.3 审计维度1.4 实现原则

　　

　　整个平台的基本实现原理很简单，就是通过规则集来过滤我们的审计对象（目前支持四种）。符合规则的审计对象都怀疑有问题。该平台将提供这些问题和相关信息进行人工筛选。可见，平台的强大功能主要取决于规则集的丰富程度。平台还提供了一些扩展能力，方便规则集的扩展。

　　1.5 平台架构

　　

　　图中方框部分是平台的主要模块。不同背景颜色的模块表示不同的当前进度状态。虚线代表数据流，实线代表控制流。它的核心是这些模块：

　　1.6 操作流程

　　

　　二、环境建设

　　本项目将使用mysql、mongo和redis。Python 支持 2.6、2.7，但目前不支持 python3。

　　mysql用于存储pt-query-digest捕获的mysql的慢查询，mongo存储我们的规则、oracle的采集结果、执行作业、解析结果集等，redis作为task的队列调度芹菜。

　　在 mysql 的数据 采集 部分中，我们使用 pt-query-digest 工具。

　　2.1 依赖安装创建新用户

　　为了减少后期对supervisord.conf配置文件的修改，建议使用统一用户进行安装

　　 adduser themis-test

su - themis-test

　　以下操作默认安装在themis-test用户下，除了virtualenv安装需要切换到root用户。

　　安装 cx_Oracle 依赖项

　　由于审计过程中需要连接oracle数据库，所以需要先安装cx_Oracle的依赖，参考：

　　安装python依赖

　　先安装virtualenv，参考链接：建议安装13.0.3或更新版本

　　如果上网不方便，或者在公司内网，可以从：3sy3中提取代码

　　压缩包收录所有需要的依赖

　　安装虚拟环境

　　tar -zxvf virtualenv-13.0.3.tar.gz

cd virtualenv-13.1.0

python setup.py install

　　virtualenv的使用请参考：

　　安装其他依赖

　　首先初始化虚拟环境

　　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/python 2. 7/站点包在这里。

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

　　pip install -r requirement.txt

　　单独安装Pyh，下载地址：

　　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。这两个分配给文件下载服务器和花卉管理工具。

　　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管理，crontab可以添加pt-query-digest。

　　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其他信息，包括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。该账号需要有查询所有表的权限，即选择任意表。

　　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使用

　　本平台采用第二种方案

　　从 pt-query-digest 下载并安装，如果缺少依赖项使用 yum install。

　　使用 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的配置。

　　五、web管理终端5.1 手动打开web管理终端

　　执行以下命令

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

　　访问权限：7000开通管理终端

　　六、主管配置6.1个主管配置

　　;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

supervisord -c script/supervisord.conf

重载supervisor

supervisorctl -u sqlreview -p sqlreview.themis reload

进入supervisor管理控制台,这里的-u,-p代表supervisorctl的用户名和密码，在supervisord.conf中配置

supervisorctl -u username -p password

　　参考：

　　七、常见问题

　　文件位置：capture/sql.py webui/utils/f_priv_db_user_list.py

　　某些情况下需要安装python-devel，centos install yum install python-devel

　　mysqldb安装问题参考：

　　八、异常处理

　　程序中的错误可以通过打开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 身份验证选项。

　　九、加入开发

　　可以直接提出问题。

　　本文摘自：wiki:.