MySQL存儲過程：批量為用戶授權

MySQL存儲過程：批量為用戶授權

編寫出這些腳本的需求是把慢查日志寫入數據庫中，方便查看。

1. 由於默認的mysql.slow_log表使用的是csv數據引擎，不支持對數據進行索引，所以需要將其修改為MyISAM引擎，並對query_time字段進行索引以優化查尋效率。

2. 需要對所有的用戶進行授權，讓大家要可通過調用 pub_getSlowQuery( limit ) 存儲過程獲取一天的慢查記錄數據。

3. 存儲過程命名約定：priv_ 起頭的為私有存儲過程，不需要對用戶授權，以pub_起頭的存儲過程對所有的會員進行授權，只允許運行，不可修改和刪除。

-- 修改慢查日志表結構，添加索引優化查尋速度
DROP PROCEDURE IF EXISTS `mysql`.`priv_setSlowLogEngine`;
DELIMITER $$
CREATE PROCEDURE `mysql`.`priv_setSlowLogEngine`() COMMENT '修改慢查設置'
BEGIN
    /** 關閉慢查記錄 */
    SET GLOBAL slow_query_log=0;
    /** 修改存儲方式 */
    SET GLOBAL log_output='TABLE';
    /** 記錄日志的執行時間 */
    SET GLOBAL long_query_time=3;
    /** 修改表引擎 */
    ALTER TABLE `mysql`.`slow_log` ENGINE=MYISAM;
    /** 添加索引 */
    ALTER TABLE `mysql`.`slow_log` ADD INDEX `query_time`(`query_time`);
    /** 開啟慢查記錄 */
    SET GLOBAL slow_query_log=1;
END$$
DELIMITER ;

-- 獲取慢查尋句子列表
DROP PROCEDURE IF EXISTS `mysql`.`pub_getSlowQuery`;
DELIMITER $$
CREATE PROCEDURE `mysql`.`pub_getSlowQuery`(IN top INT) COMMENT '獲取慢查記錄'
BEGIN
    /**
     * 昨天凌晨一點的時間
     * 業務需求是每天凌晨時間執行，所以是取昨天凌晨到當前時間的所有慢查日志 */
    DECLARE yesterday DATETIME;
    SELECT CONCAT_WS(' ', DATE_SUB(CURDATE(),INTERVAL 1 DAY), '00:00:00') INTO yesterday;
    SET @sql=CONCAT("SELECT * FROM `mysql`.`slow_log` WHERE `query_time`>0 ORDER BY `query_time` DESC LIMIT 0",top);
    /** 使用預處理執行SQL句子 */
    PREPARE m FROM @sql;
    EXECUTE m;
    DEALLOCATE PREPARE m;
END$$
DELIMITER ;

-- 授權操作
DROP PROCEDURE IF EXISTS `mysql`.`priv_grantToProcedure`;
DELIMITER $$
CREATE PROCEDURE `mysql`.`priv_grantToProcedure`( IN procedureName VARCHAR(30) ) COMMENT '對存儲過程授權'
BEGIN
    DECLARE not_found_data INT DEFAULT 0;
    DECLARE userName VARCHAR(20) DEFAULT '';
    DECLARE hostName VARCHAR(20) DEFAULT '';
    
    /**
     * 將用戶列表讀入游標 */
    DECLARE users CURSOR FOR SELECT `user`,`host` FROM mysql.user WHERE `user`!='csc86';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found_data=1;
    
    OPEN users;
    WHILE not_found_data=0 DO
        FETCH users INTO userName,hostName;
        SET @sql=CONCAT('GRANT Execute ON PROCEDURE `mysql`.`',procedureName,'` TO `',userName,'`@`',hostName,'`');
        
        /** 使用預處理執行SQL句子 */
        PREPARE m FROM @sql;
        EXECUTE m;
        DEALLOCATE PREPARE m;
    END WHILE;
    CLOSE users;
END$$
DELIMITER ;

-- 將mysql庫中以pub_開頭的存儲過程對所有用戶授權
DROP PROCEDURE IF EXISTS `mysql`.`priv_setPrivileges`;
DELIMITER $$
CREATE PROCEDURE `mysql`.`priv_setPrivileges`() COMMENT '設置調用存儲過程權限'
BEGIN
    /**
     * 游標 */
    DECLARE not_found_data INT DEFAULT 0;
    
    /**
     * 存儲過程名稱 */
    DECLARE proc_name VARCHAR(30) DEFAULT '';
    
    /**
     * 讀取所有公開的存儲過程 */
    DECLARE procedures CURSOR FOR SELECT `name` FROM `mysql`.`proc` WHERE `db`='mysql' AND `type`='PROCEDURE' AND `name` REGEXP '^pub_';
    
    /**
     * 到達游標尾部時，設置not_found_data為1 */
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found_data = 1;
    
    /**
     * 打開游標進入循環 */
     -- priv_grantToProcedure
    OPEN procedures;
    TRUNCATE TABLE mysql.`procs_priv`;
    WHILE not_found_data=0 DO
        FETCH procedures INTO proc_name;
        CALL priv_grantToProcedure( proc_name );
    END WHILE;
    /** 關閉游標 */
    CLOSE procedures;
    
    /** 刷新權限 */
    FLUSH PRIVILEGES;
END$$
DELIMITER ;