Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to understand SQL audit weapon goinception", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's train of thought slowly in depth, together to study and learn "how to understand SQL audit weapon goinception"!

A brief introduction to goinception

People who have used inception are familiar with SQL audit this acquisition, as DBA, audit SQL is a very important part of daily work, audit SQL for later projects and database maintenance plays a vital role, such as a building without a solid foundation support, it can not stand for a long time.

GoInception is a MySQL operation and maintenance tool that integrates audit, execution, backup and generation of rollback statements. By parsing the syntax of executing SQL, it returns the audit results based on custom rules, and provides the functions of executing and backing up and generating rollback statements.

Architecturally, goinception is exactly like inception, where SQL is submitted to goinception,goinception and connected to online MySQL for review. During execution, it is also connected to the online MySQL for execution. Goinception provides the function of backup and rollback, which means that it can listen to the binlog during execution and generate a reverse rollback SQL based on binlog.

2. Installation testing

Needless to say, let's install and test it directly.

Download, install

Officials have provided binary packages, which are a big benefit of installation and deployment.

Download address: https://github.com/hanchuanchuan/goInception/releases

Download the corresponding version of goinception and decompress it directly. After the decompression is completed, there is a default configuration file in config/config.toml.default, and the general meaning is relatively clear. Then my modified part has been written in the configuration file below, and I have not modified any other parts.

# Log file [log.file] filename = "/ opt/goinception/logs/goinception.log" max-days = 7 max-backups = 7 [inc] backup_host = "xxxx" backup_port = 3306 backup_user = "goinception" backup_password = "goinception" sql_safe_updates = 1 lang = "zh-CN" [osc] osc_min_table_size = 1024 [ghost] ghost_on = true ghost_chunk_size = 10000 ghost_dml_batch_size = 100

So, let's start goinception.

. / goInception-config=config/config.toml &

After booting, we can see that port 4000 has started listening.

[root@VM_0_9_centos ~] # netstat-lntp | grep 4000 tcp6 00: 4000: * LISTEN 31404/./goInception

test

Goinception can follow the calling method of inception. Examples are as follows:

/ *-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

So let's build a python script to test:

#! / usr/bin/env python3 #-*-coding:utf-8-*-import pymysql import prettytable as pt tb = pt.PrettyTable () sql =''/ *-- user=xucl;--password=xuclxucl123;--host=127.0.0.1;--check=1;--port=3308;*/ inception_magic_start; use sbtest; create table T1 (id int primary key,c1 int, c2 int); insert into T1 (id,c1,c2) values '' conn = pymysql.connect (host='127.0.0.1', user='', passwd='', db='', port=4000, charset= "utf8mb4") cur = conn.cursor () ret = cur.execute (sql) result = cur.fetchall () cur.close () conn.close () tb.field_names = [I [0] for i in cur.description] for row in result: tb.add_row (row) print (tb)

The output after execution is as follows:

[root@VM_0_9_centos goinception] # python3 test.py +-+- -- + |-+- -+-+ | order_id | stage | error_ |-+- -+ | order_id | stage | error_ |-+ -+ | order_id | stage | error_level |-+- -+-+ | order_id | stage | error__status |-+ | order_id | stage | error _ message | sql | affected_rows | sequence | backup_dbname | execute_time | sqlsha1 | backup_time | +- -+- -+-+ | 1 | CHECKED | 0 | Audit Completed | None | use sbtest | 0 | 0,00000000 | None | 0 | None | 0 | | 2 | CHECKED | 2 | Audit Completed | Table'T1 'already exists. | | create table T1 (id int primary key,c1 int, c2 int) | 0 | 0 | 00000001 | None | 0 | None | 0 | 3 | CHECKED | 0 | Audit Completed | None | insert into T1 (id,c1,c2) values (1) 1) | 1 | 0,000000002 | None | 0 | None | 0 | +- -+- -+

You can see that the audit results are still very clear.

So what to do if it needs to be carried out? We just need to modify the header in the example and change-- check=1 to-- execute=1.

/ *-- user=xucl;--password=xuclxucl123;--host=127.0.0.1;--execute=1;--port=3308;*/

Then if you want to back up, add the option-- backup=1.

/ *-- user=xucl;--password=xuclxucl123;--host=127.0.0.1;--execute=1;--backup=1;--port=3308;*/

III. Audit rules

The audit rules of goinception can be found in the document: https://hanchuanchuan.github.io/goInception/rules.html

I have picked up some audit rules that I think are more important, and they are listed as follows:

Create table

Table attribute

Check item related configuration items this table does not exist in current inventory in the table name, column name, index name length of no more than 64 bytes object name allowed character [a-zA-Z0-9 _] check_identifier character set limit enable_set_charset,support_charset collation limit enable_set_collation,support_collation storage engine limit enable_set_engine Support_engine cannot be established as a partitioned table enable_partition_table can only have one primary key table must have a primary key check_primary_key at least one list must contain certain columns must_have_columns does not allow create table as syntax to prohibit the use of Foreign keyenable_foreign_key

Column attribute

Check item-related configuration item char length greater than 20 needs to be changed to varchar (length configurable) max_char_length cannot have duplicate column names non-numeric columns cannot use self-increment does not allow invalid library name / table name prefix if it is a timestamp type, you must specify a default value. If check_timestamp_default is of type datetime, you must specify a default value. Check_datetime_default cannot have two columns of type timestamp at the same time, and if it is of type datetime, it cannot have two columns that specify DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP. Check_timestamp_count,check_datetime_count only timestamp or datatime can specify that the on updateon update expression can only be CURRENT_TIMESTAMP

Index property check item

Check item related configuration item index must have a name enable_null_index_nameUnique index must be prefixed with uniq_ check_index_prefix ordinary index must be prefixed with idx_ the number of columns of check_index_prefix index must not exceed 5 max_key_parts primary key index column limit max_primary_key_parts primary key column must be indexed using int or bigintenable_pk_columns_only_int, the specified column must exist. Columns in the index cannot be repeated. BLOB columns cannot be built as KEY index lengths cannot exceed 767 or 3072. It is decided by the innodb_large_prefix of the actual mysql that the index name cannot be PRIMARY index name cannot be duplicated.

Default value

A column of type BLOB/TEXT related to the check item cannot have a default value of enable_blob_not_null that is not NULL. If the default value is NULL, but the column type is NOT NULL, or it is a primary key column, or it is defined as a self-incrementing column, an error is reported. The default value cannot be set for JSON columns. Default values need to be defined for each column, except for self-incrementing columns / primary key / JSON/ computed columns / and large field columns check_column_default_value

Self-increasing column

If the name of the auto-increment column is not id, it may be meaningful. It is not recommended that the check_autoincrement_name auto-increment column type must be int or bigintcheck_autoincrement_datatype self-increment column should be set to unsigned enable_autoincrement_unsigned.

ALTER

Check whether there are multiple ALTER in the same table. It is recommended to merge merge_alter_table columns. Table attributes only support modifications to the storage engine, table comments, self-increment, and default character sets. Whether to allow change column to operate whether enable_change_column allows column order change check_column_position_change whether column type change check_column_type_change

DML

INSERT

Check whether there is a column in the related configuration item table. There must be a column that is not null. If the inserted value is null, an error will be reported.

INSERT SELECT

All libraries / tables / fields involved in the relevant configuration items of the check item must exist. You must specify the insert list, that is, which columns to write to, such as insert into t (id,id2) select... Whether check_insert_field allows select * enable_select_star must have where condition check_dml_where cannot have orderby rand clause enable_orderby_rand use explain to get the estimated number of rows or select count to get the true execution number call option real_row_count,explain_rule

UPDATE/DELETE

Check item related configuration item table must exist where condition check_dml_where cannot have orderby statement check_dml_orderby affects more than 10000 rows, alarm (number configurable) max_update_rows does a simple check on the expression of WHERE condition, including what does not specify multiple tables to update or delete When there must be a update multi-table association for each table and related fields, if set does not specify a table prefix, automatically judge whether the columns that do not specify the table prefix are ambiguous update multi-table association, if set has multiple table fields, also support the rollback statement generation to use explain to get the estimated number of rows or select count to get the true number of executives. call option realRowCount,explain_rulemysql before 5.6 Automatically convert statement to select when setting database sql_safe_updates parameter sql_safe_updates multi-table association with explain, audit whether join statement contains on clause columns in check_dml_where condition have implicit type conversion check_implicit_type_conversionupdate set determines whether set uses comma or and separation

Fourth, the feeling of using

After a brief test of goinception, it feels good to experience it as a whole. Compared with the previous inception, goinception has the following advantages:

Installation and deployment is very simple, and the official binary package is provided directly.

Integrated gh-ost does not need to be installed separately, and it also provides pt-osc way to execute DDL

ALTER execution options based on table size are provided and the size is configurable

Associated updates are supported. Inception itself does not support it.

Provides a richer choice of audit rules, and is based on MySQL5.7, with better support than inception

Long-term stable update, from the commit records on github, the author is still very diligent.

Faster backup function, before using inception, we often encounter the phenomenon that the binlog that needs to be backed up is too large and the network timeout (judging from the documents, the batch backup used by goinception is said to be much faster)

V. system integration

At this point, we have basically chosen goinception as our audit engine, so how to integrate into the existing automation system is a problem.

First talk about the situation of our company, our company has a process center, that is, the developer will select the library above, submit the SQL,DBA to receive the work order after manual review, manual execution.

In the audit process, for DBA, the naked eye audit is too bitter, manual implementation is not careful is to take the blame. Therefore, based on goinception, I provide several APIs for restful, which are as follows:

Db information API, which returns the dbname that the user can see according to the user's permissions.

SQL check API. Users submit SQL, call goinception for audit, and then go to DBA after verification.

SQL execution API. DBA selects the instance to be executed, calls goinception for SQL execution, and notifies the developer that the work order is completed after the execution is completed.

Developers can view the work order and download the corresponding rollback SQL.

Thank you for your reading, the above is the content of "how to understand SQL audit weapon goinception". After the study of this article, I believe you have a deeper understanding of how to understand SQL audit weapon goinception, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.