作者介绍
韩全安(willhan) 华中科技大学,硕士,现代数据库方向。2013年毕业,就职于腾讯到今,工作项目:TMySQL、SQL审核、InnoDB列压缩、TSpider、GCS
本文将主要从以下几个部分同大家探讨:
腾讯游戏业务的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
SQL审核工具应该完全兼容 MySQL 的输入,及解析 MySQL 的语法。
我们改造 MySQL 源码的 Client 模块来实现SQL审核工具的输入,利用 MySQL 源码的语法分析模块来对 SQL 语法进行解析。
下图1为 SQL 审核工具与 MySQL 源码模块对应示例。
图1 SQL 审核工具与 MySQL 源码模块对应
SQL审核工具实现中最重要的两个模块:输入模块及语法分析模块,下面分别进行说明。
SQL审核工具的输入模块是对 MySQL Client 源码进行了改造。 MySQL Client 部分的逻辑如下:
其中最主要的函数就是 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输入模块的对比
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的语法模块实现。 这样的好处:
为此我们只需要剥离出函数 parse_sql 即可。
图3 Update语句的yacc规则
上面介绍了 SQL 审核工具的背景及实现,现在讲下SQL审核工具应如何使用。
先总结下 SQL 审核工具: 一个基于 MySQL 5.5.24 实现的组件 TMySQLParse,能够检查 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 前的版本,使用后面版本新增加的保留字作为表中字段也可以通过语法检查。
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 格式输出有助于对结果进行解析。
在下面的 TMySQLParse 的 console 中,输入了如下几条 SQL 语句,一个建表,一个是 delete * from t1
,一个 alter table
操作。
Tmysqlparse 会审核这每条 SQL 语句,我们会发现其中2个语法错误:
另外,也会发现告警,我们的建的表是没有索引的。 SQL 审核工具使用示例详见下图5:
图5 TMySQLParse 工具使用示例
更多SQL审核工具的用法详见: http://tencentdba.com/blog/tmysqlparse-instructions/
腾讯游戏 GCS 平台当前已集成了 TMySQLParse 工具,下图为应用 TMySQLParse 工具后,GCS 平台的单据检测效果:
图6 GCS 平台中应用TMySQLParse检测出语法错误
点开语法错别的链接,可得如下详细语法错误信息,同 MySQL 的表现完全一致。
图7 点击语法错误信息后所示
SQL 审核工具除了能够检测语法错误,还是提示高危的 SQL 语句给 DBA,减少 DBA 审单的压力,下图为高危告警的示例图:
图8 GCS平台高危告警示例图