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 = 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_ | ------+---------------+--------------+---------------+--------------+---------+-------------+|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_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
表属性
| 检查项 | 相关配置项 |
|---|---|
| 这个表不存在 | |
| 当前库存在 | |
| 表名、列名、索引名的长度不大于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 key | enable_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 update | |
| on update表达式只能为CURRENT_TIMESTAMP |
索引属性检查项
| 检查项 | 相关配置项 |
|---|---|
| 索引必须要有名字 | enable_null_index_name |
| Unique索引必须要以uniq_为前缀 | check_index_prefix |
| 普通索引必须要以idx_为前缀 | check_index_prefix |
| 索引的列数不能超过5个 | max_key_parts |
| 主键索引列数限制 | max_primary_key_parts |
| 主键列必须使用int或bigint | enable_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 |
自增列
| 检查项 | 相关配置项 |
|---|---|
| 建表时，自增列初始值为1 | check_autoincrement_init_value |
| 如果自增列的名字不为id，说明可能是有意义的，不建议 | check_autoincrement_name |
| 自增列类型必须为int或bigint | check_autoincrement_datatype |
| 自增列需要设置无符号 | enable_autoincrement_unsigned |
ALTER
| 检查项 | 相关配置项 |
|---|---|
| 表是否存在 | |
| 同一个表的多个ALTER建议合并 | merge_alter_table |
| 列是否存在 | |
| 表属性只支持对存储引擎、表注释、自增值及默认字符集的修改操作。 | |
| 是否允许change column操作 | enable_change_column |
| 是否允许列顺序变更 | check_column_position_change |
| 是否允许列类型变更 | check_column_type_change |
DML
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_rule |
UPDATE/DELETE
| 检查项 | 相关配置项 |
|---|---|
| 表必须存在 | |
| 必须有where条件 | check_dml_where |
| 不能有order by语句 | check_dml_orderby |
| 影响行数大于10000条，则报警（数目可配置） | max_update_rows |
| 对WHERE条件这个表达式做简单检查，具体包括什么不一一指定 | |
| 多表更新、删除时，每个表及涉及字段必须要存在 | |
| update 多表关联时,如果set未指定表前缀,自动判断 | |
| 多表时判断未指明表前缀的列是否有歧义 | |
| update多表关联时,如果set了多个表的字段,同样支持回滚语句生成 | |
| 使用explain获取预估行数或select count获取真实行数 | 调用选项realRowCount,explain_rule |
| mysql版本在5.6之前时,自动将语句转换为select做explain | |
设置数据库sql_safe_updates参数 |
sql_safe_updates |
| 多表关联时,审核join语句是否包含on子句 | check_dml_where |
| 条件中的列是否存在隐式类型转换 | check_implicit_type_conversion |
| update set 判断set使用了逗号还是and分隔 |
四、使用感受
简单测试了一下goinception，整体体验下来感觉还是很不错的，相比于之前的inception来说，goinception有如下几个优点：
五、系统集成
到这里，我们已经基本上选择goinception作为我们的审核引擎了，那么如何集成到现有的自动化系统中是个问题。
先说一下我们公司的情况，我们公司有流程中心，也就是开发会在上面选择库，提交SQL，DBA收到工单以后人工审核，手工执行。
在审核过程中，对于DBA来说，肉眼审核也太过于辛酸了，手工执行一不小心就是背黑锅。所以，基于goinception，我这边提供了几个restful的接口，分别如下：
这样一来，DBA的双手可以大大地解放了，不仅提高了工作效率，并且能够准确无误地执行SQL，减少背黑锅的概率，还提供了回滚SQL的功能，假如开发发现SQL提交有误可以及时查看回滚SQL进行数据修复。