DBA的福音，SQL审核利器-goinception

ゞ浴缸里的玫瑰 2022-12-01 15:45 384阅读 0赞

# 导读    #

> 最近在重构公司自动化运维平台的SQL审核部分，于是调研了一下goinception。在之前的公司审核引擎用的是去哪儿网开源的inception，由于某些原因，inception长久不更新并且已经不再开源，所以就把目光转向了goinception。

### 一、goinception简介 ###

使用过inception的人对SQL审核这块获取都比较熟悉，作为DBA，审核SQL是日常工作中的很重要的一块内容，审核好SQL对于后期项目以及数据库维护上起着至关重要的作用，好比一座大厦没有坚实的地基支撑，也就无法长期屹立不倒。

goInception是一个集审核、执行、备份及生成回滚语句于一身的MySQL运维工具， 通过对执行SQL的语法解析，返回基于自定义规则的审核结果，并提供执行和备份及生成回滚语句的功能。

goinception的github地址：https://github.com/hanchuanchuan/goInception  
相关的详细文档：https://hanchuanchuan.github.io/goInception/

goinception的架构：

![format_png][]

从架构上来说，goinception简直跟inception一模一样，SQL提交到goinception，goinception连接到线上MySQL进行审核。执行的时候也是连接到线上MySQL进行执行，goinception提供了备份、回滚的功能，意思就是能够监听执行期间的binlog，基于binlog生成反向的回滚SQL。

### 二、安装测试 ###

废话不多说，咱们直接安装测试一把。  
**下载、安装**  
官方提供了二进制包，简直安装部署的一大福利。  
下载地址：https://github.com/hanchuanchuan/goInception/releases  
下载好对应版本的goinception，直接解压即可，解压完成以后在`config/config.toml.default`有一个默认的配置文件，大概意思也写得比较清楚了，那我修改的部分已经写在下面的配置文件中了，其他部分我都没有修改

# 日志文件
    [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

那么，我们启动goinception

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

启动以后，我们可以看到端口4000已经启动监听了

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

**测试**  
goinception可以沿用inception的调用方法，示例如下：

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

那么我们构建一个python脚本来进行测试：

#!/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(1,1,1);
    inception_magic_commit;'''
    
    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)

执行后输出如下：

[root@VM_0_9_centos goinception]# python3 test.py 
    +----------+---------+-------------+-----------------+-----------------+----------------------------------------------------+---------------+--------------+---------------+--------------+---------+-------------+
    | order_id |  stage  | error_level |   stage_status  |  error_message  |                        sql                         | affected_rows |   sequence   | backup_dbname | execute_time | sqlsha1 | backup_time |
    +----------+---------+-------------+-----------------+-----------------+----------------------------------------------------+---------------+--------------+---------------+--------------+---------+-------------+
    |    1     | CHECKED |      0      | Audit Completed |       None      |                     use sbtest                     |       0       | 0_0_00000000 |      None     |      0       |   None  |      0      |
    |    2     | CHECKED |      2      | Audit Completed | 表 't1' 已存在. | create table t1(id int primary key,c1 int, c2 int) |       0       | 0_0_00000001 |      None     |      0       |   None  |      0      |
    |    3     | CHECKED |      0      | Audit Completed |       None      |       insert into t1(id,c1,c2) values(1,1,1)       |       1       | 0_0_00000002 |      None     |      0       |   None  |      0      |
    +----------+---------+-------------+-----------------+-----------------+----------------------------------------------------+---------------+--------------+---------------+--------------+---------+-------------+

可以看到审核结果还是非常清晰的  
那如果需要执行的话怎么做呢？我们只需要修改示例中的头部，将`--check=1`改成`--execute=1`即可

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

那你如果想备份的话，添加选项`--backup=1`即可

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

### 三、审核规则 ###

goinception的审核规则可以见文档：https://hanchuanchuan.github.io/goInception/rules.html  
我捡了一些我认为比较重要的审核规则，列成表格如下：  
**create table**

**表属性**

<table> 
 <thead> 
  <tr> 
   <th>检查项</th> 
   <th>相关配置项</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>这个表不存在</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>当前库存在</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>表名、列名、索引名的长度不大于64个字节</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>对象名允许字符[a-zA-Z0-9_]</td> 
   <td>check_identifier</td> 
  </tr> 
  <tr> 
   <td>字符集限制</td> 
   <td>enable_set_charset,support_charset</td> 
  </tr> 
  <tr> 
   <td>排序规则限制</td> 
   <td>enable_set_collation,support_collation</td> 
  </tr> 
  <tr> 
   <td>存储引擎限制</td> 
   <td>enable_set_engine,support_engine</td> 
  </tr> 
  <tr> 
   <td>不能建立为分区表</td> 
   <td>enable_partition_table</td> 
  </tr> 
  <tr> 
   <td>只能有一个主键</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>表要有主键</td> 
   <td>check_primary_key</td> 
  </tr> 
  <tr> 
   <td>至少有一个列</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>表必须包含某些列</td> 
   <td>must_have_columns</td> 
  </tr> 
  <tr> 
   <td>不允许create table as 语法</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>禁止使用Foreign key</td> 
   <td>enable_foreign_key</td> 
  </tr> 
 </tbody> 
</table>

**列属性**

<table> 
 <thead> 
  <tr> 
   <th>检查项</th> 
   <th>相关配置项</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>char长度大于20的时候需要改为varchar（长度可配置）</td> 
   <td>max_char_length</td> 
  </tr> 
  <tr> 
   <td>不能有重复的列名</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>非数值列不能使用自增</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>不允许无效库名/表名前缀</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>如果是timestamp类型的，则要必须指定默认值。</td> 
   <td>check_timestamp_default</td> 
  </tr> 
  <tr> 
   <td>如果是datetime类型的，则要必须指定默认值。</td> 
   <td>check_datetime_default</td> 
  </tr> 
  <tr> 
   <td>不能同时有两个timestamp类型的列，如果是datetime类型，则不能有两个指定DEFAULT CURRENT_TIMESTAMP及ON UPDATE CURRENT_TIMESTAMP的列。</td> 
   <td>check_timestamp_count,check_datetime_count</td> 
  </tr> 
  <tr> 
   <td>只有timestamp或datatime才能指定on update</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>on update表达式只能为CURRENT_TIMESTAMP</td> 
   <td><br></td> 
  </tr> 
 </tbody> 
</table>

**索引属性检查项**

<table> 
 <thead> 
  <tr> 
   <th>检查项</th> 
   <th>相关配置项</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>索引必须要有名字</td> 
   <td>enable_null_index_name</td> 
  </tr> 
  <tr> 
   <td>Unique索引必须要以uniq_为前缀</td> 
   <td>check_index_prefix</td> 
  </tr> 
  <tr> 
   <td>普通索引必须要以idx_为前缀</td> 
   <td>check_index_prefix</td> 
  </tr> 
  <tr> 
   <td>索引的列数不能超过5个</td> 
   <td>max_key_parts</td> 
  </tr> 
  <tr> 
   <td>主键索引列数限制</td> 
   <td>max_primary_key_parts</td> 
  </tr> 
  <tr> 
   <td>主键列必须使用int或bigint</td> 
   <td>enable_pk_columns_only_int</td> 
  </tr> 
  <tr> 
   <td>建索引时，指定的列必须存在。</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>索引中的列，不能重复</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>BLOB列不能建做KEY</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>索引长度不能超过767或3072,由实际mysql的innodb_large_prefix决定</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>索引名不能是PRIMARY</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>索引名不能重复</td> 
   <td><br></td> 
  </tr> 
 </tbody> 
</table>

**默认值**

<table> 
 <thead> 
  <tr> 
   <th>检查项</th> 
   <th>相关配置项</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>BLOB/TEXT类型的列，不能有非NULL的默认值</td> 
   <td>enable_blob_not_null</td> 
  </tr> 
  <tr> 
   <td>如果默认值为NULL，但列类型为NOT NULL，或者是主键列，或者定义为自增列，则报错。</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>JSON列不能设置默认值。</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>每个列都需要定义默认值，除了自增列/主键/JSON/计算列/以及大字段列之外</td> 
   <td>check_column_default_value</td> 
  </tr> 
 </tbody> 
</table>

**自增列**

<table> 
 <thead> 
  <tr> 
   <th>检查项</th> 
   <th>相关配置项</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>建表时，自增列初始值为1</td> 
   <td>check_autoincrement_init_value</td> 
  </tr> 
  <tr> 
   <td>如果自增列的名字不为id，说明可能是有意义的，不建议</td> 
   <td>check_autoincrement_name</td> 
  </tr> 
  <tr> 
   <td>自增列类型必须为int或bigint</td> 
   <td>check_autoincrement_datatype</td> 
  </tr> 
  <tr> 
   <td>自增列需要设置无符号</td> 
   <td>enable_autoincrement_unsigned</td> 
  </tr> 
 </tbody> 
</table>

**ALTER**

<table> 
 <thead> 
  <tr> 
   <th>检查项</th> 
   <th>相关配置项</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>表是否存在</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>同一个表的多个ALTER建议合并</td> 
   <td>merge_alter_table</td> 
  </tr> 
  <tr> 
   <td>列是否存在</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>表属性只支持对存储引擎、表注释、自增值及默认字符集的修改操作。</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>是否允许change column操作</td> 
   <td>enable_change_column</td> 
  </tr> 
  <tr> 
   <td>是否允许列顺序变更</td> 
   <td>check_column_position_change</td> 
  </tr> 
  <tr> 
   <td>是否允许列类型变更</td> 
   <td>check_column_type_change</td> 
  </tr> 
 </tbody> 
</table>

**DML**

**INSERT**

<table> 
 <thead> 
  <tr> 
   <th>检查项</th> 
   <th>相关配置项</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>表是否存在</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>列必须存在</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>不为null的列，如果插入的值是null，报错</td> 
   <td><br></td> 
  </tr> 
 </tbody> 
</table>

**INSERT SELECT**

<table> 
 <thead> 
  <tr> 
   <th>检查项</th> 
   <th>相关配置项</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>涉及的所有库/表/字段必须存在</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>必须指定插入列表，也就是要写入哪些列，如insert into t (id,id2) select …</td> 
   <td>check_insert_field</td> 
  </tr> 
  <tr> 
   <td>是否允许select *</td> 
   <td>enable_select_star</td> 
  </tr> 
  <tr> 
   <td>必须有where条件</td> 
   <td>check_dml_where</td> 
  </tr> 
  <tr> 
   <td>不能有order by rand子句</td> 
   <td>enable_orderby_rand</td> 
  </tr> 
  <tr> 
   <td>使用explain获取预估行数或select count获取真实行数</td> 
   <td>调用选项<code>real_row_count</code>,explain_rule</td> 
  </tr> 
 </tbody> 
</table>

**UPDATE/DELETE**

<table> 
 <thead> 
  <tr> 
   <th>检查项</th> 
   <th>相关配置项</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>表必须存在</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>必须有where条件</td> 
   <td>check_dml_where</td> 
  </tr> 
  <tr> 
   <td>不能有order by语句</td> 
   <td>check_dml_orderby</td> 
  </tr> 
  <tr> 
   <td>影响行数大于10000条，则报警（数目可配置）</td> 
   <td>max_update_rows</td> 
  </tr> 
  <tr> 
   <td>对WHERE条件这个表达式做简单检查，具体包括什么不一一指定</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>多表更新、删除时，每个表及涉及字段必须要存在</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>update 多表关联时,如果set未指定表前缀,自动判断</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>多表时判断未指明表前缀的列是否有歧义</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>update多表关联时,如果set了多个表的字段,同样支持回滚语句生成</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>使用explain获取预估行数或select count获取真实行数</td> 
   <td>调用选项<code>realRowCount</code>,explain_rule</td> 
  </tr> 
  <tr> 
   <td>mysql版本在5.6之前时,自动将语句转换为select做explain</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td>设置数据库<code>sql_safe_updates</code>参数</td> 
   <td>sql_safe_updates</td> 
  </tr> 
  <tr> 
   <td>多表关联时,审核join语句是否包含on子句</td> 
   <td>check_dml_where</td> 
  </tr> 
  <tr> 
   <td>条件中的列是否存在隐式类型转换</td> 
   <td>check_implicit_type_conversion</td> 
  </tr> 
  <tr> 
   <td>update set 判断set使用了逗号还是and分隔</td> 
   <td><br></td> 
  </tr> 
 </tbody> 
</table>

### 四、使用感受 ###

简单测试了一下goinception，整体体验下来感觉还是很不错的，相比于之前的inception来说，goinception有如下几个优点：

1.  安装部署非常简单，官方直接提供了二进制包
2.  集成了gh-ost无需另外安装，同时也提供pt-osc方式执行DDL
3.  提供了基于表大小的ALTER执行选项，并且可配置大小
4.  支持关联更新，inception本身是不支持的
5.  提供了更加丰富的审核规则选项，并且是基于MySQL5.7的，相比于inception来说支持度更好
6.  长期稳定更新，从github上的commit记录看作者还是更新得很勤奋的
7.  更快的备份功能，之前使用inception的时候经常会碰到需要备份的binlog过大，网络超时的现象（从文档上看goinception使用的批量备份，据说速度能快很多）

### 五、系统集成 ###

到这里，我们已经基本上选择goinception作为我们的审核引擎了，那么如何集成到现有的自动化系统中是个问题。

先说一下我们公司的情况，我们公司有流程中心，也就是开发会在上面选择库，提交SQL，DBA收到工单以后人工审核，手工执行。

在审核过程中，对于DBA来说，肉眼审核也太过于辛酸了，手工执行一不小心就是背黑锅。所以，基于goinception，我这边提供了几个restful的接口，分别如下：

1.  db信息接口，根据用户权限，返回该用户所能看到的dbname
2.  SQL检查接口，用户提交SQL，调用goinception进行审核，审核通过以后到DBA
3.  SQL执行接口，DBA选择执行的实例，调用goinception进行SQL执行，执行完成以后通知开发工单完成
4.  开发查看工单，可以下载对应的回滚SQL

这样一来，DBA的双手可以大大地解放了，不仅提高了工作效率，并且能够准确无误地执行SQL，减少背黑锅的概率，还提供了回滚SQL的功能，假如开发发现SQL提交有误可以及时查看回滚SQL进行数据修复。

![format_png 1][]

全文完。

Enjoy MySQL :)

--------------------

叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧

![format_png 2][]

[format_png]: /images/20221123/b3ebe5fb3a674758bb0485651e7960ef.png
[format_png 1]: /images/20221123/0808eb4936554cd98e1608a2e34a6e30.png
[format_png 2]: /images/20221123/9af8fecb91b94248b1d228a3388e99ed.png