专栏首页MySQL修行 | 老叶茶馆DBA的福音,SQL审核利器-goinception

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_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

表属性

检查项

相关配置项

这个表不存在

当前库存在

表名、列名、索引名的长度不大于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

UPDATE/DELETE

四、使用感受

简单测试了一下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进行数据修复。

全文完。

Enjoy MySQL :)


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

本文分享自微信公众号 - 老叶茶馆(iMySQL_WX),作者:徐晨亮

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-08-27

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 组复制安装部署 | 全方位认识 MySQL 8.0 Group Replication

    组复制以插件的形式提供给MySQL Server使用,组中的每个Server都需要配置和安装MGR插件。本节将以三节点的组复制为例,详细介绍组复制的搭建步骤。

    老叶茶馆
  • 组复制常规操作-分布式恢复 | 全方位认识 MySQL 8.0 Group Replication

    注意:当完成状态传输后,组复制将重新启动joiner节点的数据库进程以完成该过程。如果在joiner节点上设置了group_replication_start_...

    老叶茶馆
  • 组复制背景 | 全方位认识 MySQL 8.0 Group Replication

    MySQL Group Replication(MGR)自问世以来,一直是大家技术分享、技术讨论的热点,虽然在MySQL 5.7版本中,MGR 还不尽完善,但其...

    老叶茶馆
  • DBASK问答集萃第二期

    近期我们对DBASK小程序进行了升级,UI交互做了重大优化调整,对注册用户开放知识库全文检索功能,引入数据和云公众号文章,提问时自动关联知识库已知问题,专栏可生...

    数据和云
  • 腾讯微校与企鹅电竞携手,共同布局腾讯高校电竞领域

    12月30日,在2017企鹅电竞QGC高校赛总决赛上,腾讯微校与企鹅电竞联合发布成立“企鹅电竞-腾讯微校高校电竞联盟”,二者将共同布局腾讯高校电竞领域生态,提...

    腾讯技术工程官方号
  • javafx框架tornadofx实战-舒尔特5-增加字体大小设置和进度显示功能

    标签文本绑定到两个属性变量resultSize(结果长度)和correctSize(总长度)

    用户6167008
  • Helvetica字体的50年

    Helvetica是一种字体的名称,去年是它诞生50周年。这部纪录片就采访了许多设计师,让他们来谈自己如何看待这种字体,从而展现了字体的发展和字体设计师的生活,...

    ruanyf
  • 科大讯飞人工智能方向的一次面试经历

    三哥
  • 易图秒懂の深度学习诞生 问题篇

    认知科学改写了一帮搞物理的人在做神经网络的局面(参考 ”易图秒懂の连接主义诞生“), 从此以心理学大师Estes的学生,Rumelhart为代表的连接主义的早...

    史博
  • 科大讯飞人工智能方向的一次面试经历

    就在上个星期五(2017年12月8号),我去了科大讯飞面试Java智能语音这一块。

    三哥

扫码关注云+社区

领取腾讯云代金券