ps_setup_reload_saved()

• 调用该存储过程时，会重新加载之前调用ps_setup_save()存储过程时保存在临时表中的threads、setup_consumers、setup_instruments、setup_actors配置信息，调用该存储过程依赖于在相同会话中之前调用ps_setup_save()存储过程创建的配置备份临时表，如果之前没有调用过ps_setup_save()存储过程，该存储过程无法执行

  • 该存储过程执行需要有SUPER权限，因为执行期间会修改sql_log_bin系统变量禁用二进制日志记录功能

• 定义语句

DROP PROCEDURE IF EXISTS ps_setup_reload_saved;

DELIMITER $$

CREATE DEFINER='root'@'localhost' PROCEDURE ps_setup_reload_saved ()
    COMMENT '
            Description
            -----------

            Reloads a saved Performance Schema configuration,
            so that you can alter the setup for debugging purposes, 
            but restore it to a previous state.
            
            Use the companion procedure - ps_setup_save(), to 
            save a configuration.

            Requires the SUPER privilege for "SET sql_log_bin = 0;".

            Parameters
            -----------

            None.

            Example
            -----------

            mysql> CALL sys.ps_setup_save();
            Query OK, 0 rows affected (0.08 sec)

            mysql> UPDATE performance_schema.setup_instruments SET enabled = \'YES\', timed = \'YES\';
            Query OK, 547 rows affected (0.40 sec)
            Rows matched: 784  Changed: 547  Warnings: 0

            /* Run some tests that need more detailed instrumentation here */

            mysql> CALL sys.ps_setup_reload_saved();
            Query OK, 0 rows affected (0.32 sec)
            '
    SQL SECURITY INVOKER
    NOT DETERMINISTIC
    MODIFIES SQL DATA
BEGIN
    DECLARE v_done bool DEFAULT FALSE;
    DECLARE v_lock_result INT;
    DECLARE v_lock_used_by BIGINT;
    DECLARE v_signal_message TEXT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SIGNAL SQLSTATE VALUE '90001'
          SET MESSAGE_TEXT = 'An error occurred, was sys.ps_setup_save() run before this procedure?';
    END;

    SET @log_bin := @@sql_log_bin;
    SET sql_log_bin = 0;

    SELECT IS_USED_LOCK('sys.ps_setup_save') INTO v_lock_used_by;

    IF (v_lock_used_by != CONNECTION_ID()) THEN
        SET v_signal_message = CONCAT('The sys.ps_setup_save lock is currently owned by ', v_lock_used_by);
        SIGNAL SQLSTATE VALUE '90002'
          SET MESSAGE_TEXT = v_signal_message;
    END IF;

    DELETE FROM performance_schema.setup_actors;
    INSERT INTO performance_schema.setup_actors SELECT * FROM tmp_setup_actors;

    BEGIN
        -- Workaround for http://bugs.mysql.com/bug.php?id=70025
        DECLARE v_name varchar(64);
        DECLARE v_enabled enum('YES', 'NO');
        DECLARE c_consumers CURSOR FOR SELECT NAME, ENABLED FROM tmp_setup_consumers;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

        SET v_done = FALSE;
        OPEN c_consumers;
        c_consumers_loop: LOOP
            FETCH c_consumers INTO v_name, v_enabled;
            IF v_done THEN
              LEAVE c_consumers_loop;
            END IF;

            UPDATE performance_schema.setup_consumers
              SET ENABLED = v_enabled
            WHERE NAME = v_name;
        END LOOP;
        CLOSE c_consumers;
    END;

    UPDATE performance_schema.setup_instruments
    INNER JOIN tmp_setup_instruments USING (NAME)
      SET performance_schema.setup_instruments.ENABLED = tmp_setup_instruments.ENABLED,
          performance_schema.setup_instruments.TIMED  = tmp_setup_instruments.TIMED;
    BEGIN
        -- Workaround for http://bugs.mysql.com/bug.php?id=70025
        DECLARE v_thread_id bigint unsigned;
        DECLARE v_instrumented enum('YES', 'NO');
        DECLARE c_threads CURSOR FOR SELECT THREAD_ID, INSTRUMENTED FROM tmp_threads;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

        SET v_done = FALSE;
        OPEN c_threads;
        c_threads_loop: LOOP
            FETCH c_threads INTO v_thread_id, v_instrumented;
            IF v_done THEN
              LEAVE c_threads_loop;
            END IF;

            UPDATE performance_schema.threads
              SET INSTRUMENTED = v_instrumented
            WHERE THREAD_ID = v_thread_id;
        END LOOP;
        CLOSE c_threads;
    END;

    UPDATE performance_schema.threads
      SET INSTRUMENTED = IF(PROCESSLIST_USER IS NOT NULL,
                            sys.ps_is_account_enabled(PROCESSLIST_HOST, PROCESSLIST_USER),
                            'YES')
    WHERE THREAD_ID NOT IN (SELECT THREAD_ID FROM tmp_threads);

    DROP TEMPORARY TABLE tmp_setup_actors;
    DROP TEMPORARY TABLE tmp_setup_consumers;
    DROP TEMPORARY TABLE tmp_setup_instruments;
    DROP TEMPORARY TABLE tmp_threads;

    SELECT RELEASE_LOCK('sys.ps_setup_save') INTO v_lock_result;

    SET sql_log_bin = @log_bin;
END$$

DELIMITER ;

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-ps-setup-reload-saved.html

---

上一篇： ps_setup_enable_thread()存储过程 | 下一篇： ps_setup_reset_to_default()存储过程