Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to delete the log table regularly in the MySQL database. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

1. Open event Scheduler

Set global event_scheduler= ON; show variables like'% event_scheduler%';#my.cnf adds parameter event_scheduler= on

two。 Create a log to delete a record table

CREATE TABLE IF NOT EXISTS mq_log (name VARCHAR (400) NOT NULL COMMENT 'delete information', row INT COMMENT 'delete rows', del_time TIMESTAMP COMMENT 'delete time') COMMENT = 'log delete record table'

3. Create log delete stored procedure

Here, we mainly use the characteristics of exception recording, number of rows of influence records, and transaction processing.

Drop procedure if exists del_mq_log;DELIMITER / / create procedure del_mq_log () BEGIN DECLARE affect_rows INT; DECLARE affect_rows2 INT; DECLARE affect_rows3 INT; declare v_commit int default 2;-- define transaction use. 1 is normal,-10 is failed declare msg text;-- recording error message-- msg captures error message declare continue handler for sqlexception begin get diagnostics condition 1 msg = message_text in case of exception Set v_commit =-10; end; start transaction;-set transaction delete from lcp_mq_record where last_update_date < DATE_SUB (CURDATE (), INTERVAL 30 DAY); select ROW_COUNT () into @ affect_rows; delete from lcp_dd_service_record where last_update_date < DATE_SUB (CURDATE (), INTERVAL 30 DAY); select ROW_COUNT () into @ affect_rows2 Delete from lcp_api_service_record where last_update_date < DATE_SUB (CURDATE (), INTERVAL 30 DAY); select ROW_COUNT () into @ affect_rows3; insert into mq_log values ('lcp_mq_record deleted successfully', @ affect_rows,now ()), ('deleted lcp_dd_service_record successful', @ affect_rows2,now ()), ('lcp_api_service_record deleted successfully', @ affect_rows3,now ()) -- record deletion-- exception rollback and log if v_commit =-10 then ROLLBACK; insert into mq_log values (msg,0,now ()); end if; END//DELIMITER

4. Create a scheduled task that executes the stored procedure at 1 a. M. every day

CREATE EVENT e_del_mqlog ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD (DATE_ADD (CURDATE (), INTERVAL 1 DAY), INTERVAL 1 HOUR) DO call del_mq_log ()

5. The first deletion needs to be recycled.

Alter table lcp_mq_record engine=innodb;alter table lcp_dd_service_record engine=innodb;alter table lcp_api_service_record engine=innodb

On how to regularly delete the log table in the MySQL database to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.