A set of audit, execution, backup and generate rollback statements in one MySQL automated operation and maintenance tool

Time:2021-1-25

A set of audit, execution, backup and generate rollback statements in one MySQL automated operation and maintenance tool

Before that, brother migrant workers also introduced an open source SQL Management Tool:Automatic completion, rollback! Introduction of a visual SQL diagnostic tool

Today, the migrant worker brother recommends another SQL audit tool: goinception.

Introduction to goinception

Goinception is a MySQL operation and maintenance tool that integrates auditing, executing, backing up and generating rollback statements. Through parsing the syntax of executing SQL, goinception returns the auditing results based on user-defined rules, and provides the functions of executing, backing up and generating rollback statements.

GitHub address:https://github.com/hanchuanch…

file:https://hanchuanchuan.github….

Goinception architecture

A set of audit, execution, backup and generate rollback statements in one MySQL automated operation and maintenance tool

Goinception installation

The official offers several installation methods, as follows.

  • 1. Source code installation

Source code installation requires go v1.2 or above, and use go mod for dependency management.

[[email protected] ~]# git clone https://github.com/hanchuanchuan/goInception.git
[[email protected] ~]# cd goInception
[[email protected] ~]# make parser
[[email protected] ~]# go build -o goInception tidb-server/main.go
  • 2. Docker mode
[[email protected] ~]# docker pull hanchuanchuan/goinception
  • 3. Binary installation (recommended)

Direct to the official address:https://github.com/hanchuanch…After downloading, you can unzip and run it directly.

[[email protected] ~]# mkdir goinception
[[email protected] ~]# tar zxf goInception-linux-amd64-v1.2.3.tar.gz -C ./goinception/
[[email protected] ~]# cd goinception/
[[email protected] goinception]# ll
total 38476
drwxr-xr-x 2 root root        33 Aug 30 03:48 config
-rwxr-xr-x 1  501 games 39399424 May 22 07:45 goInception

After decompression, you will see a default configuration file in the config directory config.toml.default , you can modify it according to the actual situation.

Goinception uses tidb source code reconstruction, so some parameters can refer to tidb related documents

config.toml The file consists of several parts, which are the outermost configuration, such as host, port, etc., and the groups, such as [Inc], [log], etc. Example (this example is for demonstration only) config.toml File structure, please refer to the following for detailed parameters:https://github.com/hanchuanch…

host = "0.0.0.0"
port = 4000
path = "/tmp/tidb"
[log]
#Log parameters
level = "info"
format = "text"
[log.file]
#Log file parameters
filename = ""
max-size = 300
[inc]
#Audit options
enable_nullable = true
enable_drop_table = false
check_table_comment = false
check_column_comment = false
#Wait
[osc]
#PT OSC parameter
osc_on = false
osc_min_table_size = 16
[ghost]
#GH OST parameter
ghost_allow_on_master = true

After the configuration modification is completed, it can be started normally.

[[email protected] goinception]# ./goInception -config=config/config.toml
[[email protected] ~]# netstat -lntp|grep 4000
tcp6   0   0 :::4000    :::*    LISTEN    1250/./goInception 

Use examples

/*--user=root;--password=root;--host=127.0.0.1;--check=1;--port=3306;*/
inception_magic_start;
use test;
create table t1(id int primary key);
inception_magic_commit;

Other introductions

1. Result information

There are two kinds of information returned to users,

  • One is that there are errors in the basic information submitted to goinception, such as incomplete or incorrect source information. In this case, an exception is directly reported, including the error code and error information. It is the same as the exception of MySQL server, and can be handled normally outside.
  • Second, if there is no problem above, the client will be informed of the inspection result in the form of result set. It is consistent with MySQL native result set. In the returned result set, each row of data is a submitted SQL statement. In goinception, all the submitted statement blocks are separated one by one and returned in the result set. For each statement, what problems or status are in the result set.

Note: if there is a syntax error in the statement, you can’t continue, because goinception can’t separate the remaining statements. At this time, the previously checked multiple rows and multiple result sets are returned, and the following error statements are returned in one row. Of course, the error message of this one is a syntax error.

2. With backup function

It comes with backup function. First, it is configured when the service starts config.toml (put in the [Inc] section)

A set of audit, execution, backup and generate rollback statements in one MySQL automated operation and maintenance tool

When executing SQL, add the option of — backup = true or — backup = 1.

3. Audit rules

For more information about audit rules and options, please refer to:https://hanchuanchuan.github….

Contrast perception

1. Function comparison

A set of audit, execution, backup and generate rollback statements in one MySQL automated operation and maintenance tool

2. Speed

A set of audit, execution, backup and generate rollback statements in one MySQL automated operation and maintenance tool

3. Use

A set of audit, execution, backup and generate rollback statements in one MySQL automated operation and maintenance tool

This kind of tools not only liberate the hands of DBAs to a certain extent, but also avoid errors to a great extent, so as to ensure the correct rate of SQL execution. At the same time, it also provides SQL rollback function, which can roll back when errors occur, so as to ensure data security and integrity.

A set of audit, execution, backup and generate rollback statements in one MySQL automated operation and maintenance tool