Master SQL Auditing with Yearning: Step‑by‑Step Deployment and Practical DBA Guide

If you want to become a practical DBA, this article introduces Yearning, an open‑source MySQL SQL audit platform, and provides step‑by‑step deployment and usage instructions suitable for beginners and experienced DBAs.

What is Yearning?

Yearning is a Go + Vue based open‑source MySQL SQL audit platform. It supports automatic review, an approval workflow, execution after approval, automatic rollback generation, query audit, rollback management, LDAP login, and DingTalk/email notifications.

In a DevOps pipeline it ties together code, automation, review, and security: developers submit SQL tickets, DBAs review and execute them, and the system records actions and automatically provides rollbacks, ensuring transparency and safety.

Core Components

Yearning (Audit Center) : handles SQL tickets – submit, approve, execute, rollback.

MySQL : stores Yearning’s data and also runs the Yearning service itself.

Target Database : the MySQL instance (development, test, or production) where the actual SQL runs.

DataGrip : front‑end tool developers use to write SQL, submit tickets, and view results.

Docker Compose (optional) : one‑click deployment of MySQL + Yearning.

Deploy Yearning

Method 1: Bare‑metal deployment on aimalinux

# 1. Install MySQL and create the database
mysql -uroot -p
CREATE DATABASE Yearning CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 2. Download Yearning binary
wget https://github.com/cookieY/Yearning/releases/download/v3.1.0/Yearning-v3.1.0-linux-amd64.zip
unzip Yearning-v3.1.0-linux-amd64.zip
cd Yearning-v3.1.0-linux-amd64

# 3. Edit conf.toml (core configuration)
vim conf.toml
# Example content:
[ Mysql ]
Host = "127.0.0.1"
Port = "3306"
User = "root"
Password = "your_password"
Db = "Yearning"

[ General ]
SecretKey = "16_char_random_string"
# SecretKey can be set only once; changing it later will break decryption.

# 4. Initialize and start
./Yearning install   # creates all backend tables
./Yearning run       # starts HTTP service on port 8000 (admin/Yearning_admin)

Front‑end URL:

http://127.0.0.1:8000

Method 2: Docker Compose quick deployment

Create a

docker-compose.yml

file with the following content:

version: '3'
services:
  mysql:
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: yourpass
      MYSQL_DATABASE: Yearning
    ports:
      - "3306:3306"
    volumes:
      - ./mysql-data:/var/lib/mysql

  yearning:
    image: easysoft/yearning:latest
    depends_on:
      - mysql
    environment:
      DB_HOST: mysql
      DB_PORT: 3306
      DB_NAME: Yearning
      DB_USER: root
      DB_PASSWORD: yourpass
      SECRET_KEY: abcdefgh12345678
      IS_DOCKER: is_docker
    ports:
      - "18000:8000"
    command: /bin/bash -c "./Yearning install && ./Yearning run"
    volumes:
      - ./yearning-data:/data

Start the services:

docker-compose up -d

Yearning front‑end URL:

http://localhost:18000

Multi‑environment configuration isolates environments efficiently.

DataGrip: Submitting an SQL Ticket

Log in to the Yearning front‑end (DataGrip can open the browser automatically).

Add a “Data Source” with the target MySQL connection details and appropriate read/write permissions.

Create an audit‑process template (at least one “review” step is required to generate rollback SQL).

Configure permission groups (DML, DDL, QUERY).

Create a user, assign it to the permission groups, and use that user in DataGrip.

Submit an SQL ticket (e.g., DELETE, INSERT, UPDATE) from DataGrip.

After the reviewer approves, Yearning executes the SQL and records a rollback statement.

View execution status and screenshots in DataGrip.

Best Practices & Tips for Beginners

SQL syntax support : Yearning supports ~99% of common MySQL statements; complex multi‑table, cross‑database DML, or stored‑procedure rollbacks may not be supported.

SecretKey security : Set the SecretKey once and never change it, otherwise encrypted passwords become unreadable.

Include an approval step : Without a review stage, rollback cannot be generated.

Rollback example :

DELETE FROM orders WHERE created_at < '2024-01-01';

generates automatically:

INSERT INTO orders_backup SELECT * FROM orders WHERE created_at < '2024-01-01';

Summary

Yearning is a concise, efficient, traceable, and rollback‑capable SQL audit tool for DevOps environments. When used with DataGrip, developers can submit tickets, DBAs can review and execute them, and the system automatically generates rollback scripts and audit logs, making it suitable for both newcomers and seasoned DBAs.