Automate Daily Batch Backups of few Tables 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 //

DROP PROCEDURE IF EXISTS applicarearchive.sp_MasterArchive_Final //

CREATE PROCEDURE applicarearchive.sp_MasterArchive_Final()
BEGIN
   DECLARE v_done INT DEFAULT FALSE;
   DECLARE v_table_name VARCHAR(255);
   DECLARE v_id_col VARCHAR(100);
   DECLARE v_time_col VARCHAR(100);
   DECLARE v_source_db VARCHAR(100) DEFAULT '<targetDB>';
   DECLARE v_backup_db VARCHAR(100) DEFAULT 'applicarearchive';
   
   -- COMPLETE DATA MAP for all requested tables
   DECLARE table_cursor CURSOR FOR 
       SELECT * FROM (
           -- Intellitrace & Core Tables
           SELECT 'intellitrace_errors' as t, 'id' as i, 'excep_at' as c UNION
           SELECT 'alert_queue', 'id', 'created_time' UNION
           SELECT 'oshistory_detail', 'rl', 'dt' UNION
           SELECT 'monitor_data', 'rl', 'dt' UNION
           SELECT 'business_txn_avg', 'rl', 'dt' UNION
           SELECT 'int_profiling_hist', 'rl', 'from_time' UNION
           
           -- LAMA Tables
           SELECT 'lama_application_metrics', 'rl', 'recorded' UNION
           SELECT 'lama_database_metrics', 'rl', 'recorded' UNION
           SELECT 'lama_db_bandwidth', 'rl', 'dt' UNION
           SELECT 'lama_db_latency', 'rl', 'dt' UNION
           SELECT 'lama_db_queue', 'rl', 'dt' UNION
           SELECT 'lama_db_status', 'rl', 'dt' UNION
           SELECT 'lama_disk_details', 'rl', 'dt' UNION
           SELECT 'lama_errors', 'rl', 'excep_at' UNION
           SELECT 'lama_hardware_metrics', 'rl', 'recorded' UNION
           SELECT 'lama_holidays', 'rl', 'dt' UNION
           SELECT 'lama_network_metrics', 'rl', 'recorded' UNION
           SELECT 'lama_networks_avg', 'rl', 'dt'
       ) mapping;
         
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

   -- Ensure Log table exists with 'status' column
   CREATE TABLE IF NOT EXISTS applicarearchive.archive_log (
       id INT AUTO_INCREMENT PRIMARY KEY,
       table_name VARCHAR(255),
       rows_moved INT,
       sync_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
       status VARCHAR(255)
   );

   -- Time Window for Yesterday
   SET @start_val = CONCAT(CURDATE() - INTERVAL 1 DAY, ' 00:00:00');
   SET @end_val   = CONCAT(CURDATE() - INTERVAL 1 DAY, ' 23:59:59');

   OPEN table_cursor;
   read_loop: LOOP
       FETCH table_cursor INTO v_table_name, v_id_col, v_time_col;
       IF v_done THEN LEAVE read_loop; END IF;
       
       SET @rows_affected = 1;
       SET @total_moved = 0;

       -- Move data in batches of 10,000 to prevent lag/locking
       WHILE @rows_affected > 0 DO
           SET @move_sql = CONCAT(
               'INSERT INTO ', v_backup_db, '.', v_table_name, 
               ' SELECT * FROM ', v_source_db, '.', v_table_name, 
               ' WHERE ', v_time_col, ' BETWEEN ? AND ? ',
               ' AND ', v_id_col, ' NOT IN (SELECT ', v_id_col, ' FROM ', v_backup_db, '.', v_table_name, ') ',
               ' LIMIT 10000'
           );
           PREPARE m_stmt FROM @move_sql;
           EXECUTE m_stmt USING @start_val, @end_val;
           SET @rows_affected = ROW_COUNT();
           SET @total_moved = @total_moved + GREATEST(0, @rows_affected);
           DEALLOCATE PREPARE m_stmt;
       END WHILE;

       -- FIXED: Log result including the STATUS column
       INSERT INTO applicarearchive.archive_log (table_name, rows_moved, status)
       VALUES (
           CONCAT(v_table_name, ' (via ', v_time_col, ')'), 
           @total_moved, 
           IF(@total_moved > 0, 'SUCCESS', 'NO NEW DATA')
       );

   END LOOP;
   CLOSE table_cursor;

END //
DELIMITER ;

NOTE: The query above only backs up data from the  few tables, rather than the entire database.

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;
DELIMITER //
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
  BEGIN
    TRUNCATE TABLE applicarearchive.archive_log;
    CALL applicarearchive.sp_MasterArchive_Final();
  END //

DELIMITER ;

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;
DELIMITER //
DROP EVENT IF EXISTS applicarearchive.daily_archive_at_8pm //
CREATE EVENT applicarearchive.daily_archive_at_8pm
ON SCHEDULE EVERY 1 DAY
STARTS (CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 20 HOUR)
DO
  BEGIN
    TRUNCATE TABLE applicarearchive.archive_log;
    CALL applicarearchive.sp_MasterArchive_Final();
  END //

DELIMITER ;

NOTE: Execute the below query to confirm the event is saved in the system.

SHOW EVENTS FROM applicarearchive;

Step 4: We need to create the archive_log table in your archive database. This table will record which table was processed, how many rows were moved, and exactly when it finished.

CREATE TABLE IF NOT EXISTS applicarearchive.archive_log (
    id INT AUTO_INCREMENT PRIMARY KEY,    
    table_name VARCHAR(255),             
    rows_moved INT,                      
    finish_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(255)                  
);

Explanation of the Columns:

• id: A unique number for every entry.

• table_name: Records the table name (and the column name used, e.g., oshistory_detail [dt]).

• rows_moved: The number of records successfully transferred for "Yesterday".

• finish_time: The exact date and time the backup for that specific table completed.

Step 5:  Execute query to see the results. It will show you the most recent backups at the top.

SELECT * FROM applicarearchive.archive_log ORDER BY finish_time DESC;

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

SHOW EVENTS FROM applicarearchive;