如何限制使用者修改long_query_time

GreatSQL發表於2024-10-30

如何限制使用者修改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掉。

相關係統表：

#performance_schema下的系統表
#儲存每個會話的會話級系統變數
greatsql> SHOW CREATE TABLE variables_by_thread\G
*************************** 1. row ***************************
       Table: variables_by_thread
Create Table: CREATE TABLE `variables_by_thread` (
  `THREAD_ID` bigint unsigned NOT NULL,
  `VARIABLE_NAME` varchar(64) NOT NULL,
  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`THREAD_ID`,`VARIABLE_NAME`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

#儲存全域性的系統變數
greatsql> SHOW CREATE TABLE global_variables\G
*************************** 1. row ***************************
       Table: global_variables
Create Table: CREATE TABLE `global_variables` (
  `VARIABLE_NAME` varchar(64) NOT NULL,
  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`VARIABLE_NAME`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

event程式的實現：

DELIMITER $$
CREATE EVENT check_session_long_query_time
ON SCHEDULE EVERY 5 SECOND
DO
BEGIN
    DECLARE v_processlist_id BIGINT UNSIGNED;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR
        SELECT t.PROCESSLIST_ID
        FROM performance_schema.variables_by_thread v
        inner join performance_schema.threads t on v.thread_id=t.thread_id
        WHERE v.VARIABLE_NAME = 'long_query_time' 
        AND v.VARIABLE_VALUE != (select VARIABLE_VALUE from performance_schema.global_variables where 
        VARIABLE_NAME = 'long_query_time' ) ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_processlist_id ;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 終止連線
        KILL v_processlist_id ;
    END LOOP;
    CLOSE cur;
END$$
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 😃

關於 GreatSQL

GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。

相關連結： GreatSQL社群 Gitee GitHub Bilibili

GreatSQL社群：

社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html

image-20230105161905827

技術交流群：

微信：掃碼新增GreatSQL社群助手微信好友，傳送驗證資訊加群。

image-20221030163217640

相關文章