Mysql事件

一、数据库事件状态，ON|1开启，OFF|0关闭

show variables like '%sche%';

如果查询不到变量，说明数据库版本过低，不支持事件。
#启动事件，由于这是全局变量，需要root用户才能执行
#下面的方法在数据库关闭或者服务重启时就会失效，需要重新设置。
set global event_scheduler=1;
#如果想要每次重启数据库服务后，事件仍然生效需要在mysql.ini文件中加入event_scheduler = ON; 的语句
#查询数据库所有创建的事件
select * from mysql.event;
#开启事件任务
alter event event_name enable;
alter event event_name on completion perserve enable;
#关闭事件任务
alter event event_name disable;
alter event event_name on completion perserve disable;
#查看事件的定义

show create event event_name;

二、创建事件
#创建事件语法格式

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;

schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

#event_name表示事件名称
#schedule表示触发点，AT timestamp一般用于只执行一次。EVERY interval一般用于周期性执行，可以设定开始时间和结束时间。
#ON COMPLETION PRESERVE：表示任务执行之后仍保留
#ON COMPLETION NOT PRESERVE：表示任务执行完成后不保留
#ENABLE|DISABLE：表示设置启用或者禁止这个事件。
#COMMENT：添加注释
#修改事件的语法格式

ALTER EVENT event_name  
[ON SCHEDULE schedule]  
[RENAME TO new_event_name]  
[ON COMPLETION [NOT] PRESERVE]  
[COMMENT 'comment']  
[ENABLE | DISABLE]  
[DO sql_statement]

三、创建事例

#创建事件日志表，记录事件执行时间

CREATE TABLE `event_log` (                                                                                                                                                                                                                                                                                                                                                                                                                                                
             `event_name` varchar(32) COLLATE utf8_bin DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                 
             `sp_name` varchar(32) COLLATE utf8_bin DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                    
             `func_name` varchar(32) COLLATE utf8_bin DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                  
             `table_name` varchar(32) COLLATE utf8_bin DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                 
             `start_time` varchar(32) COLLATE utf8_bin DEFAULT NULL,                                                                                                                                                                                                                                                                                                                                                                                                                 
             `end_time` varchar(32) COLLATE utf8_bin DEFAULT NULL                                                                                                                                                                                                                                                                                                                                                                                                                    
           ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='record event execute log'

#删除指定事件（先单独执行这一条）
#DROP event IF EXISTS event_update_store_type_analysis_model ;

#设置数据库启用事件
SET GLOBAL event_scheduler=1 ;
#指定创建事件的用户test
USE test ;
#分隔符$$和分号;区分开来，否则会报错，因为mysql识别分号作为结束符，和编写的语句中的分号冲突
DELIMITER $$
#创建事件event_name
CREATE event event_name
#指定事件执行的周期每个月的9号凌晨1点执行
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-9 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)
#事件执行完成后不删除
ON COMPLETION PRESERVE 
#启用该事件
ENABLE 
DO 
BEGIN
	SET @startTime=DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
#该事件的处理逻辑就是调一个存储过程，也可以直接编写语句。
#将需要执行的处理逻辑放到不同的存储过程中，可以在同一事件中同时调多个存储过程，避免为每一个任务编写单独的事件
	CALL sp_name();
	SET @endTime=DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
#事件执行详情记录日志
	INSERT INTO event_log
	     VALUES('event_name',
	            'sp_name',
	            '',
	            'table_name',
	             @startTime,
	             @endTime);
END $$
DELIMITER ;

四、涉及到主从复制时注意事项
在生产环境中，使用event时，注意在主库上开启定时器，从库上关闭定时器，event触发所有操作均会记录binlog进行主从同步，从库上开启定时器很可能造成卡库。切换主库后之后记得将新主库上的定时器打开。

五、遇到的问题
事件执行的时间点总是比创建事件指定的执行时间早8个小时

使用show variables like 'time_zone';看到的时区是SYSTEM，和系统时区相同。
使用cat /etc/sysconfig/clock查看系统时区是ZONE="Asia/ShangHai"，也没问题。
这时事件执行却和系统时间相差8小时，有两种修改方式
1）通过设置时区这个全局变量，将其加8小时：SET GLOBAL time_zone = '+8:00';
不过这种方式在mysql数据库服务重启后会失效，需再此设置
2）在mysql配置文件my.conf或者my.ini（根据mysql版本不同，配置文件名不同）[mysqld]下面添加default-time-zone=+8:00
这种方式随mysql服务器启动生效。
还有最简单的方式：创建任务时设置时间周期比预期的晚8小时。。。