README.md

Backup scripts for MySQL

backup.sql

# Move rows from one table to backup table

DELIMITER $$

DROP PROCEDURE IF EXISTS backup_events_table $$

CREATE PROCEDURE backup_events_table()
proc_label:BEGIN

    SET @oldest = (SELECT `created` FROM events WHERE `created` < DATE_SUB(NOW(),INTERVAL 2 DAY) ORDER BY created ASC LIMIT 1);

    IF @oldest IS NULL THEN
        LEAVE proc_label;
    END IF;

    set @oldest_day = DATE_FORMAT(@oldest,'%Y-%m-%d');
    SET @tbl = CONCAT('events-', @oldest_day);
    SET @q = CONCAT('CREATE TABLE IF NOT EXISTS `', @tbl, '` LIKE `events`');
    PREPARE stmt FROM @q;
    EXECUTE stmt;

    SET @q = CONCAT('INSERT INTO `', @tbl, '` SELECT * FROM `events` WHERE DATE_FORMAT(created, "%Y-%m-%d") = "', @oldest_day, '"');
    PREPARE stmt FROM @q;
    EXECUTE stmt;

    SET @q = CONCAT('DELETE FROM `events` WHERE DATE_FORMAT(created, "%Y-%m-%d") = "', @oldest_day, '"');
    PREPARE stmt FROM @q;
    EXECUTE stmt;

END $$

DELIMITER ;

# Backup table event

CREATE EVENT backup_tables
ON SCHEDULE EVERY 24 HOUR
DO
    CALL backup_events_table();