Table archiving using SP for automation purpose

Create the below SP in your database and execute call usp_dba_history_archive() 

 DROP procedure IF EXISTS `usp_dba_history_archive`;

DELIMITER $$

 

CREATE  PROCEDURE `usp_dba_notificatio_history_archive`()

BEGIN

  DECLARE a INT Default 1 ;

  simple_loop: LOOP

# replace with your delete script

    -- delete from test.history  where notifydate <= '2020-12-31 11:59:59'  ORDER BY `notifydate` desc  LIMIT 100000;

     DO SLEEP(2);

# to check still how many records pending for delete, you can also comment this line if not required

select count(1) from test.history  where notifydate >= '2020-12-31 11:59:59' ;

DO SLEEP(2);

     select a; 

    SET a=a+1;

# you can  increase/decrease the below value based on the number of records required to delete

    IF a=15 THEN

      LEAVE simple_loop;

    END IF;

 END LOOP simple_loop;

END$$

DELIMITER ;