腾讯游戏DBA利刃 - SQL审核工具介绍

作者介绍

韩全安(willhan) 华中科技大学,硕士,现代数据库方向。2013年毕业,就职于腾讯到今,工作项目:TMySQL、SQL审核、InnoDB列压缩、TSpider、GCS

主题简介

本文将主要从以下几个部分同大家探讨:

  1. 诞生背景
  2. 实现原理
  3. 使用介绍
  4. 应用示例

1. 诞生背景

腾讯游戏业务的DB变更流程是由职能化或运维同学在腾讯游戏GCS平台(Game Cloud Storage)中提SQLScript的变更单,DBA对SQL逐句进行审核,通过后再由提单者在GCS平台执行现网变更。

腾讯游戏GCS平台:是腾讯互动娱乐事业群DBA(简称:腾讯游戏DBA)倾力打造,提供多样化的底层存储架构(TProxy\TMySQL\TSpider\TRedis),以及贴合游戏运营生命周期实现数据层的管理调度体系。

由于变更单据的多样性与复杂性,变更单的审核工作不止消耗DBA大量的时间精力,也无法保证变更单的正确性。可能会执行非法SQL导致变更时间延长,影响游戏正常开服造成损失。

下表为腾讯游戏 GCS 平台(Game Cloud Storage)统计2012.7.1~2013.7.1 一年SQL变更单据语法错误的结果。

表1 2012.7.1~2013.7.1的单据语法错误统计

从上表可以看出,变更因语法错误导致的失败率为3.3%,平均每2天有一个变更失败是因为语法错误。于是诞生了SQL审核工具(TMySQLParse)。

SQL审核工具(TMySQLParse)用于对 MySQL的SQL 语句进行语法解析,判定语法正确性,并根据自定义的高危特性检测SQL是否存在高危情况。

通过将 TMySQLParse 集成到 GCS 平台中,可以降低人工审单的难度及减少其工作量,从而实现审单的自动化。

在 TMySQLParse 集成 GCS 平台后,运维的提单就可由SQL审核工具自动进行语法解析及高危SQL告警,保证提交语法正确的变更单据到现网服务器中。

SQL审核工具 TMySQLParse 的git地址(包含有32位与64位可执行文件): https://github.com/GCSAdmin/tmysqlparse

2. 实现原理

SQL审核工具应该完全兼容 MySQL 的输入,及解析 MySQL 的语法。

我们改造 MySQL 源码的 Client 模块来实现SQL审核工具的输入,利用 MySQL 源码的语法分析模块来对 SQL 语法进行解析。

下图1为 SQL 审核工具与 MySQL 源码模块对应示例。

图1 SQL 审核工具与 MySQL 源码模块对应

SQL审核工具实现中最重要的两个模块:输入模块及语法分析模块,下面分别进行说明。

2.1 输入模块

SQL审核工具的输入模块是对 MySQL Client 源码进行了改造。 MySQL Client 部分的逻辑如下:

  1. MY_INIT(),初始化一些系统函数、资源及变量,比如线程、临界区及tcp/ip等。
  2. Isatty(),判定输入输出是文件还是 console 。
  3. load_defaults(),从配置文件读取配置参数。
  4. get_options(),读取mysql 选项参数。
  5. batch_readline_init(),初始化 console 大小。
  6. mysql_server_init(),与服务器相关信息的初始化。
  7. init_alloc_root(),分配 root 内存。
  8. sql_connect(),与Server连接。
  9. read_and_execute(),处理SQL语句并与Server交互。
  10. mysql_end(),资源释放。

其中最主要的函数就是 read_and_execute(),在这个函数中有对SQL语句的完整处理。

read_and_execute() 函数的主体是一个 for(;;) 循环。

在这个循环里,处理每一条读取或者输入的字符串,分别通过函数 batch_readline() 从文件读或函数 my_cgets() 从终端读,利用哪种方式读取取决于前面的 isatty() 函数值。

SQL解析工具需要完全兼容 MySQL Client 的输入模式,比如文件/终端输入,支持 delimiter 分割断句,支持各种注释等,却不需要连接到MySQL Server,并与MySQL Server进行交互。

因此,SQL审核工具在MySQL Client的基础上,不需要做load_defaultsserver_initsql_connect ,并且将 read_and_execute() 改造为 read_and_sqlparse ,即提取SQL并进行语法解析。

图2 MySQL Client与TMySQLParse输入模块的对比

2.2 语法解析模块

MySQL 语法处理是由 yacc 实现的, yacc(Yet Another Compiler Compiler) ,是一个经典的生成语法分析器的工具。

MySQL 通过 yacc定义语法规则,并且将 SQL 语句解析出来的内容放在LEX结构体里。

如下图3所示为 Update 语句的 yacc 规则,

Update:为一个非终结符,后面为一系列的终结符号与非终结符号组合。

如果SQL语句能够匹配到其中的终结符号,则执行大括弧 {} 中的动作,否则则进一步解析解析成终结符号。

从图3也可以看出,语法解析的内容存放在LEX结构体中。在 MySQL 源码中,函数 parse_sql() 封装了MySQL中通过 yacc 解析语法的逻辑。传入一条 SQL 给 parse_sql(), parse_sql()即可将SQL语句生成语法树,保存到LEX结构体中。

SQL 审核工具的语法解析模块就是依赖于MySQL的语法模块实现。 这样的好处:

  1. 完全兼容 MySQL 的语法
  2. 不需要构造复杂LEX结构体,这样除了满足语法检查的要求,也可以通过 LEX 获取语法特征信息

为此我们只需要剥离出函数 parse_sql 即可。

图3 Update语句的yacc规则

3. 使用介绍

上面介绍了 SQL 审核工具的背景及实现,现在讲下SQL审核工具应如何使用。

先总结下 SQL 审核工具: 一个基于 MySQL 5.5.24 实现的组件 TMySQLParse,能够检查 SQL 语法正确性,并提取需要的语法特征。

具体一点就是:

  • TMySQLParse 是一个独立组件,能够兼容 MySQL 的输入(终端/文件,delimiter 断句)。
  • 完全的支持MySQL语法(MySQL 5.5.24, TMySQL 1.4),并兼容多个 MySQL 版本的保留字 。
  • 能够提取语句类型,包括自定义类型(CREATE_TABLE_WITHOUT_INDEX),能够提取库、表、索引等信息 。

3.1 SQL 审核工具参数

SQL 解析组件有如下参数,比如指定字符集,字符 MYSQL 版本号,指定输入的文件路径,指定获取表的个数,获取 table,database 信息等。具体详见图4:

图4 TMySQLParse工具选项示例

其中-v参数用来指定MySQL的版本号。 TMySQLParse是基于MySQL 5.5.24的源码实现,所以主要支持MySQL 5.5.24版本的语法。此处指定MySQL版本号的意义在于兼容MySQL各个版本间的保留字。由于各业务使用习惯不同,开发商可能在MySQL 5.1版本中使用了MySQL 5.5版本的保留字,会被TMySQLParse判定为语法错误。

为此我们兼容了 MySQL 各个版本间的保留字。即 MySQL 5.5.24 前的版本,使用后面版本新增加的保留字作为表中字段也可以通过语法检查。

3.2 SQL 审核工具的输入/输出

SQL 审核工具 TMySQLParse 提供两种输入方式:

  • 从终端输入
  • 文本输入

文本输入可以通过./tmysqlparse < xxx.sql。 xxx.sql 即为输入的文件。

TMySQLParse 部分参数使用示例,详见如下:

指定输出结果到 xxx.xml 中 ./tmysqlparse -f xxx.xml

version 是 MySQL 版本号,如 ”5.0” ./tmysqlparse -v version

输出 TMySQLParse 的版本信息 ./tmysqlparse -V/--version

提供帮助信息 ./tmysqlparse –help

指定数据库名为test ./tmysqlparse test

示例命令:

./tmysqlparse –f xxx.xml test –v “5.1” < xxx.sql

tmysqlparse以xml的形式输出SQL检查的结果。

通过如下定义: <result></result> 中包含TMySQLParse分析后的所有结果。

<syntax_failed></syntax_failed> 包含所有语法出错的信息。

<failed_info></failed_info> 包含一条出错语句,里面再分 <sql>、<error_code>、<error_msg><line> 四部分来输出出错SQL语句的信息。

<risk_warnings></risk_warnings> 包含所有的高危告警信息,产生告警的前提是语法正确,与 <syntax_failed></syntax_failed> 互相独立,不存在交集。

<warning_info></warning_info> 包含一条产生高危告警的SQL语句信息。

<type>、<name>、<text><line> 四部分给出告警SQL语句信息。

<info></info> 则存储额外的信息

<type> 中包含是产生告警的类型,比如:

STMT_DROP_DB  删除数据库操作
STMT _DROP_TABLE  删除表操作
STMT _DROP_VIEW 删除视图操作
STMT _TRUNCATE 清空表操作
STMT _DELETE 删除操作不带where条件
STMT _UPDATE 更新操作不带where条件
STMT _CREATE_TABLE 创建表时blob/text字段数大于10
STMT _ALTER_TABLE 更改表增加的blob/text字段数大于10

下图5为输出结果的截图,以 xml 格式输出有助于对结果进行解析。

3.3 SQL 审核工具的使用

在下面的 TMySQLParse 的 console 中,输入了如下几条 SQL 语句,一个建表,一个是 delete * from t1,一个 alter table 操作。

Tmysqlparse 会审核这每条 SQL 语句,我们会发现其中2个语法错误:

  • delete 这条SQL多一个*
  • alter table 加字段多了一个add

另外,也会发现告警,我们的建的表是没有索引的。 SQL 审核工具使用示例详见下图5:

图5 TMySQLParse 工具使用示例

更多SQL审核工具的用法详见: http://tencentdba.com/blog/tmysqlparse-instructions/

4. 应用示例

腾讯游戏 GCS 平台当前已集成了 TMySQLParse 工具,下图为应用 TMySQLParse 工具后,GCS 平台的单据检测效果:

图6 GCS 平台中应用TMySQLParse检测出语法错误

点开语法错别的链接,可得如下详细语法错误信息,同 MySQL 的表现完全一致。

图7 点击语法错误信息后所示

SQL 审核工具除了能够检测语法错误,还是提示高危的 SQL 语句给 DBA,减少 DBA 审单的压力,下图为高危告警的示例图:

图8 GCS平台高危告警示例图

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-05-15

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏https://www.cnblogs.com/L

【Spark篇】---Spark中Shuffle文件的寻址

Spark中Shuffle文件的寻址是一个文件底层的管理机制,所以还是有必要了解一下的。

1005
来自专栏FreeBuf

Fuzz自动化Bypass软WAF姿势

0×00 前言 在我刚接触安全这块时候遇到注入有WAF的网站时候无从下手,寻找各种有关绕过waf的文章,在网页浏览器上使用SQL语句为了绕过WAF变了个法加了些...

49010
来自专栏随心DevOps

[实战篇] Python 运维中使用并发

今天从大哥手里接了一个需求: 验证一下新的 Docker 镜像仓库(Docker Registry)是否迁移成功了 简单粗暴的方法就是拿到老仓库中的镜像列表(I...

38612
来自专栏乐百川的学习频道

Python 爬虫获取某贴吧所有成员用户名

最近想用Python爬虫搞搞百度贴吧的操作,所以我得把原来申请的小号找出来用。有一个小号我忘了具体ID,只记得其中几个字母以及某个加入的贴吧。所以今天就用爬虫来...

17810
来自专栏Java架构师历程

权限表的设计

用户通过角色与权限进行关联。简单地说,一个用户拥有若干角色,每一个角色拥有若干权限。这样,就构造成“用户-角色-权限”的授权模型。在这种模型中,用户与角色之间,...

1202
来自专栏Sorrower的专栏

内核必须懂(二): 文件系统初探

715
来自专栏c#开发者

Oracle 开放源代码项目

Oracle 开放源代码项目 这是无数个可扩展、使用以及构建于 Oracle 技术的开放源代码项目中的一个简短的示例。如果您有自己喜欢的开放源代码项目未在此处列...

4868
来自专栏有趣的Python

Elastic Stack入门与实践

配置文件位于config目录中: - elasticsearch.yml es的相关设置 - jvm.options jvm的相关参数 - log4j2....

1582
来自专栏Danny的专栏

探秘Oracle表空间、用户、表之间的关系

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/huyuyang6688/article/...

692
来自专栏PHP技术

比较全面的MySQL优化参考

本文整理了一些MySQL的通用优化方法,做个简单的总结分享,旨在帮助那些没有专职MySQL DBA的企业做好基本的优化工作,至于具体的SQL优化,大部分通过加适...

3254

扫码关注云+社区