使用Yearning部署一个工单化SQL语句检测平台

ywz888

发布于 2022-8-23 16:47

浏览

0收藏

• 1. 引入
• 2. 环境准备
▪ 2.1 通过yum安装Docker
▪ 2.2 安装数据源
▪ 2.3 安装部署Yearning
• 3. 简单使用Yearning
• 4. 总结

1. 引入
MySQL是十分热门的开源数据库，在软件开发中广泛用于数据的持久化存储。在软件开发生命周期当中往往会遇到问题，编码人员根据业务需求编写对应模块代码，经由测试人员进行压力测试等性能测试后发现，该模块性能达不到预期指标，结果分析得出是编码人员对数据源交互的SQL语句缺陷，开发人员通常想着先定位问题，然后反馈给DBA。今天分享一款可视化的SQL自动检测平台：Yearning MYSQL，可以方便开发人员初步定位问题，同时将SQL快速提交给DBA处理，DBA可以通过预设的规则来规范SQL语句的规范，必要时可以把错误的SQL语句快速回滚。

• 开源地址：https://gitee.com/cookieYe/Yearning
• 官方手册：https://guide.yearning.io/

使用Yearning部署一个工单化SQL语句检测平台-鸿蒙开发者社区

Yearning MYSQL 是一个开源的SQL语句审核平台。该平台提供了数据源查询审计、SQL审核等多种功能，支持MySQL 5.7及以上版本，可以在一定程度上解决运维与开发之间的那一环，功能丰富，代码开源，安装部署容易,本文运行环境：

[root@VM-0-12-centos ~]# cat /etc/redhat-release
CentOS Linux release 8.0.1905 (Core)

2. 环境准备
2.1 通过yum安装Docker
卸载系统之前的 docker：

[root@VM-0-12-centos ~]# sudo yum remove docker \
docker-client \
docker-client-latest \
docker-common \
docker-latest \
docker-latest-logrotate \
docker-logrotate \
docker-engine

安装 docker-CE：

[root@VM-0-12-centos ~]# sudo yum install -y yum-utils \
device-mapper-persistent-data \
lvm2

设置 docker repo 的 yum 位置：

[root@VM-0-12-centos ~]# sudo yum-config-manager \
--add-repo \
https://download.docker.com/linux/centos/docker-ce.repo

安装 docker，以及 docker-cli：

[root@VM-0-12-centos ~]# sudo yum install docker-ce docker-ce-cli containerd.io

启动 docker：

[root@VM-0-12-centos ~]# sudo systemctl start docker

设置 docker 开机自启动：

[root@VM-0-12-centos ~]# sudo systemctl enable docker

2.2 安装数据源
使用Docker拉取MySQL 8.0最新版本的镜像：

[root@VM-0-12-centos ~]# docker pull mysql:8.0
8.0: Pulling from library/mysql
c1ad9731b2c7: Pull complete 
54f6eb0ee84d: Pull complete 
...
Status: Downloaded newer image for mysql:8.0
docker.io/library/mysql:8.0

创建文件夹：

[root@VM-0-12-centos ~]# mkdir -p /opt/datas/docker/mysql/conf
[root@VM-0-12-centos ~]# mkdir -p /opt/datas/docker/mysql/data

运行镜像：

[root@VM-0-12-centos ~]# docker run \
--name mysql \
--restart=unless-stopped \
-it -p 3306:3306 \
-v /opt/datas/docker/mysql/conf/my.cnf:/etc/mysql/my.cnf \
-v /opt/datas/docker/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD='MySQL2022.~#' \
-d mysql

也可以使用GreatSQL，使用Docker拉取GreatSQL最新版本的镜像：

[root@VM-0-12-centos ~]# docker pull greatsql/greatsql
Using default tag: latest
Alatest: Pulling from greatsql/greatsql
a1d0c7532777: Pull complete 
0689c7a54f49: Pull complete 
...
Status: Downloaded newer image for greatsql/greatsql:latest
docker.io/greatsql/greatsql:latest

[root@VM-0-12-centos ~]# docker run -d \
--name greatsql --hostname=greatsql \
-p 3306:3306 -p 33060:33060 -p 33061:33061 \
-e MYSQL_ROOT_PASSWORD='GreatSQL.~#' \
greatsql/greatsql

为需要部署Yearning的服务器中的数据库创建Yearning库，Yearning 不依赖于任何第三方 SQL 审核工具作为审核引擎,内部已自己实现审核/回滚相关逻辑。仅依赖 Mysql 数据库。mysql 版本必须为5.7及以上版本(8.0及以上请将sql_mode 设置为空)并已事先自行安装完毕且创建 Yearning 库,字符集应为 UTF8mb4 。

2.3 安装部署Yearning
创建Yearning的目录：

[root@VM-0-12-centos ~]# mkdir -p /mydata/yearning
[root@VM-0-12-centos ~]# cd /mydata/yearning

下载并解压Yearning：

[root@VM-0-12-centos yearning]# wget https://github.com/cookieY/Yearning/releases/download/2.3.5/Yearning-2.3.5-linux-amd64.zip
[root@VM-0-12-centos yearning]# unzip Yearning-2.3.5-linux-amd64.zip

修改conf.toml文件内容：

[Mysql]
Db = "Yearning"
Host = "127.0.0.1"
Port = "3306"
Password = "GreatSQL.~#"
User = "root"

[General]
# 数据库加解密key，只可更改一次
SecretKey = "dbcjqheupqjsuwsm"
Hours = 4

初始化Yearning，自动分配管理员账号及初始密码，用户名: admin，密码:Yearning_admin：

[root@VM-0-12-centos yearning]# ./Yearning install

(/Users/henryyee/Yearning-go/src/service/migrate.go:32) 
[2022-06-13 17:02:52]  [0.68ms]  INSERT INTO `core_accounts` (`username`,`password`,`rule`,`department`,`real_name`,`email`) VALUES ('admin','pbkdf2_sha256$120000$Ams9FoCyweoY$F5GLRMRRwL8ZW8PAYs6XknhLZ0Up2LLMLAVIw4t1Sd8=','super','DBA','超级管理员','')  
[1 rows affected or returned ] 

(/Users/henryyee/Yearning-go/src/service/migrate.go:40) 
[2022-06-13 17:02:52]  [1.70ms]  INSERT INTO `core_global_configurations` (`authorization`,`ldap`,`message`,`other`,`stmt`,`audit_role`,`board`) VALUES ('global','{"url":"","user":"","password":"","type":"(\u0026(objectClass=organizationalPerson)(sAMAccountName=%s))","sc":"","ldaps":false}','{"web_hook":"","host":"","port":25,"user":"","password":"","to_user":"","mail":false,"ding":false,"ssl":false,"push_type":false,"key":""}','{"limit":"1000","idc":["Aliyun","AWS"],"multi":false,"query":false,"exclude_db_list":[],"insulate_word_list":[],"register":false,"export":false,"per_order":2,"ex_query_time":60,"query_timeout":0}',0,'{"DMLAllowLimitSTMT":false,"DMLInsertColumns":false,"DMLMaxInsertRows":10,"DMLWhere":false,"DMLOrder":false,"DMLSelect":false,"DDLCheckTableComment":false,"DDlCheckColumnComment":false,"DDLCheckColumnNullable":false,"DDLCheckColumnDefault":false,"DDLEnableAcrossDBRename":false,"DDLEnableAutoincrementInit":false,"DDLEnableAutoIncrement":false,"DDLEnableAutoincrementUnsigned":false,"DDLEnableDropTable":false,"DDLEnableDropDatabase":false,"DDLEnableNullIndexName":false,"DDLIndexNameSpec":false,"DDLMaxKeyParts":5,"DDLMaxKey":5,"DDLMaxCharLength":10,"MaxTableNameLen":10,"MaxAffectRows":1000,"MaxDDLAffectRows":0,"SupportCharset":"","SupportCollation":"","CheckIdentifier":false,"MustHaveColumns":"","DDLMultiToSubmit":false,"DDLPrimaryKeyMust":false,"DDLAllowColumnType":false,"DDLImplicitTypeConversion":false,"DDLAllowPRINotInt":false,"DDLEnableForeignKey":false,"DDLTablePrefix":"","DDLColumnsMustHaveIndex":"","DDLAllowChangeColumnPosition":false,"DDLCheckFloatDouble":false,"IsOSC":false,"OscBinDir":"","OscDropNewTable":false,"OscDropOldTable":false,"OscCheckReplicationFilters":false,"OscCheckAlter":false,"OscAlterForeignKeysMethod":"rebuild_constraints","OscMaxLag":1,"OscRecursionMethod":"processlist","OscCheckInterval":1,"OscMaxThreadConnected":25,"OscMaxThreadRunning":25,"OscCriticalThreadConnected":20,"OscCriticalThreadRunning":20,"OscPrintSql":false,"OscChunkSize":0,"OscChunkTime":0.5,"OscSize":0,"AllowCreateView":false,"AllowCreatePartition":false,"AllowSpecialType":false,"PRIRollBack":false,"OscSleep":0,"OscCheckUniqueKeyChange":false,"OscLockWaitTimeout":60}','')  
[1 rows affected or returned ] 

(/Users/henryyee/Yearning-go/src/service/migrate.go:47) 
[2022-06-13 17:02:52]  [0.48ms]  INSERT INTO `core_graineds` (`username`,`group`) VALUES ('admin','["admin"]')  
[1 rows affected or returned ] 

(/Users/henryyee/Yearning-go/src/service/migrate.go:51) 
[2022-06-13 17:02:52]  [1.12ms]  INSERT INTO `core_role_groups` (`name`,`permissions`) VALUES ('admin','{"ddl_source":[],"dml_source":[],"auditor":[],"query_source":[]}')  
[1 rows affected or returned ] 
初始化成功!
 用户名: admin
密码:Yearning_admin

这个时候可以看到Yearning库中已经存放了初始化的数据了：

(Tue Jun 14 14:20:59 2022)[root@GreatSQL][Yearning]>show tables;
+----------------------------+
| Tables_in_Yearning         |
+----------------------------+
| core_accounts              |
| core_auto_tasks            |
| core_data_sources          |
| core_global_configurations |
| core_graineds              |
| core_query_orders          |
| core_query_records         |
| core_role_groups           |
| core_rollbacks             |
| core_sql_orders            |
| core_sql_records           |
| core_workflow_details      |
| core_workflow_tpls         |
+----------------------------+
13 rows in set (0.00 sec)
(Tue Jun 14 14:26:42 2022)[root@GreatSQL][Yearning]>select username,rule,department,real_name from core_accounts;
+----------+-------+------------+-----------------+
| username | rule  | department | real_name       |
+----------+-------+------------+-----------------+
| admin    | super | DBA        | 超级管理员      |
+----------+-------+------------+-----------------+
1 rows in set (0.00 sec)

然后就可以启动Yearning了：

[root@VM-0-12-centos yearning]# ./Yearning run
检查更新.......
数据已更新!

    __  __          
    _ \/ /_________ 
    __  /_  _ \  _ \
    _  / /  __/  __/
    /_/  \___/\___/   yee v0.2.3
-----Easier and Faster-----

3. 简单使用Yearning
Yearning前台页面简洁大方，不仅可以对使用平台的用户进行明确的权限划分还有高可用性的审计功能等，大致总结如下：

• Yearning内置SQL编辑器可以快捷编辑SQL语句
• Yearning支持SQL审核流程化工单操作，可以对SQL语句进行检测SQL语句执行SQL回滚
• Yearning实现推送E-mail工单推送和钉钉webhook机器人工单推送

Yearning在服务器上部署运行后，就可以通过http://服务器ip:8000/#/login进行访问了，默认账号为初始化账号，页面如图：

使用Yearning部署一个工单化SQL语句检测平台-鸿蒙开发者社区