|
需求
SQL Server的作業(yè)歷史（Job）記錄是保存在msdb庫中的，很方便就查詢相關(guān)的Job定義，計(jì)劃和歷史記錄，而MySQL的event卻沒有歷史記錄。為方便查看event是否正常執(zhí)行以及執(zhí)行結(jié)果，通過以下兩個(gè)步驟來實(shí)現(xiàn)類似的功能。
實(shí)現(xiàn)
1. 在mysql庫創(chuàng)建event執(zhí)行的歷史記錄表
CREATE TABLE `mysql`.`udf_event_history` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `event_gid` varchar(36) NOT NULL, `db_name` varchar(128) NOT NULL DEFAULT '', `event_name` varchar(128) NOT NULL DEFAULT '', `start_time` datetime(3) NOT NULL DEFAULT current_timestamp(), `end_time` datetime(3) DEFAULT NULL, `is_success` tinyint(4) DEFAULT 0, `duration` decimal(15,3) DEFAULT NULL, `error_msg` varchar(512) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_event_git` (`event_gid`), KEY `idx_db_event_name` (`db_name`,`event_name`), KEY `idx_s_e_time` (`start_time`,`end_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2．根據(jù)以下建模板創(chuàng)建event 請(qǐng)注意根據(jù)實(shí)際情況修改相關(guān)信息
USE db1; DELIMITER $$ CREATE DEFINER=`root`@`localhost` EVENT `event_test1` ON SCHEDULE EVERY 1 MINUTE STARTS '2019-01-01 00:00:00' ON COMPLETION PRESERVE ENABLE DO BEGIN DECLARE r_code CHAR(5) DEFAULT '00000'; DECLARE r_msg TEXT; DECLARE v_error INT; DECLARE v_start_time DATETIME(3) DEFAULT NOW(3); DECLARE v_event_gid VARCHAR(36) DEFAULT UPPER(REPLACE(UUID(),'-','')); /*修改為實(shí)際的event名*/ INSERT INTO mysql.udf_event_history (db_name, event_name, start_time, event_gid) VALUES(DATABASE(), 'event_test1', v_start_time, v_event_gid); BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN SET v_error = 1; GET DIAGNOSTICS CONDITION 1 r_code = RETURNED_SQLSTATE, r_msg = MESSAGE_TEXT; END; /*實(shí)際要執(zhí)行語句或存儲(chǔ)過程，等*/ CALL db1.usp_1(); END; UPDATE mysql.udf_event_history SET end_time = NOW(3), is_success = ISNULL(v_error), duration = TIMESTAMPDIFF(microsecond,start_time, NOW(3)) / 1000000, error_msg = CONCAT('error = ', r_code,', message = ', r_msg) WHERE event_gid = v_event_gid; END$$ DELIMITER ;
通過查詢mysql.udf_event_history表，可以知道event的開始結(jié)束時(shí)間、是否成功、執(zhí)行時(shí)長、錯(cuò)誤信息，等，為管理日常調(diào)度計(jì)劃提供方便。 root@localhost [db1]select * from mysql.udf_event_history limit 2\G; *************************** 1. row *************************** id: 1 db_name: db1 event_name: event_test1 start_time: 2019-12-03 15:44:00.000 end_time: 2019-12-03 15:44:00.001 is_success: 0 duration: 0.001 error_msg: error = 42000, message = PROCEDURE db1.usp_2 does not exist event_gid: AB305D8C15A011EAB822005056AB041E *************************** 2. row *************************** id: 2 db_name: db1 event_name: event_test2 start_time: 2019-12-03 15:46:00.000 end_time: 2019-12-03 15:46:05.405 is_success: 1 duration: 5.405 error_msg: NULL event_gid: F2B6197C15A011EAB822005056AB041E
參考 |
|
|
