Introduction to Yearning
Yearning MYSQL is an SQL statement auditing platform. It provides a variety of functions such as query audit, SQL audit, etc., and supports Mysql, which can solve the link between operation and maintenance and development to a certain extent, with rich functions, open source code, and easy installation and deployment!
Project address: https://gitee.com/cookieYe/Yearning
Caution:
Yearning 1.x requires inception to provide SQL audit and rollback functions.
Yearning 2.0 has implemented its own SQL audit/rollback function without relying on Inception
Yearning 功能介绍
- SQL queries
- Query tickets
- Export
- Auto-completion, intelligent prompts
- Query statement auditing
- SQL auditing
- Process chemical sheet
- SQL statement detection and execution
- SQL rollback
- Historical audit trails
- Push
- E-mail ticket push
- DingTalk webhook bot ticket push
- User rights and management
- Role-splitting
- Fine-grained user-based permissions
- enroll
- other
- todoList
- LDAP login
- Dynamic audit rule configuration
- AutoTask is automated
Yearning installation
Yearning does not rely on any third-party SQL auditing tools as an auditing engine, and has implemented its own auditing/rollback related logic.
- Rely only on Mysql databases. The mysql version must be 5.7 or later, and the character set for creating the Yearning library should be UTF8mb4 (only the mysql version required for Yearning)
- Yearning logs only output error levels, no logs can be considered as no running errors!
- Yearning is based on 1080p resolution, and only supports 1080p and above monitor access
Yearning download
Yearning-go officially provides binary download packages, download address. Select the package you want to download to download it.
Source code installation: You need to install some dependencies first, etc., and some related installation steps are as follows:
[root@test ~]# git clone https://github.com/cookieY/Yearning.git
[root@test ~]# mv Yearning /usr/local/yearning
[root@test ~]# cd /usr/local/yearning/
[root@test yearning]# cd install/
[root@test install]# ll
total 26728
-rw-r--r-- 1 root root 57763 Apr 23 16:06 connections.py
-rw-r--r-- 1 root root 16818 Apr 23 16:06 cursors.py
-rw-r--r-- 1 root root 679 Apr 23 16:06 docker_start.sh
-rw-r--r-- 1 root root 27270656 Apr 23 16:06 inception.tar
-rw-r--r-- 1 root root 11762 Apr 23 16:06 Yearning.sh
[root@test install]# tar xf inception.tar
[root@test install]# cd inception/bin/
[root@test bin]# ll
total 12472
-rw-r--r-- 1 501 games 722 Jan 3 10:39 inc.cnf
-rwxr-xr-x 1 501 games 9016999 Oct 12 2017 Inception
-rwxr-xr-x 1 501 games 3747312 Oct 12 2017 mysql
However, now the official allows you to download the .zip package directly and decompress it directly (so it is not recommended to install it with source code here), which is more convenient.
mysql> create database Yearning default character set utf8mb4 collate utf8mb4_unicode_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Yearning |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
You only need to install the Mysql database in advance and configure it as required.
[root@CentOS7-1 ~]# ll
total 14036
-rw-------. 1 root root 1320 Apr 8 05:08 anaconda-ks.cfg
-rw-r--r-- 1 root root 14349178 Apr 11 09:26 Yearning-2.2.0.linux-amd64.zip
Above we downloaded the latest version 2.2.0 directly from github:
[root@CentOS7-1 ~]# cd Yearning-go/
[root@CentOS7-1 Yearning-go]# ll
total 9772
-rw-r--r-- 1 root root 127 Aug 1 2019 conf.toml
drwxr-xr-x 6 root root 116 Apr 6 22:32 dist
-rw-r--r-- 1 root root 620 Jan 8 21:06 docker-compose.yml
-rw-r--r-- 1 root root 597 Aug 21 2019 Dockerfile
-rw-r--r-- 1 root root 177 Aug 23 2019 # README
-rwxr--r-- 1 root root 9985356 Apr 6 22:35 Yearning
-rw-r--r-- 1 root root 283 Jan 15 03:55 yearning.service
Modify the configuration file
vim conf.toml
[Mysql]
Db = "Yearning"
Host = "127.0.0.1"
Port = "3306"
Password = "xxxx"
User = "root"
[General] #数据库加解密key,只可更改一次。
SecretKey = "dbcjqheupqjsuwsm"
SecretKey is the salt that encrypts or decrypts the token/database password. It is recommended that all users change the SecretKey before installing Yearning for the first time (there will be security risks if it is not changed), and the format: uppercase and lowercase letters are acceptable, and the length must be 16 bits.
Note: This key can only be changed during the initial installation! If it is changed again, the previously stored data source password cannot be decrypted, and eventually the relevant data source information cannot be obtained.
initialize
./Yearning -m
If you want to initialize again, you need to delete all the tables in the yearning database, otherwise the repeated execution will be invalid.
Start the service
#默认启动
./Yearning -s
#参数启动
./Yearning -s -b "192.168.1.9" -p "8000"
Help
[root@CentOS7-1 Yearning-go]# ./Yearning -h
version: Yearning/2.2.0 general 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用户密码
Open your browser
Default password: admin/Yearning_admin
Introduction to related function modules
1. User module
Dashboard: The dashboard mainly displays the data of Yearning, including the number of users/data sources/tickets/queries and other charts, and users can change their password/email/real name in the personal information column, and can view the user permissions and application permissions (the application permissions will be invalidated after version 2.1.7).
My Tickets: displays the information of the tickets submitted by the user, and you can modify the SQL statement and submit the tickets that fail to be executed or rejected by clicking the details, and you can view the rollback statement and quickly submit the SQL statement for the tickets that are successfully executed.
Ticket DLL: review DDL-related SQL submissions, view table structures/indexes, and highlight SQL syntax and autocomplete.
DML review: DML-related SQL submissions are reviewed, and SQL syntax is highlighted/auto-completed.
Query: Query/export data SQL syntax highlighting/auto-completion Fast DML statement submission.
2. Audit module
Ticket review: DDL/DML administrator reviews and executes the ticket.
Query Review: The user's query is moderated.
Permission Audit: User permission audit (2.1.7 or earlier)
3. Management management module
User Management: Create, modify, and delete users
Database Management: Add/edit/delete data sources
User Permissions: User permissions are modified/cleared
Function settings: Set message push related information, including DingTalk bot/email, LDAP related information, global configuration information, and global configuration switch
Audit Rule: Configure an SQL detection rule
AutoTask automates the execution of tasks
Version 2.1.4 Added: Users can set up automatic execution of tasks through the change function. If the submitted DML statement meets the requirements of the task. It will be performed automatically without the need for reviewers. This feature is available only for DML statements. Please use with caution!
summary
Yearning is an SQL audit platform, which can solve the problems between operation and maintenance and development to a certain extent, unfortunately only Mysql is supported, and you can try it if you are interested!
That's all for today's sharing, if it helps, welcome to support it with one click triple (like, comment, forward)!
Reader-only group: We sincerely invite you to join the technical exchange group and roll together!
If there are any errors or other problems, please leave comments and corrections. If it helps, welcome to like + forward and share. For more related open source technical articles, please stay tuned!Resource sharing (Xiaobian has carefully prepared various academic Xi materials for 2048G.) Including system operation and maintenance, database, redis, MogoDB, e-book, Java basic course, Java practical project, architect comprehensive tutorial, architect practical project, big data, Docker container, ELK Stack, machine learning Xi, BAT interview intensive lecture video, etc. )