/**
.::::.
.::::::::.
::::::::::: 佛主保佑、永无Bug
..:::::::::::'
'::::::::::::'
.::::::::::
'::::::::::::::..
..::::::::::::.
``::::::::::::::::
::::``:::::::::' .:::.
::::' ':::::' .::::::::.
.::::' :::: .:::::::'::::.
.:::' ::::: .:::::::::' ':::::.
.::' :::::.:::::::::' ':::::.
.::' ::::::::::::::' ``::::.
...::: ::::::::::::' ``::.
````':. ':::::::::' ::::..
'.:::::' ':'````..
*/
/**
*
* @Authror 万物皆导
* @Date ${DATE} ${TIME}
*/
毋庸置疑,这肯定是一个悲伤的故事,我这边有一个需求,我要对一些记录和日志表进行迁移备份,介于当下条件,我们没有使用 MaxCompute 进行备份,也没有通过主从数据库关系或者 DTS 等等其它技术进行优雅处理,所以我们当下使用一个比较简单的处理方式,就是通过定时任务进行唤起存储过程进行备份操作。
根据以上需求,显而易见,存储过程里面的内容便是核心,其实相对来说也比较简单,无非就是分三步走,如下:
- 对源表表结构进行复制
- 备份指定时间的数据
- 删除源表里面的已经备份的数据
首先我这边会创建一个备份配置表,如下:
-- 创建备份配置表
CREATE TABLE `back_up_base` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`source_database` varchar(100) NOT NULL COMMENT '源库',
`source_table` varchar(100) NOT NULL COMMENT '源表',
`destination_database` varchar(100) NOT NULL COMMENT '目标库',
`destination_table` varchar(100) NOT NULL COMMENT '目标表',
`days` int(11) unsigned NOT NULL DEFAULT '15' COMMENT '保留天数',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
接下来就是写存储过程了,首先看看我第一版写的,如下:
DELIMITER $$
# 备份存储过程
CREATE DEFINER=`root`@`%` PROCEDURE `back_up_record`()
BEGIN
# 声明变量信息
DECLARE source_database VARCHAR(100) DEFAULT '';
DECLARE source_table VARCHAR(100) DEFAULT '';
DECLARE destination_database VARCHAR(100) DEFAULT '';
DECLARE destination_table VARCHAR(100) DEFAULT '';
DECLARE days INT DEFAULT 15;
DECLARE done int DEFAULT FALSE;
# 获取需要备份的数据库信息
DECLARE cur_back_up CURSOR FOR SELECT source_database,source_table,destination_database,destination_table,days FROM `backup`.back_up_base;
# 定义循环结束标志
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set done = true;
# 打开游标
OPEN cur_back_up;
# 开始遍历
read_loop: LOOP
FETCH cur_back_up INTO source_database,source_table,destination_database,destination_table,days;
IF done THEN LEAVE read_loop;
END IF;
# 创建表
# 设置执行语句
SET @create_table = CONCAT('CREATE TABLE IF NOT EXISTS `',destination_database,'`.`',destination_table,'` (LIKE `',source_database,'`.`',source_table,'`)');
# 预处理动态 sql
PREPARE stmt_create_table FROM @create_table;
# 执行 sql
EXECUTE stmt_create_table;
# 释放预处理
DEALLOCATE PREPARE stmt_create_table;
# 备份数据
# 设置执行语句
SET @back_up_data = CONCAT('INSERT INTO `',destination_database,'`.`',destination_table,'` SELECT * FROM `',source_database,'`.`',source_table,'` WHERE created<DATE_SUB(CURDATE(),INTERVAL ',days,' DAY)');
# 预处理动态 sql
PREPARE stmt_back_up_data FROM @back_up_data;
# 执行 sql
EXECUTE stmt_back_up_data;
# 释放预处理
DEALLOCATE PREPARE stmt_back_up_data;
# 删除数据
# 设置执行语句
SET @delete_data = CONCAT('DELETE FROM `',source_database,'`.`',source_table,'` WHERE created<DATE_SUB(CURDATE(),INTERVAL ',days,' DAY)');
# 预处理动态 sql
PREPARE stmt_delete_data FROM @delete_data;
# 执行 sql
EXECUTE stmt_delete_data;
# 释放预处理
DEALLOCATE PREPARE stmt_delete_data;
END LOOP read_loop;
# 关闭游标
CLOSE cur_back_up;
END $$
DELIMITER ;
咋一看,赶脚没啥问题哈,一顿操作猛如虎,就是老执行报错,因为我使用的是 navicat,所以不怎么汇调试,阿导只有三年前使用 oracle 才用过存储过程并加以调试。那我只能注释一行行代码来排查问题,最终发现问题是遍历的时候,FETCH 并没有获取到元数据,这是一个很苦逼的事。
郁闷了一天,先使用 java 动态列出所有的需要备份的表,然后进行执行,临时撑个场子。
阿导绝不能认输,我一定的找到引起这个问题的原因,毕竟学无止境,尤其是咱们这行,必须要有打破砂锅问到底的精神,做到不抛弃不放弃的精神。
通过阅读大量前辈的经验,我发现存储过程中定义的变量名称不能和游标体里面的变量一样,否则造成混淆,导致无法获取到值的情况,者是真尴尬,毕竟按照我们程序员思路,这个应该在编译环节就抛出的异常,所以阿导遇到这个问题是真郁闷,也怪阿导学艺不精导致的,希望我这边记录下来并分享给需要帮助的人,少走一些弯路。
修改后的存储过程如下:
DELIMITER $$
# 创建存储过程
CREATE DEFINER=`root`@`%` PROCEDURE `back_up_record`()
BEGIN
# 声明变量信息
DECLARE cur_source_database VARCHAR(100) DEFAULT '';
DECLARE cur_source_table VARCHAR(100) DEFAULT '';
DECLARE cur_destination_database VARCHAR(100) DEFAULT '';
DECLARE cur_destination_table VARCHAR(100) DEFAULT '';
DECLARE cur_days INT DEFAULT 15;
DECLARE done int DEFAULT FALSE;
# 获取需要备份的数据库信息
DECLARE cur_back_up CURSOR FOR SELECT source_database,source_table,destination_database,destination_table,days FROM `backup`.back_up_base;
# 定义循环结束标志
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set done = true;
# 打开游标
OPEN cur_back_up;
# 开始遍历
read_loop: LOOP
FETCH cur_back_up INTO cur_source_database,cur_source_table,cur_destination_database,cur_destination_table,cur_days;
IF done THEN LEAVE read_loop;
END IF;
# 创建表
# 设置执行语句
SET @create_table = CONCAT('CREATE TABLE IF NOT EXISTS `',cur_destination_database,'`.`',cur_destination_table,'` (LIKE `',cur_source_database,'`.`',cur_source_table,'`)');
# 预处理动态 sql
PREPARE stmt_create_table FROM @create_table;
# 执行 sql
EXECUTE stmt_create_table;
# 释放预处理
DEALLOCATE PREPARE stmt_create_table;
# 备份数据
# 设置执行语句
SET @back_up_data = CONCAT('INSERT INTO `',cur_destination_database,'`.`',cur_destination_table,'` SELECT * FROM `',cur_source_database,'`.`',cur_source_table,'` WHERE created<DATE_SUB(CURDATE(),INTERVAL ',cur_days,' DAY)');
# 预处理动态 sql
PREPARE stmt_back_up_data FROM @back_up_data;
# 执行 sql
EXECUTE stmt_back_up_data;
# 释放预处理
DEALLOCATE PREPARE stmt_back_up_data;
# 删除数据
# 设置执行语句
SET @delete_data = CONCAT('DELETE FROM `',cur_source_database,'`.`',cur_source_table,'` WHERE created<DATE_SUB(CURDATE(),INTERVAL ',cur_days,' DAY)');
# 预处理动态 sql
PREPARE stmt_delete_data FROM @delete_data;
# 执行 sql
EXECUTE stmt_delete_data;
# 释放预处理
DEALLOCATE PREPARE stmt_delete_data;
END LOOP read_loop;
# 关闭游标
CLOSE cur_back_up;
END $$
DELIMITER ;
终于到了最后一步,定时开启定时任务就行了,如下:
CREATE EVENT IF NOT EXISTS backup_record_event
ON SCHEDULE EVERY 1 DAY DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
ON COMPLETION PRESERVE ENABLE
-- 这边需要更改库,目前设置每天凌晨1点跑数据
DO CALL back_up_record();
至此已结束。