Automate Daily Batch Backups of Init profiling hist Table Records to an Archive Database

Step 1: Before backing up the database, create the applicarearchive schema, execute the applicare-related sql's and ensure all tables are created.

Step 2: Connect to the database via MySQL Workbench, open a new SQL query tab, and execute the following command.

NOTE: Replace the <targetDB> placeholder with actual backup database name before running the query.

DELIMITER //

CREATE PROCEDURE applicarearchive.sp_BatchArchiveYesterday()
BEGIN
   DECLARE rows_affected INT DEFAULT 1;

   SET @yesterday_start = CONCAT(CURDATE() - INTERVAL 1 DAY, ' 00:00:00');
   SET @yesterday_end   = CONCAT(CURDATE() - INTERVAL 1 DAY, ' 23:59:59');

   SET @sourcedb = '<targetDB>';
   SET @backupdb  = 'applicarearchive';
   WHILE rows_affected > 0 DO

       SET @sql_query = CONCAT(
           'INSERT INTO ', @backupdb, '.int_profiling_hist ',
           'SELECT * FROM ', @sourcedb, '.int_profiling_hist ',
           'WHERE from_time BETWEEN ? AND ? ',
           'AND rl NOT IN (SELECT rl FROM ', @backupdb, '.int_profiling_hist) ',
           'LIMIT 10000'
       );
       PREPARE stmt FROM @sql_query;
       EXECUTE stmt USING @yesterday_start, @yesterday_end;
       SET rows_affected = ROW_COUNT();
       DEALLOCATE PREPARE stmt;

   END WHILE;
END //
DELIMITER ;

NOTE: The query above only backs up data from the int_profiling_hist table, rather than the entire database

Safe Data Duplication: This query performs a "Safe Copy" that mirrors yesterday's data into the archive without removing or altering the original records

Batch Load Management: It processes records in chunks of 10,000 to maintain low CPU usage and prevent the database from locking up during the transfer.

Automated Time Filtering: The script automatically calculates the 24-hour range for "Yesterday," ensuring only the correct historical data is captured each time it runs.

Duplicate Prevention: It uses a unique identifier check (rl) to ensure that even if the script runs twice, it won't create double entries in the archive.

Dynamic Table Routing: Through the use of Prepared Statements, the script can dynamically target different databases without needing manual code changes.

Step 3: Run the query below to enable the MySQL Event Scheduler and schedule the sp_BatchArchiveYesterday procedure to run daily at 1:00 AM.

SET GLOBAL event_scheduler = ON;
DROP EVENT IF EXISTS applicarearchive.daily_archive_at_1am;
CREATE EVENT applicarearchive.daily_archive_at_1am
ON SCHEDULE EVERY 1 DAY
STARTS (CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 1 HOUR)
DO
 CALL applicarearchive.sp_BatchArchiveYesterday();

To modify the scheduled time, first execute the query below to drop the existing event, then re-create it with your desired schedule.

DROP EVENT IF EXISTS applicarearchive.daily_archive_at_1am;

To change the time to 8:00 PM, We need to adjust the INTERVAL in the STARTS clause. Since MySQL uses a 24-hour format, 8:00 PM is 20 hours.

e.g.

SET GLOBAL event_scheduler = ON;
CREATE EVENT applicarearchive.daily_archive_at_8pm
ON SCHEDULE EVERY 1 DAY
STARTS (CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 20 HOUR)
DO
 CALL applicarearchive.sp_BatchArchiveYesterday();

Step 4: Use this query to list all active events within the applicarearchive schema.

SHOW EVENTS FROM applicarearchive;