mysql定时复制表数据_Mysql使用存储过程和定时任务批量复制表数据到另一个表...

存储过程介绍

mysql从5.0开始提供存储过程功能，存储过程就是在数据库内部执行的函数方法，外部程序可直接调用数据库内部的存储过程，从而实现对数据库内部数据和逻辑的保密。

日常系统都会定时对一些表做删除和备份，这里也可以使用存储过程来实现。

这里我们实现一个可以把数据库中表A的数据在每天的指定时间使用mysql定时任务备份到表B中

mysql存储过程实现

存储过程实现把表A数据使用游标批量导入到表B中，由于频繁的COMMIT会引起大量Redo Log的物理I/O，会极大的限制数据库的性能。所以为了提升导入数据性能，存储过程每10000笔commit数据1次

DELIMITER $$

create PROCEDURE backAtoB()

BEGIN

-- 创建变量用来接收游标数据 变量的声明、游标的声明和HANDLER声明的顺序不能乱，必须是先声明变量，再申明游标，最后声明HANDLER

-- 变量的定义不要和你的select的列的键同名！否则fetch into 会失败

DECLARE v_id INT DEFAULT 0;

DECLARE v_name VARCHAR(100) DEFAULT '';

DECLARE v_creattime DATETIME DEFAULT NULL;

DECLARE v_count INT DEFAULT 0;

DECLARE done INT DEFAULT 0;

-- 创建游标

DECLARE rs_cursor CURSOR FOR

SELECT id,name,creattime FROM tbla WHERE creattime

-- 指定游标循环结束时的返回值 ,在游标循环到最后会将 done 设置为 1

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

-- 打开游标

OPEN rs_cursor;

-- 关闭事务自动提交,这里只是临时关闭，存储过程执行完成后autocommit还是原来系统的状态

SET autocommit=0;

-- 开始循环

read_loop: LOOP

-- 提取游标里的数据到上面声明的变量中

FETCH rs_cursor INTO v_id,v_name,v_creattime;

-- 声明何时结束循环

IF done=1 THEN

LEAVE read_loop;

END IF;

-- 循环时的事件

-- 向表B插入数据,表B和表A结构一致,为提升插入效率每10000条commit一次

INSERT INTO tblb VALUES (v_id,v_name,v_creattime);

SET v_count = v_count + 1;

IF MOD(v_count,10000)=0 THEN

COMMIT;

SET v_count = 0;

END IF;

-- 关闭循环

END LOOP read_loop;

-- 导入数据不是10000整数倍时需要在loop结束后在提交一次

COMMIT;

-- 关闭游标

CLOSE rs_cursor;

END$$

DELIMITER ;

存储过程的相关命令

#查看某个数据库中的存储过程

select `name` from mysql.proc where db = 'dbname' and `type` = 'PROCEDURE';

#删除某个存储过程

DROP PROCEDURE backAtoB;

#条用某个存储过程

call backAtoB();

mysql定时任务

mysql默认的定时任务功能是关闭的，查看是否关闭命令sql,off表示关闭

show variables like '%event_scheduler%';

开启定时任务有两种方式

方式一 使用sql语句方式设置，但是仅对当前启动实例有效，重启后失效

set global event_scheduler=1;

方式二 在配置文件中设置,/etc/my.cnf文件新增event_scheduler=ON ，然后重启mysql

#[mysqld]下新增如下内容

event_scheduler=ON

重启mysql

service mysqld restart

创建一个定时任务的事件

--在database1数据库中创建backAtoB_everyday事件

CREATE EVENT `database1`.`backAtoB_everyday`

-- 创建定时时间为2020-03-26 11:12:00开始，每天执行1次的任务

-- 如果想要执行，必须设置为当前时间以后的时间点！！！

ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP '2020-03-26 11:12:00'

-- 创建定时时间为明天凌晨1点开始，每天执行1次的任务

-- ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)

-- 表示创建后并不开始生效

-- ON completion preserve disable

--调用写好的存储过程

DO call backAtoB();

常用的设置event中Schedule方法

on schedule every 1 second//每秒执行1次

on schedule every 2 minute//每两分钟执行1次

on schedule every 3 day//每3天执行1次

on schedule every 1 month //每1个月执行1次

on schedule every 1 year //每年执行1次

#创建定时时间为2020-03-26 11:12:00开始，每天执行1次的任务

#如果想要执行，必须设置为当前时间以后的时间点！！！

on schedule every 1 day TIMESTAMP '2020-03-26 11:12:00'

on schedule at current_timestamp()+interval 5 day// 5天后执行

on schedule at current_timestamp()+interval 10 minute// 10分钟后执行

on schedule at '2020-01-01 23:50:00'// 在2020年01月1日，晚上23点50执行

//指定时间段，5天后开始每天都执行，事件执行结束时间到下个月

on schedule every 1 day starts current_timestamp()+interval 5 day ends current_timestamp()+interval 1 month

//从现在起每天执行，执行5天

on schedule every 1 day ends current_timestamp()+interval 5 day

查询已经创建的事件

SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;

注：如果你的全局参数没有配置开启定时任务，会导致information_schema.EVENTS表中还是查询不到记录

启动或者关闭已经创建好的事件，例如启动或者关闭定时任务

alter event event_name on completion preserve enable;//开启定时任务

alter event event_name on completion preserve disable;//关闭定时任务