配置表 | 全方位认识 sys 系统库

作者   罗小波·沃趣科技高级数据库技术专家

出品   沃趣科技

在上一篇《初相识 | 全方位认识 sys 系统库》中，我们针对sys 系统库做了一个不痛不痒的开端，是不是觉得太简单了？别急，本期我们将为大家带来系列第二篇《配置表|全方位认识 sys 系统库》，让你一次性重新找回学习performance_schema时的感觉，下面请跟随我们一起开始 sys 系统库的系统学习之旅吧。

| sys 系统库配置

1. sys_config表

该表包含sys系统库的配置选项，每个配置选项一行记录。该表是innodb表，可以通过客户端更新此表来持久化配置，server重启不会丢失。

sys_config表字段含义如下：

• variable：配置选项名称

• value：配置选项值

• set_time：该行配置最近修改时间

• set_by：最近一次对改行配置进行修改的帐户名。如果自server安装sys 系统库以来，该行配置从未被更改过，则该列值为NULL

为了减少对sys_config表直接读取的次数，sys 系统库中的视图、存储过程在需要使用到这些配置选项时，会优先检查这些配置选项对应的用户自定义配置选项变量(用户自定义配置选项变量与该表中的配置选项都具有相同的名称，例如：表中的diagnostics.include_raw选项，对应的自定义配置选项变量是@sys.diagnostics.include_raw)。如果用户定义的配置选项变量存在于当前会话作用域中并且是非空的，那么sys 系统库中的函数、存储过程将优先使用该配置选项变量值。否则，该sys 系统库函数和存储过程将使用sys_config表中的配置选项值(从表中读取配置选项值之后，会将sys_config表中的配置选项时同时更新到用户自定义配置选项变量中，以便在同一会话后续对该值的引用时使用变量值，而不必再次从sys_config表中读取)，示例：statement_truncate_len配置选项控制format_statement()函数返回的语句的最大长度。默认值为64.如果要临时将当前会话的值更改为32，可以设置对应的@sys.statement_truncate_len用户定义的配置选项变量：

# statement_truncate_len配置选项默认是64，直接调用format_statement()函数返回是64字节长度，在未调用任何涉及到该配置选项的函数之前，该自定义变量值为NULL，此时函数需要从表中查询默认值
admin@localhost : sys 11:47:37> select @sys.statement_truncate_len;
+-----------------------------+
| @sys.statement_truncate_len |
+-----------------------------+
| NULL                        |
+-----------------------------+
1 row in set (0.00 sec)
admin@localhost : sys 11:51:53> SET @stmt = 'SELECT variable, value, set_time, set_by FROM sys_config';
Query OK, 0 rows affected (0.00 sec)
admin@localhost : sys 11:52:04> SELECT format_statement(@stmt);
+----------------------------------------------------------+
| format_statement(@stmt)                                  |
+----------------------------------------------------------+
| SELECT variable, value, set_time, set_by FROM sys_config |
+----------------------------------------------------------+
1 row in set (0.01 sec)
# 调用过一次format_statement()函数之后，表中的默认值会被更新到该自定义配置选项变量中
admin@localhost : sys 11:52:12> select @sys.statement_truncate_len;
+-----------------------------+
| @sys.statement_truncate_len |
+-----------------------------+
| 64                          |
+-----------------------------+
1 row in set (0.00 sec)
# 在会话级别中修改为32
admin@localhost : sys 11:52:20> set @sys.statement_truncate_len = 32;
Query OK, 0 rows affected (0.00 sec)
admin@localhost : sys 11:52:34> select @sys.statement_truncate_len;
+-----------------------------+
| @sys.statement_truncate_len |
+-----------------------------+
|                          32 |
+-----------------------------+
1 row in set (0.00 sec)
# 再次调用format_statement()函数值，可以发现返回结果中的长度缩短了，说明使用了session级别修改的值32
admin@localhost : sys 11:52:41> SELECT format_statement(@stmt);
+-----------------------------------+
| format_statement(@stmt)          |
+-----------------------------------+
| SELECT variabl ... ROM sys_config |
+-----------------------------------+
1 row in set (0.00 sec)

要停止使用用户定义的配置选项变量并恢复使用sys_config表中的值，可以将会话中的配置选项变量设置为NULL，或者结束当前会话（结束会话会使得用户定义的变量被销毁）重新开启一个新的会话：

mysql> SET @sys.statement_truncate_len = NULL;
mysql> SELECT format_statement(@stmt);
+----------------------------------------------------------+
| format_statement(@stmt)                                  |
+----------------------------------------------------------+
| SELECT variable, value, set_time, set_by FROM sys_config |
+----------------------------------------------------------+

注意：如果用户在会话中设置了自定义配置选项变量值，然后再更新了sys_config表中相同名称的配置选项，则对于当前会话，sys_config表中的配置选项值不生效（除非设置自定义配置选项变量值为NULL），只对于新的会话且不存在自定义配置选项变量或者自定义配置选项值为NULL生效（因为此时会从sys_config表中读取）

sys_config表中的选项和相应的用户定义的配置选项变量相关描述如下：

• diagnostics.allow_i_s_tables，@sys.diagnostics.allow_i_s_tables：如果此选项为ON，则diagnostics()存储过程在调用时会扫描INFORMATION_SCHEMA.TABLES表找到所有的基表与STATISTICS表执行联结查询，扫描每个表的统计信息。如果基表非常多，该操作可能比较昂贵。默认为OFF。此选项在MySQL 5.7.9中新增

• diagnostics.include_raw，@sys.diagnostics.include_raw：如果此选项为ON，则diagnostics()存储过程的输出信息中会包括metrics视图中的原始输出信息（该存储过程中会调用metrics视图）。默认为OFF。此选项在MySQL 5.7.9中新增

• ps_thread_trx_info.max_length，@sys.ps_thread_trx_info.max_length：由ps_thread_trx_info()函数生成的JSON输出结果的最大长度。默认值为65535字节。此选项在MySQL 5.7.9中新增

• statement_performance_analyzer.limit，@sys.statement_performance_analyzer.limit：不具有内置限制的视图返回的最大行数。默认值为100（例如，statements_with_runtimes_in_95th_percentile视图具有内置限制，即只返回平均执行时间为占总执行时间分布的95百分位数的语句）。此选项在MySQL 5.7.9中新增

• statement_performance_analyzer.view，@sys.statement_performance_analyzer.view：给statement_performance_analyzer()存储过程当作入参使用的自定义查询或视图名称（statement_performance_analyzer()存储过程由diagnostics()存储过程内部调用）。如果该选项值包含空格，则将其值解释为查询语句。否则解释为视图名称，且这个视图必须是提前创建好的用于查询performance_schema.events_statements_summary_by_digest表的视图。如果statement_performance_analyzer.limit配置选项值大于0，则statement_performance_analyzer.view配置选项指定的查询语句或视图中不能有任何LIMIT子句(因为statement_performance_analyzer.limit选项在statement_performance_analyzer()存储过程中是作为一个条件判断值决定是否要添加一个LIMIT子句，如果你再自行添加一个LIMIT会导致语法错误)。statement_performance_analyzer.view配置选项默认值为NULL。此选项在MySQL 5.7.9中新增

• statement_truncate_len，@sys.statement_truncate_len：控制format_statement()函数返回的语句文本的最大长度。超过该长度的语句文本会被截断，只保留该配置选项定义的长度文本。默认值为64字节

其他选项可以被添加到sys_config表中。例如：如果存在debug配置选项且不为null值，则diagnostics()和execute_prepared_stmt()存储过程调用时会执行检查并做相应的判断，但默认情况下，此选项在sys_config表中不存在，因为debug输出通常只能临时启用，通过会话级别设置自定义配置选项变量实现，如：set @sys.debug='ON';

# 如果所有会话都需要使用，则可以将debug选项insert到sys_config表中
mysql> INSERT INTO sys_config (variable, value) VALUES('debug', 'ON');
# 要更改表中的调试配置选项值，可以使用update语句更新该配置选项值
## 首先，修改表中的值：
mysql> UPDATE sys_config SET value = 'OFF' WHERE variable = 'debug';
## 然后，为了确保当前会话中的存储过程调用时使用表中的更改后的值，需要将相应的用户定义的变量设置为NULL
mysql> SET @sys.debug = NULL;

记录内容示例

admin@localhost : sys 09:48:46> select * from sys_config;
+--------------------------------------+-------+---------------------+--------+
| variable                            | value | set_time            | set_by |
+--------------------------------------+-------+---------------------+--------+
| diagnostics.allow_i_s_tables        | OFF  | 2017-07-06 12:43:53 | NULL  |
| diagnostics.include_raw              | OFF  | 2017-07-06 12:43:53 | NULL  |
| ps_thread_trx_info.max_length        | 65535 | 2017-07-06 12:43:53 | NULL  |
| statement_performance_analyzer.limit | 100  | 2017-07-06 12:43:53 | NULL  |
| statement_performance_analyzer.view  | NULL  | 2017-07-06 12:43:53 | NULL  |
| statement_truncate_len              | 64    | 2017-07-06 12:43:53 | NULL  |
+--------------------------------------+-------+---------------------+--------+
6 rows in set (0.00 sec)

PS：对sys_config表的insert和update操作会触发sys_config_insert_set_user和sys_config_update_set_user触发器，而该触发器在5.7.x版本中新增了一个用户mysql.sys，且这俩触发器定义时指定了DEFINER=mysql.sys@localhost（表示该触发器只能用mysql.sys用户调用），so..该用户必须存在(对MySQL 做安全加固的小朋友要注意了，别直接对mysql.user表做truncate之类的操作，先看一眼表中存在着哪些用户)，否则对sys_config表操作时就算是超级管理员用户也无法修改（报错：ERROR 1449 (HY000): The user specified as a definer ('mysql.sys'@'localhost') does not exist），如果不小心删除了mysql.sys用户 ，可以使用如下语句重新创建(注意：使用create语句创建用户会失败，报错：ERROR 1396 (HY000): Operation CREATE USER failed for 'mysql.sys'@'localhost'，所以，强烈不建议删除mysql.sys用户，因为grant创建用户的语法即将废弃，当然，如果在不支持grant语句创建用户的MySQL版本中删了mysql.sys用户，也有办法补救，比如：直接insert用户权限表或者drop掉触发器再指定INVOKER=mysql.sys@localhost)

grant TRIGGER on sys.* to 'mysql.sys'@'localhost' identified by 'letsg0';
# 注意：mysql.sys用户初始化默认对表sys.sys_config表只有select权限，无法调用sys_config_insert_set_user和sys_config_update_set_user触发器完成更新set_by字段为当前操作用户名，会报错
# ERROR 1143 (42000): UPDATE command denied to user 'mysql.sys'@'localhost' for column 'set_by' in table 'sys_config'，所以要实现这个功能，针对sys.sys_config表还需要添加insert和update权限给mysql.sys用户
grant select,insert,update on sys.sys_config to 'mysql.sys'@'localhost' identified by 'letsg0';

2. sys_config_insert_set_user触发器

当对sys_config表执行INSERT语句添加配置选项行时，sys_config_insert_set_user触发器会将sys_config表的set_by列设置为当前用户名。

• 注意事项：要使得该触发器生效，有如下三个条件： 

  * mysql.sys用户必须存在，因为定义语句中DEFINER='mysql.sys'@'localhost' 表示只有该用户才能够调用该触发器，当然，为了方便，你可以删掉这个触发器，然后使用INVOKER='mysql.sys'@'localhost'子句创建 

* mysql.sys用户初始化默认对sys.sys_config表只有select权限，无法调用sys_config_insert_set_user和sys_config_update_set_user触发器完成更新set_by字段为当前操作用户名，会报错ERROR 1143 (42000): UPDATE command denied to user 'mysql.sys'@'localhost' for column 'set_by' in table 'sys_config'，所以要实现这个功能，针对sys.sys_config表还需要添加insert和update权限给mysql.sys用户 

* @sys.ignore_sys_config_triggers自定义变量必须为0值，任何非0值将导致该触发器不执行更新set_by字段操作

sys_config_insert_set_user触发器定义语句如下：

DROP TRIGGER IF EXISTS sys_config_insert_set_user;
DELIMITER $$
CREATE DEFINER='mysql.sys'@'localhost' TRIGGER sys_config_insert_set_user BEFORE INSERT on sys_config
FOR EACH ROW
BEGIN
IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN
    SET NEW.set_by = USER();
END IF;
END$$
DELIMITER ;

3. sys_config_update_set_user触发器

当对sys_config表执行UPDATE语句添加配置选项行时，sys_config_update_set_user触发器会将sys_config表的set_by列设置为当前用户名

• 注意事项：同sys_config_insert_set_user触发器注意事项

sys_config_update_set_user触发器定义语句如下：

DROP TRIGGER IF EXISTS sys_config_update_set_user;
DELIMITER $$
CREATE DEFINER='mysql.sys'@'localhost' TRIGGER sys_config_update_set_user BEFORE UPDATE on sys_config
FOR EACH ROW
BEGIN
IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN
    SET NEW.set_by = USER();
END IF;
END$$
DELIMITER ;

本期内容就介绍到这里，本期内容参考链接如下：

• https://dev.mysql.com/doc/refman/5.7/en/sys-sys-config-update-set-user.html

• https://dev.mysql.com/doc/refman/5.7/en/sys-schema-tables.html

• https://dev.mysql.com/doc/refman/5.7/en/sys-sys-config-insert-set-user.html

• https://dev.mysql.com/doc/refman/5.7/en/sys-sys-config.html

"翻过这座山，你就可以看到一片海！"。坚持阅读我们的"全方位认识 sys 系统库"系列文章分享，你就可以系统地学完它。 谢谢你的阅读，我们下期不见不散！

| 作者简介

罗小波·沃趣科技高级数据库技术专家

IT从业多年，历任运维工程师，高级运维工程师，运维经理，数据库工程师，曾参与版本发布系统，轻量级监控系统，运维管理平台，数据库管理平台的设计与编写，熟悉MySQL的体系结构时，InnoDB存储引擎，喜好专研开源技术，追求完美。

相关链接

更多干货，欢迎来撩~