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

导读最近在重构公司自动化运维平台的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的架构：
从架构上来说，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=7max-backups=7[inc]backup_host=”xxxx”backup_port=3306backup_user=”goinception”backup_password=”goinception”sql_safe_updates=1lang=”zh-CN”[osc]osc_min_table_size=1024[ghost]ghost_on=trueghost_chunk_size=10000ghost_dml_batch_size=100那么，我们启动goinception
./goInception-config=config/config.toml&启动以后，我们可以看到端口4000已经启动监听了
[root@VM_0_9_centos~]#netstat-lntp|grep4000tcp600:::4000:::*LISTEN31404/./goInception测试goinception可以沿用inception的调用方法，示例如下：
/*–user=root;–password=root;–host=127.0.0.1;–check=1;–port=3306;*/inception_magic_start;usetest;createtablet1(idintprimarykey);inception_magic_commit;那么我们构建一个python脚本来进行测试：
#!/usr/bin/envpython3#-*-coding:utf-8-*-importpymysqlimportprettytableaspttb=pt.PrettyTable()sql=”’/*–user=xucl;–password=xuclxucl123;–host=127.0.0.1;–check=1;–port=3308;*/inception_magic_start;usesbtest;createtablet1(idintprimarykey,c1int,c2int);insertintot1(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]foriincur.description]forrowinresult:tb.add_row(row)print(tb)执行后输出如下：
[root@VM_0_9_centosgoinception]#python3test.py+———-+———+————-+—————–+—————–+—————————————————-+—————+————–+—————+————–+———+————-+|order_id|stage|error_level|stage_status|error_message|sql|affected_rows|sequence|backup_dbname|execute_time|sqlsha1|backup_time|+———-+———+————-+—————–+—————–+—————————————————-+—————+————–+—————+————–+———+————-+|1|CHECKED|0|AuditCompleted|None|usesbtest|0|0_0_00000000|None|0|None|0||2|CHECKED|2|AuditCompleted|表’t1’已存在.|createtablet1(idintprimarykey,c1int,c2int)|0|0_0_00000001|None|0|None|0||3|CHECKED|0|AuditCompleted|None|insertintot1(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
表属性
检查项相关配置项这个表不存在当前库存在表名、列名、索引名的长度不大于64个字节对象名允许字符[a-zA-Z0-9_]check_identifier字符集限制enable_set_charset,support_charset排序规则限制enable_set_collation,support_collation存储引擎限制enable_set_engine,support_engine不能建立为分区表enable_partition_table只能有一个主键表要有主键check_primary_key至少有一个列表必须包含某些列must_have_columns不允许create table as 语法禁止使用Foreign keyenable_foreign_key列属性
检查项相关配置项char长度大于20的时候需要改为varchar（长度可配置）max_char_length不能有重复的列名非数值列不能使用自增不允许无效库名/表名前缀如果是timestamp类型的，则要必须指定默认值。check_timestamp_default如果是datetime类型的，则要必须指定默认值。check_datetime_default不能同时有两个timestamp类型的列，如果是datetime类型，则不能有两个指定DEFAULT CURRENT_TIMESTAMP及ON UPDATE CURRENT_TIMESTAMP的列。check_timestamp_count,check_datetime_count只有timestamp或datatime才能指定on updateon update表达式只能为CURRENT_TIMESTAMP索引属性检查项
检查项相关配置项索引必须要有名字enable_null_index_nameUnique索引必须要以uniq_为前缀check_index_prefix普通索引必须要以idx_为前缀check_index_prefix索引的列数不能超过5个max_key_parts主键索引列数限制max_primary_key_parts主键列必须使用int或bigintenable_pk_columns_only_int建索引时，指定的列必须存在。索引中的列，不能重复BLOB列不能建做KEY索引长度不能超过767或3072,由实际mysql的innodb_large_prefix决定索引名不能是PRIMARY索引名不能重复默认值
检查项相关配置项BLOB/TEXT类型的列，不能有非NULL的默认值enable_blob_not_null如果默认值为NULL，但列类型为NOT NULL，或者是主键列，或者定义为自增列，则报错。JSON列不能设置默认值。每个列都需要定义默认值，除了自增列/主键/JSON/计算列/以及大字段列之外check_column_default_value自增列
检查项相关配置项建表时，自增列初始值为1check_autoincrement_init_value如果自增列的名字不为id，说明可能是有意义的，不建议check_autoincrement_name自增列类型必须为int或bigintcheck_autoincrement_datatype自增列需要设置无符号enable_autoincrement_unsignedALTER
检查项相关配置项表是否存在同一个表的多个ALTER建议合并merge_alter_table列是否存在表属性只支持对存储引擎、表注释、自增值及默认字符集的修改操作。是否允许change column操作enable_change_column是否允许列顺序变更check_column_position_change是否允许列类型变更check_column_type_changeDML
INSERT
检查项相关配置项表是否存在列必须存在不为null的列，如果插入的值是null，报错INSERT SELECT
检查项相关配置项涉及的所有库/表/字段必须存在必须指定插入列表，也就是要写入哪些列，如insert into t (id,id2) select …check_insert_field是否允许select *enable_select_star必须有where条件check_dml_where不能有order by rand子句enable_orderby_rand使用explain获取预估行数或select count获取真实行数调用选项real_row_count,explain_ruleUPDATE/DELETE
检查项相关配置项表必须存在必须有where条件check_dml_where不能有order by语句check_dml_orderby影响行数大于10000条，则报警（数目可配置）max_update_rows对WHERE条件这个表达式做简单检查，具体包括什么不一一指定多表更新、删除时，每个表及涉及字段必须要存在update 多表关联时,如果set未指定表前缀,自动判断多表时判断未指明表前缀的列是否有歧义update多表关联时,如果set了多个表的字段,同样支持回滚语句生成使用explain获取预估行数或select count获取真实行数调用选项realRowCount,explain_rulemysql版本在5.6之前时,自动将语句转换为select做explain设置数据库sql_safe_updates参数sql_safe_updates多表关联时,审核join语句是否包含on子句check_dml_where条件中的列是否存在隐式类型转换check_implicit_type_conversionupdate set 判断set使用了逗号还是and分隔四、使用感受简单测试了一下goinception，整体体验下来感觉还是很不错的，相比于之前的inception来说，goinception有如下几个优点：
安装部署非常简单，官方直接提供了二进制包
集成了gh-ost无需另外安装，同时也提供pt-osc方式执行DDL
提供了基于表大小的ALTER执行选项，并且可配置大小
支持关联更新，inception本身是不支持的
提供了更加丰富的审核规则选项，并且是基于MySQL5.7的，相比于inception来说支持度更好
长期稳定更新，从github上的commit记录看作者还是更新得很勤奋的
更快的备份功能，之前使用inception的时候经常会碰到需要备份的binlog过大，网络超时的现象（从文档上看goinception使用的批量备份，据说速度能快很多）
五、系统集成到这里，我们已经基本上选择goinception作为我们的审核引擎了，那么如何集成到现有的自动化系统中是个问题。
先说一下我们公司的情况，我们公司有流程中心，也就是开发会在上面选择库，提交SQL，DBA收到工单以后人工审核，手工执行。
在审核过程中，对于DBA来说，肉眼审核也太过于辛酸了，手工执行一不小心就是背黑锅。所以，基于goinception，我这边提供了几个restful的接口，分别如下：
db信息接口，根据用户权限，返回该用户所能看到的dbname
SQL检查接口，用户提交SQL，调用goinception进行审核，审核通过以后到DBA
SQL执行接口，DBA选择执行的实例，调用goinception进行SQL执行，执行完成以后通知开发工单完成
开发查看工单，可以下载对应的回滚SQL
这样一来，DBA的双手可以大大地解放了，不仅提高了工作效率，并且能够准确无误地执行SQL，减少背黑锅的概率，还提供了回滚SQL的功能，假如开发发现SQL提交有误可以及时查看回滚SQL进行数据修复。
全文完。
Enjoy MySQL:)
叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧