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

运维 数据库运维
最近在重构公司自动化运维平台的SQL审核部分,于是调研了一下goinception。在之前的公司审核引擎用的是去哪儿网开源的inception,由于某些原因,inception长久不更新并且已经不再开源,所以就把目光转向了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有一个默认的配置文件,大概意思也写得比较清楚了,那我修改的部分已经写在下面的配置文件中了,其他部分我都没有修改

 

  1. # 日志文件  
  2. [log.file]  
  3. filename = "/opt/goinception/logs/goinception.log"  
  4. max-days = 7  
  5. max-backups = 7  
  6. [inc]  
  7. backup_host = "xxxx"  
  8. backup_port = 3306  
  9. backup_user = "goinception"  
  10. backup_password = "goinception"  
  11. sql_safe_updates = 1  
  12. lang = "zh-CN"  
  13. [osc]  
  14. osc_min_table_size = 1024  
  15. [ghost]  
  16. ghost_on = true  
  17. ghost_chunk_size = 10000  
  18. ghost_dml_batch_size = 100 

那么,我们启动goinception 

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

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

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

测试

goinception可以沿用inception的调用方法,示例如下: 

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

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

  1. #!/usr/bin/env python3  
  2. # -*- coding:utf-8 -*-  
  3. import pymysql  
  4. import prettytable as pt  
  5. tb = pt.PrettyTable()  
  6. sql = '''/*--user=xucl;--password=xuclxucl123;--host=127.0.0.1;--check=1;--port=3308;*/  
  7. inception_magic_start;  
  8. use sbtest;  
  9. create table t1(id int primary key,c1 int, c2 int);  
  10. insert into t1(id,c1,c2) values(1,1,1);  
  11. inception_magic_commit;'''  
  12. conn = pymysql.connect(host='127.0.0.1'user=''passwd='',  
  13.                        db=''port=4000charset="utf8mb4")  
  14. cur = conn.cursor()  
  15. ret = cur.execute(sql)  
  16. result = cur.fetchall()  
  17. cur.close()  
  18. conn.close() 
  19. tb.field_names = [i[0] for i in cur.description]  
  20. for row in result:  
  21.     tb.add_row(row)  
  22. print(tb) 

执行后输出如下: 

  1. [root@VM_0_9_centos goinception]# python3 test.py   
  2. +----------+---------+-------------+-----------------+-----------------+----------------------------------------------------+---------------+--------------+---------------+--------------+---------+-------------+ 
  3. | ------+---------------+--------------+---------------+--------------+---------+-------------+|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 | 
  4. +----------+---------+-------------+-----------------+-----------------+----------------------------------------------------+---------------+--------------+---------------+--------------+---------+-------------+ 
  5. |    1     | CHECKED |      0      | Audit Completed |       None      |                     use sbtest                     |       0       | 0_0_00000000 |      None     |      0       |   None  |      0      | 
  6. |    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      | 
  7. |    3     | CHECKED |      0      | Audit Completed |       None      |       insert into t1(id,c1,c2) values(1,1,1)       |       1       | 0_0_00000002 |      None     |      0       |   None  |      0      | 
  8. +----------+---------+-------------+-----------------+-----------------+----------------------------------------------------+---------------+--------------+---------------+--------------+---------+-------------+ 

 可以看到审核结果还是非常清晰的

那如果需要执行的话怎么做呢?我们只需要修改示例中的头部,将--check=1改成--execute=1即可 

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

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

  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有如下几个优点:

  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进行数据修复。  

 

责任编辑:庞桂玉 来源: 老叶茶馆
相关推荐

2009-04-16 18:15:19

动作审核审核活动SQL Server

2020-09-17 12:50:21

MySQL数据库SQL

2011-10-24 22:13:37

DBASQL Server

2018-01-11 16:06:45

数据库SQL自动审核

2010-08-16 10:10:22

SQL脚本

2011-07-07 14:22:24

SQL查询update

2019-06-19 13:10:24

MYSQL开源工具数据库

2019-07-26 09:22:20

工具代码开发

2018-06-01 10:08:00

DBA美团SQL

2021-08-17 10:39:54

SQL Server数据库优化

2018-07-17 14:25:02

SQL解析美团点评MySQL

2015-07-08 11:05:52

4G4G+

2009-01-16 15:00:42

DBASQL存储过程

2009-12-16 17:58:18

2010-02-25 15:41:12

2019-05-17 08:27:23

SQL注入漏洞攻击

2010-07-19 16:54:21

SQL

2023-12-27 08:00:00

SQL数据库LMQL

2023-09-04 08:26:08

手机开源Android

2017-06-20 08:19:56

MySQL集群架构
点赞
收藏

51CTO技术栈公众号