如何限制用户修改long_query_time

微微凉的黎明

如何限制用户修改long_query_time

需求来源

数据库的 long_query_time 设置了写入慢查询日志的SQL语句执行时长的阈值，当应用系统修改为很小的值或0时，会在数据库的慢查询日志中记录大量SQL语句，导致数据库性能降低和占用磁盘空间的快速增长。
GreatSQL 对于影响整个数据库会话级变量设置为受限会话变量（如:binlog_format,sql_log_bin和sql_log_off），同时增加了用户权限 SESSION_VARIABLES_ADMIN，只有授予了 SESSION_VARIABLES_ADMIN 的用户才能更改这些受限会话变量。
但 long_query_time 不在受限会话变量中，该如何限制应用程序修改 long_query_time 呢？
解决方法

在 performance_schema 中有表 variables_by_thread 存储了每个活动会话的会话级系统变量。可以编写一个event定时检查用户的long_query_time设置，如果与全局的long_query_time变量值不同，将该会话kill掉。
相关系统表：

1. #performance_schema下的系统表
2. #存储每个会话的会话级系统变量
3. greatsql> SHOW CREATE TABLE variables_by_thread\G
4. *************************** 1. row ***************************
5.        Table: variables_by_thread
6. Create Table: CREATE TABLE `variables_by_thread` (
7.   `THREAD_ID` bigint unsigned NOT NULL,
8.   `VARIABLE_NAME` varchar(64) NOT NULL,
9.   `VARIABLE_VALUE` varchar(1024) DEFAULT NULL,
10.   PRIMARY KEY (`THREAD_ID`,`VARIABLE_NAME`)
11. ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
12. 1 row in set (0.00 sec)
14. #存储全局的系统变量
15. greatsql> SHOW CREATE TABLE global_variables\G
16. *************************** 1. row ***************************
17.        Table: global_variables
18. Create Table: CREATE TABLE `global_variables` (
19.   `VARIABLE_NAME` varchar(64) NOT NULL,
20.   `VARIABLE_VALUE` varchar(1024) DEFAULT NULL,
21.   PRIMARY KEY (`VARIABLE_NAME`)
22. ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
23. 1 row in set (0.00 sec)

复制代码

event程序的实现：

1. DELIMITER $$
2. CREATE EVENT check_session_long_query_time
3. ON SCHEDULE EVERY 5 SECOND
4. DO
5. BEGIN
6.     DECLARE v_processlist_id BIGINT UNSIGNED;
7.     DECLARE done INT DEFAULT FALSE;
8.     DECLARE cur CURSOR FOR
9.         SELECT t.PROCESSLIST_ID
10.         FROM performance_schema.variables_by_thread v
11.         inner join performance_schema.threads t on v.thread_id=t.thread_id
12.         WHERE v.VARIABLE_NAME = 'long_query_time'
13.         AND v.VARIABLE_VALUE != (select VARIABLE_VALUE from performance_schema.global_variables where
14.         VARIABLE_NAME = 'long_query_time' ) ;
15.     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
16.     OPEN cur;
17.     read_loop: LOOP
18.         FETCH cur INTO v_processlist_id ;
19.         IF done THEN
20.             LEAVE read_loop;
21.         END IF;
22.         -- 终止连接
23.         KILL v_processlist_id ;
24.     END LOOP;
25.     CLOSE cur;
26. END$$
27. DELIMITER ;

复制代码

总结

通过kill会话来限制用户修改会话级变量，有些暴力，但也是DBA的无奈手段。较好的方式是修改受限系统变量实现方法，将受限的会话变量做成一个可动态添加的列表，如在某个系统表中予以存储，DBA可以通过添加和删除数据行来动态修改受限会话变量。MySQL开源版本也存在同样的问题，MySQL 社区已确认作者提的feature request《Optimize the handling of SESSION_VARIABLES_ADMIN permission(https://bugs.mysql.com/bug.php?id=115944)》。

Enjoy GreatSQL
来源:https://www.cnblogs.com/greatsql/p/18515195
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！