Mysql的审核平台Yearning

Yearning是mysql的审核平台，在mysql的操作过程中，特别是生产环境一个命令错误可能导致整个业务环境出现不可用的情况。所以在mysql的增删改查的过程中，需要谨慎在谨慎，所有用户不能直接连接到mysql，需要通过Yearning进行一个审核，针对mysql的增删改查的操作都需要通过Yearning来在mysql上实现，如果在操作过程中出现错误，Yearning支持回滚，支持工单流程化，支持语句检测，自动审核，支持钉钉或者邮件推送。这样不管那个环境在使用的过程中都有一个记录。

Yearning的安装

下面下载Yearning的包，可以在https://github.com/cookieY/Yearning/releases下载，选择不同的版本，我这里使用的是Yearning-2.3.0-GA-linux-amd64.zip

[root@wulaoer ~]# ls
anaconda-ks.cfg  Yearning-2.3.0-GA-linux-amd64.zip
[root@wulaoer ~]# unzip Yearning-2.3.0-GA-linux-amd64.zip
[root@wulaoer ~]# ls
anaconda-ks.cfg  __MACOSX  Yearning-2.3.0-GA-linux-amd64.zip  Yearning-go
[root@wulaoer ~]# cd Yearning-go/
[root@wulaoer Yearning-go]# ls
conf.toml  dist  docker-compose.yml  Dockerfile  # README  Yearning

查看一下Yearning的参数，使用Yearning -h。可以根据自己的需求选择不同的启动方式。

[root@wulaoer Yearning-go]# ./Yearning -h
version: Yearning/2.3.1 Interstellar author: HenryYee
Usage: Yearning [-m migrate] [-p port] [-s start] [-b web-bind] [-h help] [-c config file]

Options:
 -s  启动Yearning
 -m  数据初始化(第一次安装时执行)
 -p  端口
 -b  钉钉/邮件推送时显示的平台地址
 -x  表结构修复,升级时可以操作。如出现错误可直接忽略。
 -h  帮助
 -c  配置文件路径
 -k  用户权限变更为权限组(2.1.7以下升级至2.1.7及以上使用)
 -f  初始化Admin用户密码

Yearning需要一个数据库存储一些用户信息以及目标数据库的信息，可以在用户数据库中存储给用户授权。这里注意创建的库必须是指定utf8mb4编码，mysql版本需要要5.7以上，因为数据库中有json，5.7以下的版本不支持。

[root@wulaoer Yearning-go]# cat conf.toml
[Mysql]
Db = "Yearning"
Host = "127.0.0.1"
Port = "3306"
Password = "xxxxxx"
User = "root"

[General]
SecretKey = "dbcjqheupqjsuwsm"
Hours = 4

在配置文件中SecretKey值可以是英文大小写，必须是16位，如果不是16位无法创建数据源。此key创建后不可更改，如果修改会导致之前创建的不可用。

初始化并启动

初始化Yearning的数据库，前提是已经在配置文件中已经修改了数据库相关信息，下面创建数据库，并进行初始化。

mysql> CREATE DATABASE  `Yearning` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

[root@wulaoer Yearning-go]# ./Yearning -m

(/var/jenkins_home/workspace/Yearning-go/src/service/migrate.go:32)
[2020-09-21 17:59:40]  [1.23ms]  INSERT INTO `core_accounts` (`username`,`password`,`rule`,`department`,`real_name`,`email`) VALUES ('admin','pbkdf2_sha256$120000$2XPhVC4ME1FW$AL5rg0U3EAqJz3CMfgEaweclFmmqjjg+7OqdqpoThtg=','super','DBA','超级管理员','')
[1 rows affected or returned ]

(/var/jenkins_home/workspace/Yearning-go/src/service/migrate.go:40)
[2020-09-21 17:59:40]  [2.69ms]  INSERT INTO `core_global_configurations` (`authorization`,`ldap`,`message`,`other`,`stmt`,`audit_role`,`board`) VALUES ('global','{"url":"","user":"","password":"","type":1,"sc":"","ldaps":false}','{"web_hook":"","host":"","port":25,"user":"","password":"","to_user":"","mail":false,"ding":false,"ssl":false,"push_type":false}','{"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,'{"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,"DDLColumnsMustHaveIndex":"","DDLAllowChangeColumnPosition":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,"OscChunkTime":0.5,"OscSize":0,"AllowCreateView":false,"AllowCreatePartition":false,"AllowSpecialType":false,"PRIRollBackErr":false}','')
[1 rows affected or returned ]

(/var/jenkins_home/workspace/Yearning-go/src/service/migrate.go:47)
[2020-09-21 17:59:40]  [1.61ms]  INSERT INTO `core_graineds` (`username`,`group`) VALUES ('admin','["admin"]')
[1 rows affected or returned ]

(/var/jenkins_home/workspace/Yearning-go/src/service/migrate.go:51)
[2020-09-21 17:59:40]  [1.92ms]  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数据库初始化后提示初始化成功，Yearning的默认密码：admin/Yearning_admin，初始化后会有提示。初始化后，启动一下Yearning，默认端口是8000，也可以根据参数定义。

验证一下，把上面提示的默认账号和密码登录一下。

Yearning已经安装好了，为了后期方便管理，必须要接入LDAP进行统一管理。

Yearning接入ldap

如果已经安装好了ldap，使用管理员登录之后在管理里的设置

Yearning安装成功，后面就是如何接入mysql进行操作了，这里接入的目标机器是客户端，也就是供用户请求操作的机器。