终于可以执行event了。调查半天原因,估计是文本编译器的问题。呵呵
DELETE FROM `mysql`.`event` where `mysql`.`event`.`name` = 'DO_BACKUP';
CREATE EVENT DO_BACKUP
ON SCHEDULE
EVERY 48 HOUR
STARTS concat(date_format(now(),'%Y-%m-%d'),' 00:00:00')
ON COMPLETION preserve ENABLE
DO
BEGIN
CALL `mysql`.`back_up_metrics`() ;
END
DELETE FROM `mysql`.`event` where `mysql`.`event`.`name` = 'DO_CLEAR';
CREATE EVENT DO_CLEAR
ON SCHEDULE
EVERY 2 QUARTER
STARTS concat(date_format(now(),'%Y-%m-%d'),' 00:00:00')
ON COMPLETION preserve ENABLE
DO
BEGIN
DELETE FROM `cloud_usage`.`host_metrics_history`;
DELETE FROM `cloud_usage`.`vm_metrics_history`;
DELETE FROM `cloud_usage`.`storage_metrics_history`;
END
DROP PROCEDURE IF EXISTS `mysql`.`back_up_metrics` ;
DELIMITER $$
CREATE PROCEDURE `mysql`.`back_up_metrics`()
BEGIN
DECLARE sdate DATETIME;
SET sdate = now() - INTERVAL 2 DAY;
INSERT INTO `cloud_usage`.`host_metrics_history` SELECT * FROM `cloud_usage`.`host_metrics` WHERE `cloud_usage`.`host_metrics`.`created` < sdate;
INSERT INTO `cloud_usage`.`vm_metrics_history` SELECT * FROM `cloud_usage`.`vm_metrics` WHERE `cloud_usage`.`vm_metrics`.`created` < sdate;
INSERT INTO `cloud_usage`.`storage_metrics_history` SELECT * FROM `cloud_usage`.`storage_metrics` WHERE `cloud_usage`.`storage_metrics`.`created` < sdate;
DELETE FROM `cloud_usage`.`host_metrics` WHERE `cloud_usage`.`host_metrics`.`created` < sdate;
DELETE FROM `cloud_usage`.`vm_metrics` WHERE `cloud_usage`.`vm_metrics`.`created` < sdate;
DELETE FROM `cloud_usage`.`storage_metrics` WHERE `cloud_usage`.`storage_metrics`.`created` < sdate;
END ;$$
DELIMITER ;
DELETE FROM `mysql`.`event` where `mysql`.`event`.`name` = 'DO_BACKUP';
CREATE EVENT DO_BACKUP
ON SCHEDULE
EVERY 48 HOUR
STARTS concat(date_format(now(),'%Y-%m-%d'),' 00:00:00')
ON COMPLETION preserve ENABLE
DO
BEGIN
CALL `mysql`.`back_up_metrics`() ;
END
DELETE FROM `mysql`.`event` where `mysql`.`event`.`name` = 'DO_CLEAR';
CREATE EVENT DO_CLEAR
ON SCHEDULE
EVERY 2 QUARTER
STARTS concat(date_format(now(),'%Y-%m-%d'),' 00:00:00')
ON COMPLETION preserve ENABLE
DO
BEGIN
DELETE FROM `cloud_usage`.`host_metrics_history`;
DELETE FROM `cloud_usage`.`vm_metrics_history`;
DELETE FROM `cloud_usage`.`storage_metrics_history`;
END
DROP PROCEDURE IF EXISTS `mysql`.`back_up_metrics` ;
DELIMITER $$
CREATE PROCEDURE `mysql`.`back_up_metrics`()
BEGIN
DECLARE sdate DATETIME;
SET sdate = now() - INTERVAL 2 DAY;
INSERT INTO `cloud_usage`.`host_metrics_history` SELECT * FROM `cloud_usage`.`host_metrics` WHERE `cloud_usage`.`host_metrics`.`created` < sdate;
INSERT INTO `cloud_usage`.`vm_metrics_history` SELECT * FROM `cloud_usage`.`vm_metrics` WHERE `cloud_usage`.`vm_metrics`.`created` < sdate;
INSERT INTO `cloud_usage`.`storage_metrics_history` SELECT * FROM `cloud_usage`.`storage_metrics` WHERE `cloud_usage`.`storage_metrics`.`created` < sdate;
DELETE FROM `cloud_usage`.`host_metrics` WHERE `cloud_usage`.`host_metrics`.`created` < sdate;
DELETE FROM `cloud_usage`.`vm_metrics` WHERE `cloud_usage`.`vm_metrics`.`created` < sdate;
DELETE FROM `cloud_usage`.`storage_metrics` WHERE `cloud_usage`.`storage_metrics`.`created` < sdate;
END ;$$
DELIMITER ;