See-SQL审计平台介绍及部署

最新推荐文章于 2023-07-23 17:16:14 发布

Rugod

最新推荐文章于 2023-07-23 17:16:14 发布

阅读量2.4k

点赞数 1

分类专栏： Mysql 文章标签： mysql

本文链接：https://blog.csdn.net/ITRugod/article/details/110293541

版权

Mysql 专栏收录该内容

7 篇文章 1 订阅

订阅专栏

声明:这是我在大学毕业后进入第一家互联网公司学习的内容

背景

线下数据库，成天有人要求运维执行这sql那sql的，又苦逼又容易背锅，问了下公司的DBA大神，推荐了see审计平台，执行sql有审计记录，留痕留痕留痕，重要的事情说三遍，即使是线下环境，谨防有人删库跑路找不到人，可以解决运维一大痛点。

审计平台介绍

数据库审计平台作用

《对于开发人员》提交代码通过Inception审核，不符合规范代码会有提示，通过
Inception 审核后，开发人员可自行发起执行或定时执行，回滚等操作
《对于测试人员》提交代码需走工单流，通过Inception审核，流程自动到达部门经理，
部门经理审核，所有执行、回滚等操作由管理员操作
支持DDL，DML语句上线，回滚操作，方便统计已上线SQL，便于问题回溯
SQL优化，可提供SQL优化建议，打分、SQL改写建议，增加索引建议等

API集成

Inception: 去哪儿网开源，提供SQL语句审核、执行、回滚功能
SQLAdvisor: 美团开源，提供分析SQL中的where条件、聚合条件、多表Join关系，输出索引优化建议
SOAR: 小米开源，提供SQL启发式算法的语句优化、多列索引优化等功能

功能简介

目标库管理

支持多场地/数据中心的数据库管理，集群方式归纳目标数据库
支持目标数据库配置，库/表结构查询

SQL操作

基于Inception
SQL语法检测
SQL语句执行
SQL回滚
定时工单
历史记录

SQL查询

查询目标数据库的详细表结构
查询表数据，对结果可导出文件
SQL语句优化（基于美团SQLAdvisor）
多层次优化建议（基于小米SOAR）

用户管理

对用户/组的注册/注销/加组/授权等管理

个性化设置

管理员可以做SQL关键字拦截，平台的审批功能开关等设置
用户可以订阅其常用的数据库，指定审批工单的经理，以简化审核时所需的操作

inception设置

inception服务连接信息
inception备份库连接信息
inception支持的参数释义及值

人工审批功能

流程开关
开启流程，工单至少需双人确认（流程：提交人 – inception自动审核 – 经理审批 – DBA上线）
关闭流程，工单可由经理上线（流程：提交人 – inception自动审核 – 经理上线）

用户权限

基于RBAC的表级，对象级权限控制体系
通过用户管理设置用户权限
根据用户身份（组员/经理/总监）鉴权用户对SQL的审核/取消/执行/回滚等操作

操作流程

用户需要输入SQL，指定环境，执行人，数据库
inception自动审核SQL语法
审批人做审批通过或驳回操作
执行人做执行/撤销/回滚等操作
SQL列表界面提供SQL查询，操作等相关功能

API文档

各接口生成自动化的API文档，对接外部需求

通知

E-mail邮件推送

其他

DashBoard数据报表展示

核心组件

Inception

SQL操作基于Inception

SQL语法检测
SQL语句执行
SQL回滚
定时工单
历史记录

Inception

Inception提供的功能很丰富，首先，它可以对提交的所有语句的语法分析，如果语法有问题，都会将相应的错误信息返回给审核者。
还提供语义分析，当一个表，库，列等信息不正确或者不符合规范的时候报错，或者使用了一个不存在的对象时报错等等。还提供
了很多针对SQL规范性约束的功能，这些DBA都是可以通过系统参数来配置的。更高级的功能是，可以辅助DBA分析一条查询语句的
性能，如果没有使用索引或者某些原因导致查询很慢，都可以检查。
还提供SQL语句的执行功能，可执行的语句类型包括常用的DML及DDL语句及truncate table等操作。
Inception 在执行 DML 时还提供生成回滚语句的功能，对应的操作记录及回滚语句会被存储在备份机器上面，备份机器通过配置Inception参数来指定。

SOAR

SOAR(SQL Optimizer And Rewriter)是一个对SQL进行优化和改写的自动化工具。由小米人工智能与云平台的数据库团队开发与维护。

功能特点

跨平台支持（支持Linux, Mac环境，Windows环境理论上也支持，不过未全面测试）
目前只支持 MySQL 语法族协议的SQL优化
支持基于启发式算法的语句优化
支持复杂查询的多列索引优化（UPDATE, INSERT, DELETE, SELECT）
支持EXPLAIN信息丰富解读
支持SQL指纹、压缩和美化
支持同一张表多条ALTER请求合并
支持自定义规则的SQL改写

SQLAdvisor

SQLAdvisor是由美团点评公司技术工程部DBA团队（北京）开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析，结合分析SQL中的where条件、聚合条件、多表Join关系给出索引优化建议。

目前SQLAdvisor在美团点评内部广泛应用，公司内部对SQLAdvisor的开发全面转到github上，开源和内部使用保持一致。

主要功能：输出SQL索引优化建议

离线部署

部署过于复杂，而且部分组件网上很难下载(要么找不到官方安装包，要么在国外下载极慢)

所以本次部署采用离线方式，首先下载百度云上所有安装包

链接：https://pan.baidu.com/s/1126UeNZOVjrdVvOnrWO-uQ

提取码：1997

复制这段内容后打开百度网盘手机App，操作更方便哦

准备

操作系统支持(没列举并不代表不能安装,可以自行尝试)

CentOS 6+
CentOS 7+

安装包

bison-2.5.1.tar.gz
inception-master.zip
percona-toolkit-3.1.0_i386.tar.gz
percona-release-0.1-3.noarch.rpm
Python-3.6.6.tgz
redis-4.0.6.tar.gz
see-master.zip
SQLAdvisor-master.zip
mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

安装路径

/opt/see/

安装Mysql

安装包：

mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

安装过程参考
离线安装Mysql5.7.28及调优

Mysql配置文件内容需包含以下配置

[mysqld]
server-id = 100  # 不限制具体数值
log_bin = mysql-bin
binlog_format = row  # 或 MIXED

安装pt-online-schema-change

安装包：

percona-toolkit-3.1.0_i386.tar.gz

bison-2.5.1.tar.gz

yum install -y perl-DBI perl-DBD-mysql perl-Time-HiRes perl-ExtUtils-MakeMaker

cd /opt/see/
tar -zxvf percona-toolkit-3.1.0_i386.tar.gz
cd percona-toolkit-3.0.13
perl Makefile.PL
make
make install
ln -s /usr/local/bin/pt-online-schema-change /usr/bin/

Inception

安装包：

inception-master.zip

bison-2.5.1.tar.gz

安装

yum -y install cmake libncurses5-dev libssl-dev g++ bison gcc gcc-c++ openssl-devel ncurses-devel mysql MySQL-python

cd /opt/see/
tar -zxvf bison-2.5.1.tar.gz
cd bison-2.5.1
./configure
make
make install

cd /usr/local/
unzip inception-master.zip
cd inception-master/
sh inception_build.sh builddir linux

配置

创建文件 /etc/inc.cnf ,内容如下

[inception]
general_log=1
general_log_file=inc.log
port=6669
socket=/tmp/inc.socket 
character-set-client-handshake=0 
character-set-server=utf8 
inception_remote_system_password=123456 
inception_remote_system_user=root 
inception_remote_backup_port=3306 
inception_remote_backup_host=127.0.0.1 
inception_support_charset=utf8 
inception_enable_nullable=0 
inception_check_primary_key=1 
inception_check_column_comment=1 
inception_check_table_comment=1 
inception_osc_min_table_size=1 
inception_osc_bin_dir=/usr/bin 
inception_osc_chunk_time=0.1 
inception_ddl_support=1
inception_enable_blob_type=1 
inception_check_column_default_value=1

注意！如果你需要对其他数据库创建utf8mb4或其他字符集的库或表，需要在inception_support_charset 增加响应字符集，以,隔开，例如：

inception_support_charset=utf8,utf8mb4

启动

nohup /usr/local/inception-master/builddir/mysql/bin/Inception --defaults-file=/etc/inc.cnf &

Sqladvisor

安装包

SQLAdvisor-master.zip

percona-release-0.1-3.noarch.rpm

安装

cd /usr/local/src/
unzip inception-master.zip
yum install -y cmake libaio-devel libffi-devel glib2 glib2-devel bison
# 移除mysql-community库(无用途且和Percona-Server有冲突)
yum remove -y mysql-community-client mysql-community-server mysql-community-common mysql-community-libs
cd /usr/lib64/ 
ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so 
rpm -ivh /opt/see/percona-release-0.1-3.noarch.rpm
yum install -y Percona-Server-shared-56
cd /usr/local/src/SQLAdvisor/
cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./
make && make install

编译sqladvisor(源码目录)

cd ./sqladvisor/
cmake -DCMAKE_BUILD_TYPE=debug ./
make

测试

cp /usr/local/src/SQLAdvisor/sqladvisor/sqladvisor /usr/bin/sqladvisor
sqladvisor -h 127.0.0.1  -P 3306  -u root -p '123456' -d test -q "sql语句" -v 1

Redis

安装包

redis-4.0.6.tar.gz

安装

yum install -y gcc
cd /opt/see/
tar -zxvf redis-4.0.6.tar.gz
cd redis-4.0.6
make MALLOC=libc　　
cd src && make install
./redis-server /opt/see/redis-4.0.6/redis.conf

配置 /opt/see/redis-4.0.6/redis.conf

daemonize yes
bind 0.0.0.0

Nginx

yum install -y epel-release nginx

修改Nginx配置文件 nginx.conf, 使server部分的内容如下

server
  {
    listen 81;  # 用户访问端口
    access_log    /var/log/access.log;
    error_log    /var/log/error.log;

    location / { 
        root /usr/local/seevenv/see-master/frontend/dist/;  # 前端项目文件
        try_files $uri $uri/ /index.html =404; 
        index  index.html; 
    } 

    location /static/rest_framework_swagger {  #  前端API静态文件
        root /usr/local/seevenv/lib/python3.6/site-packages/rest_framework_swagger/; 
    } 

    location /static/rest_framework {  #  前端rest_framework静态文件
        root /usr/local/seevenv/lib/python3.6/site-packages/rest_framework/;
    } 

    location /api {
        proxy_pass http://127.0.0.1:8090;  # 后端端口此处一定得是127.0.0.1
        add_header Access-Control-Allow-Origin *; 
        add_header Access-Control-Allow-Headers Content-Type;
        add_header Access-Control-Allow-Headers "Origin, X-Requested-With, Content-Type, Accept";
        add_header Access-Control-Allow-Methods "GET, POST, OPTIONS, PUT, DELETE, PATCH";
    }

  }

See

终于到了see平台本身了

安装依赖

yum install -y readline readline-devel gcc gcc-c++ zlib zlib-devel openssl openssl-devel sqlite-devel python-devel openldap-clients openldap-devel openssl-devel

安装python3.6.6

/opt/see/
tar -xzf Python-3.6.6.tgz 
cd Python-3.6.6
./configure --prefix=/usr/local/python3.6 --enable-shared
make && make install
ln -s /usr/local/python3.6/bin/python3.6 /usr/bin/python3
ln -s /usr/local/python3.6/bin/pip3 /usr/bin/pip3
ln -s /usr/local/python3.6/bin/pyvenv /usr/bin/pyvenv

# 链接库文件
cp /usr/local/python3.6/lib/libpython3.6m.so.1.0 /usr/local/lib
cd /usr/local/lib
ln -s libpython3.6m.so.1.0 libpython3.6m.so
echo '/usr/local/lib' >> /etc/ld.so.conf
/sbin/ldconfig

安装Django及See后端

安装包

see-master.zip

cd /usr/local/
/usr/local/python3.6/bin/pyvenv seevenv
cd seevenv
source bin/activate


unzip master.zip
cd see-master/backend/
pip install -r requirements.txt --trusted-host mirrors.aliyun.com -i https://mirrors.aliyun.com/pypi/simple/

创建数据库

确保mysql的root密码为 123456
mysql -uroot -p123456 -e "create database sqlweb CHARACTER SET utf8;"
python manage.py makemigrations
python manage.py migrate
# 再执行一次migrate
python manage.py migrate

创建管理员用户 (可用于页面的用户登录)

python manage.py createsuperuser --username admin --email admin@domain.com

安装SOAR

mkdir -p /usr/local/SOAR/bin/
cp /usr/local/seevenv/see-master/frontend/src/files/soar /usr/local/SOAR/bin
chmod +x /usr/local/SOAR/bin/soar

设置（非必需操作）

打开文件 /usr/local/seevenv/see-master/backend/sqlweb/settings.py,找到以下设置并修改

MySQL

DATABASES = {
	'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'sqlweb',
        'USER': 'root',
        'PASSWORD': '123456',
        'HOST':'127.0.0.1',
        'PORT':'3306',
        'OPTIONS': {'charset':'utf8mb4'},
	},
}

Redis

REDIS_HOST = '127.0.0.1'  # redis地址
REDIS_PORT = 6379  # redis端口
REDIS_PASSWORD = ''  # redis密码

Inception配置文件

INCEPTION_SETTINGS = {
    'file_path': '/etc/inc.cnf'
}

SQLAdvisor和SOAR的路径

OPTIMIZE_SETTINGS = {
    'sqladvisor_cli': '/usr/bin/sqladvisor',
    'soar_cli': '/usr/local/SOAR/bin/soar'
}

邮件

MAIL = {
    'smtp_host': 'smtp.163.com',  # 邮件服务器
    'smtp_port': 25,  # SMTP协议默认端口是25
    'mail_user': 'sql_see@163.com',  # 邮件用户名
    'mail_pass': 'see123',  # 授权码
    'see_addr': 'http://xxx.xxx.xxx.xxx:81',  # see项目访问地址
}

启动所有服务

# mysql  3306端口
/etc/init.d/mysqld start

# inception  6669端口
nohup /usr/local/inception-master/builddir/mysql/bin/Inception --defaults-file=/etc/inc.cnf &

# redis  6379端口
redis-server /etc/redis.conf

# nginx  81端口
/usr/local/nginx/sbin/nginx

# see  8090端口
source /usr/local/seevenv/bin/activate
cd /usr/local/seevenv/see-master/backend
nohup python manage.py celery worker -c 10 -B --loglevel=info &
gunicorn -c sqlweb/gunicorn_config.py sqlweb.wsgi

启动都OK！可以使用啦：

http://xxx.xxx.xxx.xxx:81/ # see 项目

http://xxx.xxx.xxx.xxx:81/api/docs/ # see api 文档

推荐用Chrome浏览器访问

问题记录

yum install -y Percona-Server-shared-56 失败

yum 安装Percona MySQL时，提示错误：

The GPG keys listed for the "Percona-Release YUM repository - x86_64" repository are already installed but they are not correct for this package.
Check that the correct key URLs are configured for this repository.
 Failing package is: Percona-Server-client-56-5.6.43-rel84.3.el7.x86_64
 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-Percona

解决方法：yum update percona-release

重启see平台

ps aux|grep python|grep -v grep|cut -c 9-15|xargs kill -15
nohup python manage.py celery worker -c 10 -B --loglevel=info &
gunicorn -c sqlweb/gunicorn_config.py sqlweb.wsgi

解决python3下pymysql对inception支持的问题

ValueError: invalid literal for int() with base 10: 'Inception2'
# 查找pymysql源码修改connections.py文件，/usr/local/seevenv/lib/python3.6/site-packages/pymysql/connections.py

    # 找到此处
    def _request_authentication(self):
        # https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::HandshakeResponse
        if int(self.server_version.split('.', 1)[0]) >= 5:
            self.client_flag |= CLIENT.MULTI_RESULTS

    # 修改为
    def _request_authentication(self):
        # https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::HandshakeResponse
        if self.server_version.split('.', 1)[0] == 'Inception2':
            self.client_flag |= CLIENT.MULTI_RESULTS
        elif int(self.server_version.split('.', 1)[0]) >= 5:
            self.client_flag |= CLIENT.MULTI_RESULTS

解决 Inception

始终反馈”Must start as begin statement”的语法错误
# 查找pymysql源码修改cursors.py文件，/usr/local/seevenv/lib/python3.6/site-packages/pymysql/cursors.py

    # 找到此处
    if not self._defer_warnings:
        self._show_warnings()    

    # 修改为
    if not self._defer_warnings:
        pass

总结

安装还是比较麻烦的，安装完成后基本上就可以省心很多了，当然你还得给开发测试人员开通账号，然后把一些公共的账号回收(这并不简单)，一劳永逸的事哪有那么多呢，不过趁机学习了一波还是挺值的。

see平台不仅是托管审计的功能，sql优化是它的亮点，你可以通过Sqladvisor和SOAR来优化sql，减轻DBA压力(但是好像也没人会记得用这个，就自信呗，就秀)。

参考资料

See项目搭建

SOAR

SQLAdvisor

原创不易，洗文可耻。除非注明，本博文章均为原创，转载请以链接形式标明本文地址。

Rugod

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
See-SQL审计平台介绍及部署

声明:这是我在大学毕业后进入第一家互联网公司学习的内容背景线下数据库，成天有人要求运维执行这sql那sql的，又苦逼又容易背锅，问了下公司的DBA大神，推荐了see审计平台，执行sql有审计记录，留痕留痕留痕，重要的事情说三遍，即使是线下环境，谨防有人删库跑路找不到人，可以解决运维一大痛点。审计平台介绍数据库审计平台作用《对于开发人员》提交代码通过Inception审核，不符合规范代码会有提示，通过Inception 审核后，开发人员可自行发起执行或定时执行，回滚等操作《对于测试人员》提.
复制链接

扫一扫

专栏目录