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

本文转载自“高效运维”公众号，已获得授权。

作者介绍

韩全安（willhan）

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

• 团队博客： tencentdba.com

• 团队Github: https://github.com/TencentDBA/TMySQL

主题简介

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

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_defaults
、server_init
、sql_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平台高危告警示例图

如何一起愉快地发展

“高效运维”公众号（如下二维码）值得您的关注，作为高效运维系列微信群的唯一官方公众号，每周发表多篇干货满满的原创好文：来自于系列群的讨论精华、运维讲坛线上精彩分享及群友原创。“高效运维”也是互联网专栏《高效运维最佳实践》及运维2.0官方公众号。