TIDB General Log 抓取分析神器

作者： 裤衩儿飞上天原文来源：https://tidb.net/blog/d3a824d2

背景

在社区经常见到小伙伴的一个需求，抓取数据库所有操作记录，严格来说是所有业务端执行过的 sql，大部分原因是开发或者老板要查问题，又不知道问题发生在哪个模块，所以就要所有数据库操作记录。

本文主要讲述通过数据库自带的日志功能和抓取抓网络包两种方法来解决该需求。

数据库开启日志记录

mysql 开启 general_log

1. 相关参数

开启 general log 会将所有 mysql 执行过的的 SQL 语句记录下来。一般不会开启开此功能，因为 log 的量会非常庞大。但个别情况下会临时打开 general log 以供排障使用，使用完毕后再关闭。 ​ 相关参数一共有 3 个：general_log、log_output、general_log_file。

 show variables like 'general_log'; -- 查看日志是否开启 set global general_log=on; -- 开启日志功能 show variables like 'general_log_file'; -- 查看日志文件保存位置 set global general_log_file='tmp/general.lg'; -- 设置日志文件保存位置 show variables like 'log_output'; -- 看看日志输出类型 table或file set global log_output='table'; -- 设置输出类型为 table set global log_output='file'; -- 设置输出类型为file 

log_output=’FILE’ 表示将日志存入文件, 默认值是 FILE；’TABLE’表示将日志存入数据库, 这样日志信息就会被写入到 mysql.slow_log 表中。mysql 数据库支持同时两种日志存储方式, 配置的时候以逗号隔开即可, 如:log_output=‘FILE,TABLE‘. 日志记录到系统专用日志表中, 要比记录到文件耗费更多的系统资源, 因此对于需要启用慢查询日志, 又对性能有要求, 那么建议记录到文件。

2. 开启数据库 general_log 步骤

• 先执行 sql 指令：show variables like ‘%log%’;

• 默认 general_log 是 OFF 的，直接开启：set global general_log = ON;（永久修改需要在 my.cnf 的【mysqld】中添加：general_log = 1）

• 现在 mysql 就会在 general_log_file 显示的路径文件里记录 general 日志了。

TIDB 开启 general log

1. 相关参数

官方文档关于 general_log 的描述如下：

tidb_general_log

• 作用域：GLOBAL

• 是否持久化到集群：否，仅作用于当前连接的 TiDB 实例

• 默认值：OFF
  

• 这个变量用来设置是否在日志里记录所有的 SQL 语句。该功能默认关闭。如果系统运维人员在定位问题过程中需要追踪所有 SQL 记录，可考虑开启该功能。

• 在 TiDB 配置项 log.level 为 ”info” 或 ”debug” 时，通过查询 ”GENERAL_LOG” 字符串可以定位到该功能在日志中的所有记录。日志会记录以下内容：

• conn：当前会话对应的 ID

• user：当前会话用户

• schemaVersion：当前 schema 版本

• txnStartTS：当前事务的开始时间戳

• forUpdateTS：事务模式为悲观事务时，SQL 语句的当前时间戳。悲观事务内发生写冲突时，会重试当前执行语句，该时间戳会被更新。重试次数由 max-retry-count 配置。事务模式为乐观事务时，该条目与 txnStartTS 等价。

• isReadConsistency：当前事务隔离级别是否是读已提交 (RC)

• current_db：当前数据库名

• txn_mode：事务模式。可选值：OPTIMISTIC（乐观事务模式），或 PESSIMISTIC（悲观事务模式）

• sql：当前查询对应的 SQL 语句

2. tidb 开启 general log

• set global tidb_general_log=on ，开启后针对当前节点立即生效，其他节点并不会开启，并且不会持久化到集群

• 通过修改配置文件参数，使该参数永久生效，作用域范围根据配置，可以是单个节点，也可以是所有的 tidb server。例（修改所有 tidb server）：

抓取网络日志

开启日志缺点

不论是 tidb 还是 mysql，开启 general 会有性能损耗，尤其是生产环境。

• 写大量日志，io 有影响；并发高、监控不及时的情况下还可能会把磁盘打爆。

• 所有的日志回记录到一个文件里面，查看起来并不友好。尤其是 tidb，tidb 的 general log 是跟 tidb server 的日志 tidb.log 记录到一起的，查看起来非常麻烦。

• 另外，在极端的高并发和高负载模式下，数据库可能会 hang 住，日志可能会记录不成功。

在上边这种情况下，我们可以跳过数据库层，直接从网络层抓取 sql。本文主要介绍三个工具，tcpdump，vc-mysql-sniffer，mysql-snaffer

tcpdump

• tcpdump 是抓取网络包的一个工具，大家可能对其并不陌生，就简单说说使用方法，但是这种方式抓出来的文件，格式并不太友好，查看起来比较麻烦。

• 可以抓包结合 pt-query-digest 来分析查看

vc-mysql-sniffer

vc-mysql-sniffer 是一个免费的基于 MySQL 协议的抓包工具，只要是 mysql 协议的数据库都支持，是本文力荐的一个工具（个人测试对 tidb 支持非常友好）。说明如下

 vc-mysql-sniffer - A free tool to output query traffic for MySQL ​ To run: Simply execute the binary to see queries written to stdout in the format used by MySQL's slow query log. Add the -output=[filename] option to write the output to the specified file instead.

1. 主要优点

• 是一个二进制工具，使用起来比较方便。

• 对数据库 server 性能影响非常小。

• 相比 tcpdump，不会生产很大的日志文件。

• 只要是支持 mysql 协议的数据库，都可以用其来抓取。

• 生成的日志文件，跟 slow log 类似，可以直接查看，也可以直接用 pt-query-digest 分析。

• 抓取的时候启动，不抓取随时可以停掉，数据库不用做任何配置更改。

下载地址：vonwind/analysis-vc-mysql-sniffer (github.com)（由于官方已经不再更新该工具，这里就放了 github 的地址）

2. 使用方法一：

注意：vc-mysql-sniffer 部署在数据库服务器上，如果 tidb 集群前端有代理（比如 haproxy），需要部署在代理服务器上，以抓取所有的 sql。

 ## help说明 [fenglei@qa-fenglei-01 vc-mysql-sniffer]$ ./vc-mysql-sniffer --help vc-mysql-sniffer is a utility from VividCortex to monitor query activity and write results to a file. See --license for the terms governing your usage of this program. ​   -binding="[::]:3306"         This is a list of comma separated bind strings as seen in /proc/net/tcp   -help="false"                Show this usage message   -license="false"             Print the usage terms of this program   -output=""                   Filepath to output queries to. Defaults to stdout if none specified.   -show-database="false"       Include a 'USE `database`' for every statement. Supersedes show-database-changes.   -show-database-changes="true"                                Include a 'USE `database`' every time the database is changed.   -verbose="false"             Enable logging on program startup to stderr   -version="false"             Show version and exit ​   Flag                         Current value --------------------------------------------   -binding                     "[::]:3306"   -help                        "true"   -license                     "false"   -output                      ""   -show-database               "false"   -show-database-changes       "true"   -verbose                     "false"   -version                     "false" [fenglei@qa-fenglei-01 vc-mysql-sniffer]$ ​ ## 执行需要sudo权限 [fenglei@qa-fenglei-01 vc-mysql-sniffer]$ sudo ./vc-mysql-sniffer -binding="[::]:4000" -output="/tmp/vc_tidb_sql.log" ​ ## 生成的结果可以用pt-query-digest直接分析 ./pt-query-digest /tmp/vc_tidb_sql.log > /tmp/result.log

3. 使用方法二：用网友提供的一个脚本直接调用 vc-mysql-sniffer 直接生成 sql 统计结果

脚本地址：analysis-vc-mysql-sniffer/analysis-vc-log.py at master · vonwind/analysis-vc-mysql-sniffer (github.com)

 python analysis-vc-log.py yourip 3306 | sort | uniq -c | sort -nr |head -n 10

mysql-sniffer

MySQL Sniffer 是一个基于 MySQL 协议的抓包工具，实时抓取 MySQLServer 端或 Client 端请求，并格式化输出。输出内容包括访问时间、访问用户、来源 IP、访问 Database、命令耗时、返回数据行数、执行语句等。有批量抓取多个端口，后台运行，日志分割等多种使用方式，操作便捷，输出友好。项目地址：Qihoo360/mysql-sniffer: mysql-sniffer is a network traffic analyzer tool for mysql, it is developed by Qihoo DBA and infrastructure team (github.com)

由于该项目也已经不再维护，原理跟 vc-mysql-sniffer 类似，只是输出结果（展示方式）不太一样。但是输出的结果不能用 pt-query-digest 分析，所以，个人还是更推荐 vc-mysql-sniffer。

总结

个人还是力荐 vc-mysql-sniffer，也希望这个小工具能给大家的工作带来便利。文中有误不对的地方，也请大家拍砖指正。