建设银行互联网站,品牌宣传,wordpress分享朋友圈,企业网站建设问题Archery-SQL审核查询平台 文章目录Archery-SQL审核查询平台一、功能列表介绍1.1、SQL审核MySQL实例非MySQL实例审核执行分离SQL工单自动审批、高危语句驳回快速上线其他实例定时执行1.2、SQL查询多类型数据库支持授权管理页面体验1.3、SQL优化慢日志管理SQL语句优化1.4、实例管…Archery-SQL审核查询平台 文章目录Archery-SQL审核查询平台一、功能列表介绍1.1、SQL审核MySQL实例非MySQL实例审核执行分离SQL工单自动审批、高危语句驳回快速上线其他实例定时执行1.2、SQL查询多类型数据库支持授权管理页面体验1.3、SQL优化慢日志管理SQL语句优化1.4、实例管理会话管理数据库管理账号管理参数配置1.5、工具插件PTArchiverBinlog2SQLMy2SQLSchemaSync1.6、资源项目组1.7、权限角色组1.8、工作流1.9、配置管理1.10、消息通知1.11、可视化1.12、功能导图二、部署2.1、部署2.1.1、安装docker版本18.09.92.1.2、安装docker-compose版本1.29.22.1.3、安装mysql2.1.4、安装archery2.2、系统配置三、测试3.1、增加资源组3.2、新建实例3.3、修改权限组3.4、配置审批流程3.5、修改新用户注册配置3.6、创建各类型测试用户3.7、创建上线sql/回滚3.8、申请查询权限3.9、申请归档权限3.10、数据脱敏脱敏规则配置四、升级4.1、执行数据库变更脚本(请注意数据备份)4.2、更新服务五、踩坑记录5.1.执行上线sql时报错 lookup mysql on 127.0.0.11:53: no such host5.2、执行上线SQL报错Execute: Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation (sql: SHOW MASTER STATUS;).Execute: 无法获取master binlog信息.Archery定位于SQL审核查询平台旨在提升DBA的工作效率支持多种数据库的SQL上线和查询同时支持丰富的MySQL运维功能所有功能都兼容手机端操作。一、功能列表介绍
数据库查询审核执行备份数据字典慢日志会话管理账号管理参数管理数据归档MySQL√√√√√√√√√√MsSQL√×√×√×××××Redis√×√×××××××PgSQL√×√×××××××Oracle√√√√√×××××MongoDB√√√×××××××Phoenix√×√×××××××ODPS√×××××××××ClickHouse√√√×××××××
1.1、SQL审核
MySQL实例
基于Inception/goInception实现集成审核、执行、备份
非MySQL实例
支持提交和执行工单依托工作流实现流程化管理
审核执行分离
审批和执行可以分配给不同的用户进行操作
SQL工单自动审批、高危语句驳回
支持正则判断工单是否需要人工审批开启自动审批后不在正则范围内的SQL语句无需审批系统自动审核自主控制SQL是否自动驳回可自主配置对inception审核驳回的场景支持警告驳回和异常驳回
快速上线其他实例
在工单详情可快速提交相同SQL内容到其他实例可适用于testbetaga等多套环境维护的需求
定时执行
工单审核通过后可以选择定时执行或者立即执行
1.2、SQL查询
多类型数据库支持
MySQL 表级授权、脱敏查询MsSQL 库级授权、脱敏查询Redis 库级授权PostgreSQL 库级授权、脱敏查询Oracle 库级授权、脱敏查询MongoDB 库级授权Phoenix 库级授权ODPS 库级授权ClickHouse 库级授权
授权管理
工作流控制SQL查询授权支持库表级别的权限限制以及授权时间查询结果集的限制支持部分语句的动态查询脱敏有限的功能支持前台管理用户权限对用户权限进行修改和维护支持查询导出、查询日志审计
页面体验
库、表、字段补全提示多结果级展示表结构查看
1.3、SQL优化
慢日志管理
基于PT收集慢日志需要单独部署
SQL语句优化
基于SQLAdvisor|SOAR|SQLTuning的全方位优化建议
1.4、实例管理
会话管理
支持查看和批量终止会话支持查看事物、锁信息
数据库管理
管理实例数据库支持添加
账号管理
管理实例账号支持增加、授权、删除
参数配置
可修改实例动态参数并记录修改历史
1.5、工具插件
PTArchiver
支持使用pt-archiver归档MySQL数据支持直接添加配置和由用户申请归档
Binlog2SQL
将Binlog2SQL模块可视化从MySQL binlog解析出你要的SQL
My2SQL
My2SQL可视化更高效地解析MySQL binlog快速回滚
SchemaSync
对比不同数据库的Schema信息输出修改语句和回滚语句SchemaSync不仅限于表结构它可以处理的对象还有视图、事件、存储过程、函数、触发器、外键
1.6、资源项目组
支持自定义资源(项目组管理资源组和关联对象资源组成员之间审批流程、实例配置、消息通知等资源隔离
1.7、权限角色组
权限可以分配给用户也可以分配给权限组支持对大多数操作进行限制独立控制用户的审核、执行等操作权限
1.8、工作流
工作流审批流程支持多层级多用户并且隔离资源组不同资源组不同的工单类型可以配置不同的审批层级
1.9、配置管理
系统配置项、工作流审批流程可在前端页面动态修改无需重启服务实时生效
1.10、消息通知
支持钉钉、企业微信、邮件通知及时知晓工单状态变化
1.11、可视化
使用pyecharts实现工单、查询维度的可视化统计
1.12、功能导图 二、部署
2.1、部署
服务器信息
系统centos7.9
内网ip192.168.0.213
公网ip121.37.17.85
2.1.1、安装docker版本18.09.9
# wget https://download.docker.com/linux/centos/docker-ce.repo #下载docker的yum源
# mv docker-ce.repo /etc/yum.repos.d
# yum -y install docker-ce-18.09.9
# docker -v
Docker version 20.10.6, build 370c289
# systemctl start docker
# systemctl enable docker # 修改docker的日志规则
# vim /etc/docker/daemon.json #docker18版本之后无效
{
log-driver:json-file,log-opts:{ max-size :500m,max-file:30} #最大日志500m 最大保留个数30,一定删除注释
},
{registry-mirrors:[https://docker.mirrors.ustc.edu.cn]
}#修改docker的数据存储路径
# vim /usr/lib/systemd/system/docker.service
在ExecStart/usr/bin/dockerd-current \ 下添加
--graph /opt/docker \ #docker存储路径
--registry-mirrorhttp://hub-mirror.c.163.com \ #加速地址
--registry-mirrorhttps://docker.mirrors.ustc.edu.cn \
--registry-mirrorhttps://p31wiatp.mirror.aliyuncs.com \
-H tcp://0.0.0.0:6935 -H unix://var/run/docker.sock #portainer可以检测到# systemctl daemon-reload
# systemctl restart docker
# docker info |grep Root #查看docker数据存储位置
Docker Root Dir: /opt/docker
# ss -ltnp |grep 69352.1.2、安装docker-compose版本1.29.2
#安装docker-compose
# curl -L https://github.com/docker/compose/releases/download/1.29.2/docker-compose-$(uname -s)-$(uname -m) -o /usr/local/bin/docker-compose
# chmod x /usr/local/bin/docker-compose
# ln -s /usr/local/bin/docker-compose /usr/bin/docker-compose
# docker-compose --version
docker-compose version 1.29.2, build 5becea4c
2.1.3、安装mysql
创建目录
# mkdir -p /opt/mysql_8_0_31/mysql/data
# mkdir -p /opt/mysql_8_0_31/mysql/sql
# cd /opt/mysql_8_0_31/准备初始化sql
# vim /opt/mysql_8_0_31/mysql/sql/archery.sql
CREATE DATABASE archery CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;create user archery% identified by Archeryweb%123 ;GRANT ALL on archery.* TO archery%;GRANT REPLICATION SLAVE,REPLICATION CLIENT,Select,Process,Create,Update,Insert,Drop,DELETE ON *.*
TO archery%;
# 特别注意Drop,DELETE权限生产不允许授这个权限但是没有Drop,DELETE权限不能执行一些回滚sql请联系管理员手动执行回滚sqlflush privileges;编写docker-compose文件
# pwd
/opt/mysql_8_0_31/
# vim docker-compose-mysql.yaml
version: 3.7
services:mysql8_0_31:image: docker.io/mysql:8.0.31ports:- 3366:3306container_name: mysql8_0_31environment:- MYSQL_ROOT_PASSWORDArcheryweb%123volumes:- ./mysql/data:/var/lib/mysql- ./mysql/sql/archery.sql:/docker-entrypoint-initdb.d/archery.sqlrestart: alwaysdocker-compose启动mysql
# pwd
/opt/mysql_8_0_31/
# docker-compose -f docker-compose-mysql.yaml create #根据docker-compose文件创建容器及网络或存储卷
# docker-compose -f docker-compose-mysql.yaml up -d # 后台启动容器2.1.4、安装archery
上传archery.tar.gz包
# pwd
/opt# ls
Archery.tar.gz containerd docker# 解压tar包
# tar xzvf Archery.tar.gz
# ls
Archery Archery.tar.gz containerd docker# 查看目录结构
# tree ./Archery #如果提示没有tree命令请执行yum -y install treeArchery
├── archery
│ ├── docs.md
│ ├── downloads
│ │ ├── archiver
│ │ ├── binlog2sql
│ │ └── schemasync
│ ├── keys
│ ├── logs
│ │ ├── archery.log
│ │ ├── qcluster.log
│ │ └── supervisord.log
│ ├── notify.py
│ ├── notify.py.bak
│ ├── settings.py
│ ├── soar_path
│ │ ├── soar_path
│ │ └── sqladvisor_path
│ ├── soar.yaml
│ └── sql
│ └── migrations
│ ├── 0001_initial.py
│ ├── __init__.py
│ └── __pycache__
│ ├── 0001_initial.cpython-39.pyc
│ └── __init__.cpython-39.pyc
├── docker-compose.yml
├── inception
│ └── config.toml
└── mysql└── my.cnf
修改mysql连接为外置
修改docker-compose
# cd Archery
# pwd
/opt/Archery# vim docker-compose.yml
version: 3services:redis:image: redis:5container_name: redisrestart: alwayscommand: redis-server --requirepass 123456expose:- 6379#将mysql的注释掉# mysql:# image: mysql:5.7# container_name: mysql# restart: always# ports:# - 3306:3306# volumes:# - ./mysql/my.cnf:/etc/mysql/my.cnf# - ./mysql/datadir:/var/lib/mysql# environment:# MYSQL_DATABASE: archery# MYSQL_ROOT_PASSWORD: 123456goinception:image: hanchuanchuan/goinceptioncontainer_name: goinceptionrestart: alwaysports:- 4000:4000volumes:- ./inception/config.toml:/etc/config.tomlarchery:image: hhyo/archery:v1.9.1container_name: archeryrestart: alwaysports:- 9123:9123volumes:- ./archery/settings.py:/opt/archery/local_settings.py- ./archery/soar.yaml:/etc/soar.yaml- ./archery/docs.md:/opt/archery/docs/docs.md- ./archery/notify.py:/opt/archery/sql/notify.py- ./archery/downloads:/opt/archery/downloads- ./archery/soar_path/sqladvisor_path:/opt/archery/soar_path/sqladvisor_path- ./archery/soar_path/soar_path:/opt/archery/soar_path/soar_path- ./archery/sql/migrations:/opt/archery/sql/migrations- ./archery/logs:/opt/archery/logs- ./archery/keys:/opt/archery/keysentrypoint: dockerize -wait tcp://192.168.0.213:3366 -wait tcp://redis:6379 -timeout 60s /opt/archery/src/docker/startup.shenv_file:- .env
# 注意
entrypoint: dockerize -wait tcp://192.168.0.213:3366 -wait tcp://redis:6379 -timeout 60s /opt/archery/src/docker/startup.sh 这里的mysql地址一定要修改修改.env文件
# pwd
/opt/Archery
# ls -a
. .. archery docker-compose.yml .env inception mysql# vim .env
NGINX_PORT9123# https://django-environ.readthedocs.io/en/latest/quickstart.html#usage
# https://docs.djangoproject.com/zh-hans/4.1/ref/settings/
DEBUGfalse#修改mysql配置信息
DATABASE_URLmysql://archery:Archeryweb%123192.168.0.213:3366/archery
CACHE_URLredis://redis:6379/0?PASSWORD123456# https://docs.djangoproject.com/en/4.0/ref/settings/#csrf-trusted-origins
CSRF_TRUSTED_ORIGINShttp://127.0.0.1:9123# https://django-auth-ldap.readthedocs.io/en/latest/
ENABLE_LDAPfalse
AUTH_LDAP_ALWAYS_UPDATE_USERtrue
AUTH_LDAP_USER_ATTR_MAPusernamecn,displaydisplayname,emailemail# https://django-q.readthedocs.io/en/latest/configure.html#
Q_CLUISTER_WORKERS4
Q_CLUISTER_TIMEOUT60
Q_CLUISTER_SYNCfalse修改setting.py
# cd /opt/Archery/archery
# vim settings.py# 修改SECRET_KEY
# 关闭debug模式
DEBUG False
# 设置ALLOWED_HOSTS建议限制内网访问
ALLOWED_HOSTS [.example.com, # Allow domain and subdomains.example.com., # Also allow FQDN and subdomains
]
# 请求大小限制如果提交SQL语句过大可以修改该值
DATA_UPLOAD_MAX_MEMORY_SIZE 15728640
# 密码校验用户注册和添加密码校验规则
AUTH_PASSWORD_VALIDATORS [{NAME: django.contrib.auth.password_validation.UserAttributeSimilarityValidator,},{NAME: django.contrib.auth.password_validation.MinimumLengthValidator,OPTIONS: {min_length: 9, }},{NAME: django.contrib.auth.password_validation.CommonPasswordValidator,},{NAME: django.contrib.auth.password_validation.NumericPasswordValidator,},
]#mysql配置
DATABASES {default: {ENGINE: django.db.backends.mysql,NAME: archery, # 数据库名称USER: archery, # 数据库用户PASSWORD: Archeryweb%123, # 数据库密码HOST: 192.168.0.213, # 数据库HOST如果是docker启动并且关联可以使用容器名连接PORT: 3366, # 数据库端口OPTIONS: {init_command: SET sql_modeSTRICT_TRANS_TABLES, # SQL_MODE为了兼容select * group by可以按需调整charset: utf8mb4},TEST: {NAME: test_archery,CHARSET: utf8mb4,},}
}# Django-Q配置
Q_CLUSTER {name: archery,workers: 4,recycle: 500,timeout: 60,compress: True,cpu_affinity: 1,save_limit: 0,queue_limit: 50,label: Django Q,django_redis: default,sync: False # 本地调试可以修改为True使用同步模式}# 缓存配置
CACHES {default: {BACKEND: django_redis.cache.RedisCache,LOCATION: redis://redis:6379/0, # redis://host:port/dbOPTIONS: {CLIENT_CLASS: django_redis.client.DefaultClient,PASSWORD: 123456}}
}docker-compose启动Archery
# pwd
/opt/Archery
# docker-compose create #根据docker-compose文件创建容器及网络或存储卷
# docker-compose up -d # 后台启动容器# 表结构初始化
docker exec -ti archery /bin/bash
cd /opt/archery
source /opt/venv4archery/bin/activate
python3 manage.py makemigrations sql
python3 manage.py migrate # 数据初始化
python3 manage.py dbshellsql/fixtures/auth_group.sql
python3 manage.py dbshellsrc/init_sql/mysql_slow_query_review.sql# 创建管理用户
python3 manage.py createsuperuser
#账户admin 邮箱adminadmin.com 密码Fysladminexit# 重启
docker restart archery# 日志查看和问题排查
docker logs archery -f --tail50访问http://{ip}:9123
账户admin 密码Fysladmin 2.2、系统配置
GO_INCEPTION_HOST 192.168.0.213
GO_INCEPTION_PORT 4000
**BACKUP_HOST ** 192.168.0.213
BACKUP_PORT 3366
BACKUP_USER root
BACKUP_PASSWORD Archeryweb%123 SQLADVISOR_PATH /opt/archery/soar_path/sqladvisor_path
SOAR_PATH /opt/archery/soar_path/soar_path 三、测试
3.1、增加资源组 飞书机器人webhook地址https://open.larksuite.com/open-apis/bot/v2/hook/xxxxxx 3.2、新建实例 注意数据库账户权限需具有以下权限
1.对目标DB库需要有所有权限2.对所有DB库具有REPLICATION SLAVE、REPLICATION CLIENT 、Select和Process权限关于这个权限官方文档并没有说明此处需注意。
关于账户授权的sql语句示例如下
create user archery% identified by Archeryweb%123 ;GRANT ALL on archery.* TO archery%;GRANT REPLICATION SLAVE,REPLICATION CLIENT,Select,Process ON *.*
TO archery%;flush privileges;3.3、修改权限组 3.4、配置审批流程 3.5、修改新用户注册配置 测试新用户注册 账户test001
密码fysltest001 登录 可以看到之前配置的飞书机器人webhook已经发出通知了 登录DBA的账户对申请进行批复 到这新用户注册的权限组测试完毕新注册的用户仅能申请SQL查询权限
3.6、创建各类型测试用户 PM权限用户:
账户PM-test001
密码Fysltest03
DBA权限用户:
账户DBA-test001
密码Fysltest02
RD权限用户:
账户RD-test004
密码Fysltest03 3.7、创建上线sql/回滚
创建测试表
create table test(id int unsigned not null auto_increment primary key comment id,t2_name varchar(10) not null default a comment a:有效,x:无效,cun varchar(10) not null default y comment y:可用,n:不可用,email varchar(254) NOT NULL default comment 账户邮箱
) comment table;提交后根据审批流程依次登录相关的测试账户审批
测试提交回滚SQL这里省略回滚步骤跟SQL上线步骤是一样的
3.8、申请查询权限
略
3.9、申请归档权限
略
归档是将数据归档成sql文件或者csv文件本步骤详情操作省略
3.10、数据脱敏
创建上线SQL插入测试数据
INSERT INTOtest(id, t2_name, cun, email)
VALUES(1, a, y, zhangsangmail.com);
INSERT INTOtest(id, t2_name, cun, email)
VALUES(2, a, y, chensigmail.com);
INSERT INTOtest(id, t2_name, cun, email)
VALUES(3, a, y, wangwugmail.com);配置数据脱敏字段 脱敏规则配置 测试数据脱敏效果 四、升级
升级分为两步, 数据库变更和服务更新, 以 Release v1.3.8 升级为 Release v1.5.0 为例
4.1、执行数据库变更脚本(请注意数据备份)
脚本文件地址: init_sql 需要执行当前版本至目标版本之间的所有数据库变更脚本
v1.3.8_v1.4.0.sql
v1.4.2_v1.4.3.sql
v1.4.3_v1.4.5.sql
v1.4.5_v1.5.0.sql4.2、更新服务
1.使用 docker-compose -f docker-compose.yml down 停止旧版本服务, 删除旧版本服务使用的所有 docker 镜像
2.下载对应 Release 版本的压缩包: Releases
3.对比 src/docker-compose 文件夹, 按照最新配置进行调整, 注意务必更新 settings.py 文件, 否则可能无法启动, 确认配置没问题后使用 docker-compose -f docker-compose.yml up -d 启动新版本服务
4.执行数据库升级脚本, 跨多版本升级时需要执行当前版本至目标版本之间的所有数据库变更脚本(实际升级时注意替换文件名)
docker exec -it archery /bin/bash
source /opt/venv4archery/bin/activate
python manage.py dbshell src/init_sql/v1.3.8_v1.4.0.sql
python manage.py dbshell src/init_sql/v1.4.2_v1.4.3.sql
python manage.py dbshell src/init_sql/v1.4.3_v1.4.5.sql
python manage.py dbshell src/init_sql/v1.4.5_v1.5.0.sql5.应用数据库变更 python manage.py migrate
6.使用 docker-compose -f docker-compose.yml restart 重新启动服务
五、踩坑记录
5.1.执行上线sql时报错 lookup mysql on 127.0.0.11:53: no such host 登录服务器查看容器 goinception日志
# docker logs -f goinception
################################################
Warning: The following parameters will be deprecated and replaced with disable_types:enable_blob_typeenable_json_typeenable_enum_set_bitenable_timestamp_type
https://github.com/hanchuanchuan/goInception/pull/418
################################################
time2023/01/15 10:36:34.468 levelerror msgcon:14 解析失败! line 5 column 2 near \) comment table\ filesession_inception.go funcexecuteInc line220
time2023/01/15 10:36:34.469 levelerror msg create table test(\n id int unsigned not null auto_increment primary key comment id,\n t2_name varchar(10) not null default a comment a:有效,x:无效,\n cun varchar(10) not null default y comment y:可用,n:不可用,\n) comment table filesession_inception.go funcexecuteInc line221
time2023/01/15 10:38:53.933 levelerror msgcon:17 con:17 dial tcp: lookup mysql on 127.0.0.11:53: no such host filesession_inception.go funcparseOptions line2134可以看到goinception的备份数据库地址不正确还是默认的127.0.0.1
解决方案
修改goinception配置文件将备份数据库信息修改
# pwd
/opt/Archery/inception# ls
config.toml# vim config.toml
...
##修改备份数据库信息
backup_host 192.168.0.213
backup_port 3366
backup_user root
backup_password Archeryweb%123# cd ../
# pwd
/opt/Archery# docker-compose down# docker-compose up -d
再次提交SQL上线
可以看到执行正常结束 查看回滚SQL 执行上线SQL报错 lookup mysql on 127.0.0.11:53: no such host问题解决
5.2、执行上线SQL报错Execute: Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation (sql: SHOW MASTER STATUS;).Execute: 无法获取master binlog信息. 登录服务器查看容器 goinception日志
# docker logs -f goinception
is operation filesession_inception.go funcmysqlFetchMasterBinlogPosition line1570
time2020/09/03 15:50:48.804 levelerror msgcon:8 Error 1227: Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation fileconn.go funcraw line68
time2020/09/03 15:50:48.804 levelerror msgcon:8 Error 1227: Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation filesession_inception.go funcmysqlFetchMasterBinlogPosition line1570解决方案
去连接每个实例的账户需要同时授予Replication Client、Replication Slave、Process和Select权限缺一不可
GRANT REPLICATION SLAVE,REPLICATION CLIENT,Select,Process,Create,Update,Insert,Drop,DELETE ON *.*
TO archery%;# 特别注意Drop,DELETE权限生产不允许授这个权限但是没有Drop,DELETE权限不能执行一些回滚sql请联系管理员手动执行回滚sqlflush privileges;关于这个权限官方文档并没有说明此处需注意。
