要求:
每月1日0点:在不影响业务的情况下,备份整月的数据,保留6次备份。
思路:
基于MYSQL事件功能,每月按时完成操作
RENAME语句具有原子性,新旧表无缝切换
RENAME语句仅修改表定义,大表瞬间完成
基于上面三点,实现了表数据按月切割
步骤:
新建一个待替换的空表
重命名原表为日期备份表,空表为原表
删除6次前备份的那张表
参考官方文章
https://dev.mysql.com/doc/refman/5.6/en/rename-table.html
前提要求:打开MYSQL的事件功能
打开事件功能
set global event_scheduler = on;
查看事件功能是否打开
show variables like 'event_scheduler';
MYSQL事件实现
DELIMITER ;;
CREATE EVENT BACKUP
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
select now();
END
;;
DELIMITER ;
返回日期
CURDATE()
返回前一天的数字
DAY(CURDATE()-1)
日期减法函数:计算当天减去月初到昨天的天数,等于这个月的1号
DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY)
日期加法函数:得到下月1号的日期
DATE_ADD
DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY)
日期加法函数:得到下月1号的凌晨1点的时间
DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)
实现细节
创建表实现
CREATE TABLE `version_replace` (
`table_name` varchar(32) NOT NULL,
`table_version` int(10) unsigned NOT NULL DEFAULT '0',
UNIQUE KEY `table_name_idx` (`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
动态SQL语句实现改表名称为日期变量
SET @command=concat('RENAME TABLE `version` TO `version_backup_',date_format(now(),'%y%m'),'` , `version_replace` TO `version`');
PREPARE modify FROM @command;
EXECUTE modify;
动态SQL语句实现删除6次之前的备份
SET @command=concat('DROP TABLE version_backup_',date_format(date_sub(now(), interval 6 month),'%y%m'),'');
PREPARE modify FROM @command;
EXECUTE modify;
获取格式化后的年月份
select date_format(now(),'%y%m');
获取当前日期的前6个月
select date_sub(now(), interval 6 month);
取得当前日期的前6个月,并格式化显示,为拼接表名称做准备
select date_format(date_sub(now(), interval 6 month),'%y%m');
实现细节
最终实现
DELIMITER ;;
CREATE EVENT BACKUP
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 0 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
/*创建一个待替换的空表*/
CREATE TABLE `version_replace` (
`table_name` varchar(32) NOT NULL,
`table_version` int(10) unsigned NOT NULL DEFAULT '0',
UNIQUE KEY `table_name_idx` (`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*RENAME语句具有原子性,把原表重命名为日期备份表,并且将创建的新表重命名为原表*/
SET @command=concat('RENAME TABLE `version` TO `version_backup_',date_format(now(),'%y%m'),'` , `version_replace` TO `version`');
PREPARE modify FROM @command;
EXECUTE modify;
/*删除6个月前的那次备份*/
SET @command=concat('DROP TABLE version_backup_',date_format(date_sub(now(), interval 6 month),'%y%m'),'');
PREPARE modify FROM @command;
EXECUTE modify;
END
;;
DELIMITER ;
文章写的不错?请扫下面作者的讨饭专用码,赞助一下。
注意:本文来源网络/媒体,本站无法对本文内容的真实性、完整性、及时性、原创性提供任何保证,
请您自行验证核实并承担相关的风险与后果!
CoLaBug.com遵循[CC BY-SA 4.0]分享并保持客观立场,本站不承担此类作品侵权行为的直接责任及连带责任。
如您有版权、意见、投诉等问题,请通过[eMail]联系我们处理,如需商业授权请联系原作者/原网站。