开源数据库MySQL DBA运维实战1

-- 创建一个新的存储过程，用于检查MySQL复制的健康状况
DELIMITER $$

CREATE PROCEDURE `sp_check_replication_health`()
BEGIN
    -- 检查复制的状态
    SELECT
        CHANNEL_NAME,
        STATE,
        COUNT_TRANSACTIONS_IN_QUEUE AS queue_length
    FROM
        performance_schema.replication_group_members;

    -- 检查错误日志中的复制错误
    SELECT
        COUNT(*)
    FROM
        mysql.general_log
    WHERE
        command_type = 'Replication' AND
        argument LIKE '%Error_code:%';

    -- 检查复制延迟
    SELECT
        CHANNEL_NAME,
        COUNT_TRANSACTIONS_BEHIND_MASTER AS transaction_delay
    FROM
        performance_schema.replication_group_members
    WHERE
        COUNT_TRANSACTIONS_BEHIND_MASTER > 0;
END$$

DELIMITER ;