Year open source SQL audit platform installation and use

1, Introduction to year

year is the MYSQL SQL statement audit platform. Provide query audit, SQL audit and other functions.

The edition of year 1. X needs the function of SQL audit and rollback provided by inception.
Since year 2.0 does not need to rely on perception, it has implemented SQL audit / rollback function
the version of this article is Yearning2.2.0.

Two, installation

Download address

https://github.com/cookieY/Yearning/releases/download/v2.2.0/Yearning-2.2.0.linux-amd64.zip

Matters needing attention

Year does not rely on any third-party SQL audit tool as an audit engine. Internal audit / rollback logic has been implemented by itself.
Only rely on Mysql database.
mysql version must be 5.7 or above. Please install it yourself and create Yearning Library in advance. The character set should be UTF8mb4 (only mysql version required by Yearning)
The annual log only outputs the error level. Without the log, no running error can be considered!
Year based on 1080p resolution development only supports 1080p and above display access
Due to the use of many new front-end technology stacks, please use the latest version of Chrome (excluding 360 and other magic versions)
Installation steps

unzip Yearning-2.2.0.linux-amd64.zip -d /usr/local/
cd /usr/local/Yearning-go/

cat conf.toml
[Mysql]
Db = "Yearning"
Host = "127.0.0.1"
Port = "3306"
Password = "Yearning"
User = "Yearning"

[General]  #The database encryption and decryption key can only be changed once.
SecretKey = "dbcjqheupqjsuwsm"

SecretKey is the salt of token / database password encryption / decryption.
It is recommended that all users change the SecretKey before installing year for the first time (if not, there will be a security risk)
Format: both upper and lower case letters, length must be 16 bits
 Special attention:
This key can only be changed during the initial installation! It cannot be changed again after that! If it is changed again, the previously stored data source password will not be decrypted, and the related data source information will not be obtained

# ./Yearning -h
version: Yearning/2.2.0 author: HenryYee
Usage: Yearning [-m migrate] [-p port] [-s start] [-b web-bind] [-h help] [-c config file]

Options:
 -s  start-up Yearning
 -m  Data initialization(Executed on first installation)
 -p  port
 -b  Nail/Platform address displayed when mail is pushed
 -x  Table structure repair,It can be operated during upgrade. If there is an error, it can be ignored directly.
 -h  Help
 -c  Profile path
 -k  Change user authority to authority group(2.1.7 Upgrade below to 2.1.7 And above)
 -f  Initialization Admin User password

##Initialize data structure
./Yearning -m

(/var/jenkins_home/workspace/Yearning-go/src/service/migrate.go:31) 
[2020-03-01 18:17:10]  [0.47ms]  INSERT  INTO `core_accounts` (`username`,`password`,`rule`,`department`,`real_name`,`email`) VALUES ('admin','pbkdf2_sha256$120000$KMXHZJuaiVKW$EQ1XMcuApgEb6ysj6l9bOGzaVo+s/AGO13uOcNxpDD0=','admin','DBA','Super administrator','')  
[1 rows affected or returned ] 

(/var/jenkins_home/workspace/Yearning-go/src/service/migrate.go:39) 
[2020-03-01 18:17:10]  [0.80ms]  INSERT  INTO `core_global_configurations` (`authorization`,`ldap`,`message`,`other`,`stmt`,`audit_role`) 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}','{"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,"DDLTimeFieldDefault":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,"EnableSetCollation":false,"EnableSetCharset":false,"SupportCharset":"","SupportCollation":"","CheckIdentifier":false,"MustHaveColumns":"","DDLMultiToSubmit":false,"DDLPrimaryKeyMust":false,"DDLAllowColumnType":false,"DDLImplicitTypeConversion":false,"DMLMinimalRollback":false,"DDLAllowPRINotInt":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}')  
[1 rows affected or returned ] 

(/var/jenkins_home/workspace/Yearning-go/src/service/migrate.go:46) 
[2020-03-01 18:17:10]  [0.62ms]  INSERT  INTO `core_graineds` (`username`,`rule`,`permissions`,`group`) VALUES ('admin','','{"ddl":"1","ddl_source":[],"dml":"1","dml_source":[],"user":"1","base":"1","auditor":[],"query":"1","query_source":[]}',NULL)  
[1 rows affected or returned ] 
//Initialization succeeded!
 //User name: admin
//Password: year? Admin

##Start service
 ./Yearning -s
 Check for updates
 Data updated!

__    __  _____       ___   _____    __   _   _   __   _   _____  
\ \  / / | ____|     /   | |  _  \  |  \ | | | | |  \ | | /  ___| 
 \ \/ /  | |__      / /| | | |_| |  |   \| | | | |   \| | | |     
  \  /   |  __|    / / | | |  _  /  | |\   | | | | |\   | | |  _  
  / /    | |___   / /  | | | | \ \  | | \  | | | | | \  | | |_| | 
 /_/     |_____| /_/   |_| |_|  \_\ |_|  \_| |_| |_|  \_| \_____/  vgolang.ver

Welcome to Yearning
https://yearning.io
____________________________________O/_______
                                    O\
⇨ http server started on [::]:8000
 #Note that the port is open

3, Basic use

. Year recommends using Chrome browser.

In the homepage, you can see the current user number, data source, work order number, query number, personal information, work order submission statistics, high frequency library, and work order trend.

in my work order interface, you can see the historical work order, execution status, execution information, whether to back up, etc..

The work order submission interface is divided into DDL/DML. If the error level is not 0, it is abnormal. If the scheduled execution is not set, the work order will be executed automatically after approval!