腾讯游戏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 条评论
登录 后参与评论

相关文章

来自专栏不想当开发的产品不是好测试

mysql 删表引出的问题

背景 将测试环境的表同步到另外一个数据库服务器中,但有些表里面数据巨大,(其实不同步该表的数据就行,当时没想太多),几千万的数据!! 步骤 1. 既然已经把数据...

2937

使用 Excel 分析 CloudStack 使用记录

注:本文最初由 David Nailey 在 Build a Cloud 博客上撰写。

2169
来自专栏idba

死锁案例之十

死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋...

1082
来自专栏文渊之博

SQL Server内存

背景 最近一个客户找到我说是所有的SQL Server 服务器的内存都被用光了,然后截图给我看了一台服务器的任务管理器。如图 ? 这里要说明一下任务管理器不会完...

3627
来自专栏数据和云

Library Cache优化与SQL游标

? 冷菠 冷菠,网名悠然(个人主页http://www.orasky.net),资深DBA,著有《Oracle高性能自动化运维》,有近10年的数据库运维、团队...

2905
来自专栏沃趣科技

初相识 | 全方位认识 sys 系统库

前阵子,我们的"全方位认识performance_schema"系列为大家完整的介绍了performance_schema系统库。在我们的发布计划中为什么要把p...

2263
来自专栏程序猿

MySQL数据库优化小谈,简短。

更新文章的速度跟不上大家的热情了......,青岛的一场大雪,取暖基本靠抖了。 ? 好勒,现在写正经的。对于优化,这片文章我只说大致思路...

3517
来自专栏北京马哥教育

思路决定出路 | 101个MySQL调试和优化技巧

MySQL是一个功能强大的开源数据库。随着越来越多的数据库驱动的应用程序,人们一直在推动MySQL发展到它的极限。这里是101条调节和优化 MySQL安装的技巧...

2808
来自专栏liuchengxu

在 Golang 开发中使用 Makefile

使用 Golang 已经有一阵了,在 Golang 的开发过程中,我已经习惯于不断重复地手动执行 go build 和 go test 这两个命令. 不过,现...

1791
来自专栏PHP在线

MYSQL性能优化分享(分库分表)

MYSQL性能优化之分库分表与不停机修改mysql表结构,需要的朋友可以参考下 1、分库分表 很明显,一个主表(也就是很重要的表,例如用户表)无限制的增长势必严...

4025

扫码关注云+社区

领取腾讯云代金券