MySQL Event monitoring log

Set up a monitoring table:

    CREATE TABLE ' t_event_history ' (    ID INT UNSIGNED not  NULL PRIMARY KEY auto_increment,      ' dbname ' VARCHAR (+) N  OT null default ' cat ',        ' eventname ' VARCHAR (+) NOT null default ' Bakupcat ',        ' starttime ' DATETIME NOT NULL default ' 2000-01-01 00:00:00 ',        ' endtime ' DATETIME default null,        ' issuccess ' int (one-by-one) default NULL,        ' duration ' int ( One) default null,        ' errormessage ' VARCHAR (+) default null,        ' Randno ' INT (one-by-one) default null    ) Engine=innodb DEFAULT Charset=utf8;  

　

Create a job based on the following build template

#请注意根据实际情况修改相关信息

DELIMITER $ $create definer= ' root ' @ '% ' EVENT ' job_backup_tb_cat ' on SCHEDULE every 1 day starts ' 2016-09-07 01:00:00 ' on CO   Mpletion not PRESERVE ENABLE does BEGIN DECLARE v_starttime DATETIME DEFAULT now ();      DECLARE v_randno INTEGER DEFAULT Floor (RAND () *100001);      DECLARE R_code CHAR (5) DEFAULT ' 00000 ';      DECLARE r_msg TEXT;    DECLARE V_error INTEGER;         INSERT into ' t_event_history ' (' starttime ', ' issuccess ', Randno) VALUES (V_starttime,0,v_randno);              Begin #异常处理段 DECLARE CONTINUE HANDLER for SQLEXCEPTION begin SET V_error = 1;          GET Diagnostics CONDITION 1 r_code = returned_sqlstate, r_msg = Message_text;  END;    #此处为实际调用的用户程序过程 call Backup_tb_cat ();   END; UPDATE t_event_history SET Endtime=now (), Issuccess=1,duration=timestampdiff (Second,starttime,now ()), errormessage=  CONCAT (' error= ', R_code, ', message= ', r_msg), Randno=null where Starttime=v_starttime and Randno=v_randno; end$ $DELIMITER;

　　

By querying the T_event_history table, we know when the event is executed, whether the execution is successful, the execution time, and the error message when the error occurs, providing great convenience for managing our daily scheduling schedule.

MySQL Event monitoring log