微服务|Go 微服务十七存储过程 + 事件实现数据备份 mysql|数据库|golang

【微服务|Go 微服务十七存储过程 + 事件实现数据备份】点击链接查看我的个人博客，文章更全更详细
遇到一个需求。需要每天凌晨三点实现对指定的几张表执行备份（备份前一天的数据）。并且写到备份库里，并对现有库中删除掉。每天的单子量非常大,如果再加上备份读写 mysql 会比较慢
刚开始通过go协程开四个协程实现备份，一个小时备份了不到50万太慢了。所以改用存储过程+事件的方式实现。经测试
530万的数据量备份需要大概13分钟。
创建demo 数据库
创建 o_a_order表
创建 o_b_order表
创建 o_c_order表
创建 o_match_order表
备份的表
创建 o_a_order_backup表
创建 o_b_order_backup表
创建 o_c_order_backup表
创建 o_match_order_backup表

查看mysql 事件调度器是否打开

show variables like '%event_scheduler%';

开启event_scheduler sql指令

SET GLOBAL event_scheduler = ON; SET @@global.event_scheduler = ON; SET GLOBAL event_scheduler = 1; SET @@global.event_scheduler = 1;

o_a_order

-- 存储过程 o_a_order DELIMITER |DROP PROCEDURE IF EXISTS a_backup |CREATE PROCEDURE a_backup()BEGININSERT INTO o_a_order_backup SELECT * FROM o_a_order WHERE created_at < CAST(CAST(SYSDATE()AS DATE)AS DATETIME); DELETE FROM o_a_order WHERE created_at < CAST(CAST(SYSDATE()AS DATE)AS DATETIME); END|-- 定时器 omp_a_order SET GLOBAL event_scheduler = 1; CREATE EVENT IF NOT EXISTS a_backupON SCHEDULE EVERY 1 DAYON COMPLETION PRESERVEDO CALL a_backup(); -- 启动定时器 omp_a_order ALTER EVENT a_backup ON COMPLETION PRESERVE ENABLE;

o_b_order

-- 存储过程 o_b_order DELIMITER |DROP PROCEDURE IF EXISTS b_backup |CREATE PROCEDURE b_backup()BEGININSERT INTO o_b_order_backup SELECT * FROM o_b_order WHERE created_at < CAST(CAST(SYSDATE()AS DATE)AS DATETIME); DELETE FROM o_b_order WHERE created_at < CAST(CAST(SYSDATE()AS DATE)AS DATETIME); END|---- 定时器 omp_b_order SET GLOBAL event_scheduler = 1; CREATE EVENT IF NOT EXISTS b_backupON SCHEDULE EVERY 1 DAYON COMPLETION PRESERVEDO CALL b_backup(); -- 启动定时器 omp_b_order ALTER EVENT b_backup ON COMPLETION PRESERVE ENABLE;

o_c_order

DELIMITER |DROP PROCEDURE IF EXISTS c_backup |CREATE PROCEDURE c_backup()BEGININSERT INTO o_c_order_backup SELECT * FROM o_c_order WHERE created_at < CAST(CAST(SYSDATE()AS DATE)AS DATETIME); DELETE FROM o_c_order WHERE created_at < CAST(CAST(SYSDATE()AS DATE)AS DATETIME); END|---- 定时器 o_c_order SET GLOBAL event_scheduler = 1; CREATE EVENT IF NOT EXISTS c_backupON SCHEDULE EVERY 1 DAYON COMPLETION PRESERVEDO CALL c_backup(); -- 启动定时器 omp_c_order ALTER EVENT c_backup ON COMPLETION PRESERVE ENABLE;

o_match_order

-- 存储过程 o_match_order DELIMITER |DROP PROCEDURE IF EXISTS m_order_backup |CREATE PROCEDURE m_order_backup()BEGININSERT INTO o_match_order_backup SELECT * FROM o_match_order WHERE created_at < CAST(CAST(SYSDATE()AS DATE)AS DATETIME); DELETE FROM o_match_order WHERE created_at < CAST(CAST(SYSDATE()AS DATE)AS DATETIME); END|---- 定时器 o_match_order SET GLOBAL event_scheduler = 1; CREATE EVENT IF NOT EXISTS match_order_backupON SCHEDULE EVERY 1 DAYON COMPLETION PRESERVEDO CALL m_order_backup(); -- 启动定时器 o_match_order ALTER EVENT m_order_backup ON COMPLETION PRESERVE ENABLE;

show events; SHOW PROCEDURE STATUS LIKE 'a_backup'; SHOW PROCEDURE STATUS LIKE 'b_backup'; SHOW PROCEDURE STATUS LIKE 'c_backup'; SHOW PROCEDURE STATUS LIKE 'match_backup'; drop event a_backup; drop event b_backup; drop event c_backup; drop event match_order_backup;