ps_setup_reset_to_default()

• 重置performance_schema配置为默认值，对于setup_actors表直接清空重新插入(使用delete，INSERT IGNORE INTO语句)，对于setup_instruments表，通过查询sys.ps_is_instrument_default_enabled(NAME)和sys.ps_is_instrument_default_timed(NAME)函数返回event_name的默认值来执行更新配置表(使用update语句)，对于setup_consumers表直接使用IF(NAME IN (''xxx'',...), ''YES'', ''NO'')函数返回值更新配置表(使用update语句)，对于setup_objects表delete ... where not in ('xxx'...)来删除除了默认配置行之外的配置行，然后按照默认的配置行字段值重新更新这些默认配置行(使用delete，INSERT IGNORE INTO语句)，对于threads表，更新所有线程的INSTRUMENTED字段为YES(使用update语句)

• 参数：

  • in_verbose BOOLEAN：是否在该存储过程执行期间显示每个配置表还原的阶段信息，其中包括执行更新配置表的SQL语句

• 定义语句

DROP PROCEDURE IF EXISTS ps_setup_reset_to_default;

DELIMITER $$

CREATE DEFINER='root'@'localhost' PROCEDURE ps_setup_reset_to_default (
      IN in_verbose BOOLEAN
    )
    COMMENT '
            Description
            -----------

            Resets the Performance Schema setup to the default settings.

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

            in_verbose (BOOLEAN):
              Whether to print each setup stage (including the SQL) whilst running.

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

            mysql> CALL sys.ps_setup_reset_to_default(true)\\G
            *************************** 1. row ***************************
            status: Resetting: setup_actors
            DELETE
            FROM performance_schema.setup_actors
              WHERE NOT (HOST = \'%\' AND USER = \'%\' AND ROLE = \'%\')
            1 row in set (0.00 sec)

            *************************** 1. row ***************************
            status: Resetting: setup_actors
            INSERT IGNORE INTO performance_schema.setup_actors
            ...

            mysql> CALL sys.ps_setup_reset_to_default(false)\\G
            Query OK, 0 rows affected (0.00 sec)
            '
    SQL SECURITY INVOKER
    NOT DETERMINISTIC
    MODIFIES SQL DATA
BEGIN
    SET @query = 'DELETE
                    FROM performance_schema.setup_actors
                  WHERE NOT (HOST = ''%'' AND USER = ''%'' AND ROLE = ''%'')';

    IF (in_verbose) THEN
        SELECT CONCAT('Resetting: setup_actors\n', REPLACE(@query, '  ', '')) AS status;
    END IF;

    PREPARE reset_stmt FROM @query;
    EXECUTE reset_stmt;
    DEALLOCATE PREPARE reset_stmt;

    SET @query = 'INSERT IGNORE INTO performance_schema.setup_actors
                  VALUES (''%'', ''%'', ''%'')';

    IF (in_verbose) THEN
        SELECT CONCAT('Resetting: setup_actors\n', REPLACE(@query, '  ', '')) AS status;
    END IF;

    PREPARE reset_stmt FROM @query;
    EXECUTE reset_stmt;
    DEALLOCATE PREPARE reset_stmt;

    SET @query = 'UPDATE performance_schema.setup_instruments
                    SET ENABLED = sys.ps_is_instrument_default_enabled(NAME),
                        TIMED  = sys.ps_is_instrument_default_timed(NAME)';

    IF (in_verbose) THEN
        SELECT CONCAT('Resetting: setup_instruments\n', REPLACE(@query, '  ', '')) AS status;
    END IF;

    PREPARE reset_stmt FROM @query;
    EXECUTE reset_stmt;
    DEALLOCATE PREPARE reset_stmt;

    SET @query = 'UPDATE performance_schema.setup_consumers

    IF (in_verbose) THEN
        SELECT CONCAT('Resetting: setup_consumers\n', REPLACE(@query, '  ', '')) AS status;
    END IF;

    PREPARE reset_stmt FROM @query;
    EXECUTE reset_stmt;
    DEALLOCATE PREPARE reset_stmt;

    SET @query = 'DELETE
                    FROM performance_schema.setup_objects
                  WHERE NOT (OBJECT_TYPE = ''TABLE'' AND OBJECT_NAME = ''%''
                    AND (OBJECT_SCHEMA = ''mysql''              AND ENABLED = ''NO''  AND TIMED = ''NO'' )
                      OR (OBJECT_SCHEMA = ''performance_schema'' AND ENABLED = ''NO''  AND TIMED = ''NO'' )
                      OR (OBJECT_SCHEMA = ''information_schema'' AND ENABLED = ''NO''  AND TIMED = ''NO'' )
                      OR (OBJECT_SCHEMA = ''%''                  AND ENABLED = ''YES'' AND TIMED = ''YES''))';

    IF (in_verbose) THEN
        SELECT CONCAT('Resetting: setup_objects\n', REPLACE(@query, '  ', '')) AS status;
    END IF;

    PREPARE reset_stmt FROM @query;
    EXECUTE reset_stmt;
    DEALLOCATE PREPARE reset_stmt;

    SET @query = 'INSERT IGNORE INTO performance_schema.setup_objects
                  VALUES (''TABLE'', ''mysql''            , ''%'', ''NO'' , ''NO'' ),
                        (''TABLE'', ''performance_schema'', ''%'', ''NO'' , ''NO'' ),
                        (''TABLE'', ''information_schema'', ''%'', ''NO'' , ''NO'' ),
                        (''TABLE'', ''%''                , ''%'', ''YES'', ''YES'')';

    IF (in_verbose) THEN
        SELECT CONCAT('Resetting: setup_objects\n', REPLACE(@query, '  ', '')) AS status;
    END IF;

    PREPARE reset_stmt FROM @query;
    EXECUTE reset_stmt;
    DEALLOCATE PREPARE reset_stmt;

    SET @query = 'UPDATE performance_schema.threads
                    SET INSTRUMENTED = ''YES''';

    IF (in_verbose) THEN
        SELECT CONCAT('Resetting: threads\n', REPLACE(@query, '  ', '')) AS status;
    END IF;

    PREPARE reset_stmt FROM @query;
    EXECUTE reset_stmt;
    DEALLOCATE PREPARE reset_stmt;
END$$

DELIMITER ;

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

---

上一篇： ps_setup_reload_saved()存储过程 | 下一篇： ps_setup_save()存储过程