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;
Comments
0 comments
Article is closed for comments.