在上一篇《初相识 | 全方位认识 sys 系统库》中,我们针对sys 系统库做了一个不痛不痒的开端,是不是觉得太简单了?别急,本期我们将为大家带来系列第二篇《配置表 | 全方位认识 sys 系统库》,让你一次性重新找回学习performance_schema时的感觉,下面请跟随我们一起开始 sys 系统库的系统学习之旅吧。
该表包含sys系统库的配置选项,每个配置选项一行记录。该表是innodb表,可以通过客户端更新此表来持久化配置,server重启不会丢失。
sys_config表字段含义如下:
为了减少对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表中的选项和相应的用户定义的配置选项变量相关描述如下:
其他选项可以被添加到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';
当对sys_config表执行INSERT语句添加配置选项行时,sys_config_insert_set_user触发器会将sys_config表的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 ;
当对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
罗小波·沃趣科技高级数据库技术专家
IT从业多年,历任运维工程师,高级运维工程师,运维经理,数据库工程师,曾参与版本发布系统,轻量级监控系统,运维管理平台,数据库管理平台的设计与编写,熟悉MySQL的体系结构时,InnoDB存储引擎,喜好专研开源技术,追求完美。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。