mysql事件日志_我怎样才能有MySQL日志安排的事件？

我将以下内容用于事件性能报告.

请注意,它可以处理您想要抛出的多个单独事件(例如,您单独编码的N个事件).

您在活动中的步骤由您决定.我在下面的事件中有一个参考,这里有一个未显示的表,以及我在这个业务定制的事件中做的事情.显示所有这些将使这个答案太长.如果您是一名活动程序员,您会发现它的用途.

此外,在您编写一些没有它们的简单事件之前,我的事件中的WHILE循环可能不是最好的.如果您没有安全地摆脱WHILE,您的活动将永远运行.所以,如果有的话,请记住这一点.

架构存根

下面的表由其代码顶部附近的事件使用,以对其执行插入,其唯一目的是获取唯一的ID以用于插入日志表.使用日期时间列等扩展它.它显示了一个简单的usedBy列来捕获至少一些东西,比如事件名称.主要是,它需要从它返回一个指定的auto_increment(id).

drop table if exists incarnations;

create table incarnations

( -- NoteA

-- a control table used to feed incarnation id's to events that want performance reporting.

-- The long an short of it, insert a row here merely to acquire an auto_increment id

id int auto_increment primary key,

usedBy varchar(50) not null

-- could use other columns perhaps, like how used or a datetime

-- but mainly it feeds back an auto_increment

-- the usedBy column is like a dummy column just to be fed a last_insert_id()

-- but the insert has to insert something, so we use usedBy

);

通用日志记录表如下：

drop table if exists EvtsLog;

create table EvtsLog

( id int auto_increment primary key,

incarnationId int not null, -- See NoteA (above)

evtName varchar(20) not null, -- allows for use of this table by multiple events

step int not null, -- facilitates reporting on event level performance

debugMsg varchar(1000) not null,

dtWhenLogged datetime not null

-- tweak this with whatever indexes your can bear to have

-- run maintenance on this table to rid it of unwanted rows periodically

-- as it impacts performance. So, dog the rows out to an archive table or whatever.

);

一个示例事件

-- Now the event kicks in on the server on its interval schedule and processes the batch.

-- But we need to modify that Event code because prior all it did was write a row to the log table

-- saying it was looking around. But it didn't actually do anything

drop event if exists `Every_2_Minutes_QuestionUpdateImport`;

DELIMITER $$

CREATE EVENT `Every_2_Minutes_QuestionUpdateImport`

ON SCHEDULE EVERY 2 MINUTE STARTS '2015-09-01 00:00:00'

ON COMPLETION PRESERVE

DO BEGIN

DECLARE bContinue bool default true;

DECLARE counter int default 0;

DECLARE logMsg varchar(1000);

DECLARE incarnationId int default 0;

DECLARE evtAlias varchar(20);

-- right here you could save `now()` into a variable, let's call it STARTEVT

set evtAlias:='ev2minQUI'; -- a shorter unique alias name, max 20 chars

-- Below we must acquire our incarnation id from the control table used for all events

-- that want to use it. It facilitates performance reporting with the use of the `steps` column and the datetime

-- that are put in the EvtsLog table

insert incarnations(usedBy) values (evtAlias); -- see NoteA

select last_insert_id() into incarnationId; -- an auto_increment handed to us by the control table

insert EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged)

select incarnationId,evtAlias,1,'Event Fired, begin looking',now(); -- 1: we are beginning

WHILE bContinue DO -- this is an intermediate-level skills event example. Be careful or you are stuck in the event forever

select min(batchId) into @idToPerform

from EvtsQuestionsToImportBatchHandler -- table not shown in this post on Stackoverflow

where batchStatus=1; -- @idToPerform, a variable, will be null if there is no such row

insert EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged)

select incarnationId,evtAlias,5,'Debug Place 1',now();

IF (@idToPerform IS NOT NULL) THEN

-- This next update line is very crucial, to mark the batch as underway and never picked up again

-- at the top of this WHILE loop (otherwise you may be stuck in here forever)

update EvtsQuestionsToImportBatchHandler set batchStatus=2,dtProcessBegan=now() where batchId=@idToPerform;

set counter:=counter+1; -- used outside of the while loop in the end

insert EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged)

select incarnationId,evtAlias,10,"a message maybe from concat and variables",now();

--

-- Here is where you actually do something

-- Here is where you actually do something

-- Here is where you actually do something

insert EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged)

select incarnationId,evtAlias,20,"a message maybe from concat and variables",now();

-- Here is where you actually do something

-- Here is where you actually do something

-- Here is where you actually do something

insert EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged)

select incarnationId,evtAlias,30,"a message maybe from concat and variables",now();

-- mark this batch as done:

update EvtsQuestionsToImportBatchHandler set batchStatus=3,dtProcessFinished=now() where batchId=@idToPerform;

ELSE

set bContinue=false; -- we are done with the event loop

END IF;

-- if bContinue is true, we will seek the next batch to process that has batchStatus=1, if there is one

-- right here a new now() could be performed, and a timediff() against the STARTEVT

-- to determine if you want to bail out also with a " set bContinue=false; "

END WHILE; -- this is an intermediate-level skills event example. Be careful or you are stuck in the event forever

-- this msg is crucial to see in the log table to know you are not locking in an endless WHILE loop

set logMsg:=concat("Number of batches processed=",counter); -- concat example (could have been used above)

insert EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged)

select incarnationId,evtAlias,90,logMsg,now(); -- 90: we are almost done

insert EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged)

select incarnationId,evtAlias,99,'Event Done',now(); -- 99: we are done

END$$

DELIMITER ; -- sets the delimiter back to what we are used to, the semi-colon

当然,您将获得针对包含incarnationId,evtN​​ame,step和datetime的日志表的性能报告.以下查询将多行事件信息折叠为每个事件运行1行,具有时间差.

select incarnationId,dtBegin,dtEnd,TIMESTAMPDIFF(SECOND,dtBegin,dtEnd) as secDiff

from

( select incarnationId,min(dtBegin) as dtBegin,max(dtEnd) as dtEnd

from

( select incarnationId,

case

when step=1 then dtWhenLogged

end as dtBegin,

case

when step=99 then dtWhenLogged

end as dtEnd

from evtsLog

where evtName='evtName01'

) d1

group by incarnationId

) d2;

+---------------+---------------------+---------------------+---------+

| incarnationId | dtBegin | dtEnd | secDiff |

+---------------+---------------------+---------------------+---------+

| 101 | 2016-05-01 14:02:00 | 2016-05-01 14:02:01 | 1 |

| 102 | 2016-05-01 14:02:01 | 2016-05-01 14:02:07 | 6 |

+---------------+---------------------+---------------------+---------+

要以微秒为单位进行更精确的报告,需要MySQL 5.6.4或更高版本.见answer.

写入事件很棘手,因为没有与之关联的UX.通过使用日志记录表,您不仅可以实现性能报告,还可以在开发过程中获得调试消息的洞察力.

不要忘记修剪日志表以控制其大小.也许可以将它存档到某个地方(也许通过另一个事件！).