How to clear backup data regularly in MYSQL

This article introduces you how to regularly clear backup data in MYSQL, the content is very detailed, interested friends can refer to, I hope it can be helpful to you.

Mysql configuration

Since the scheduled task option is turned off by default in mysql, just to be on the safe side, let's check to see if this option is turned on

Show variables like'% event_scheduler%'

If the Off option is displayed, there are two ways to modify it

1. Statement method (the currently launched instance is valid and invalid after restart)

Set global event_scheduler=1

two。 Add the following options under configuration my.cnf (my.ini under windows)

[mysqld] event_scheduler=ON

Then save it and restart mysql.

3 | 2sql authoring

Create a stored procedure (procedure) [optional: if it is only a simple sql, you can specify it directly in the scheduled task]

Stored procedures: stored procedures, also known as stored procedures (English: Stored Procedure), are complex programs stored in the database so that database objects called by external programs can be regarded as a function or subroutine of the database.

Create PROCEDURE sched_clean ()-- named BEGIN-- definition parameter DECLARE begin_time BIGINT DEFAULT UNIX_TIMESTAMP (DATE_SUB (CURRENT_DATE (), interval 31 DAY)) * 1000 / declare end_time BIGINT DEFAULT UNIX_TIMESTAMP (DATE_SUB (CURRENT_DATE (), interval 30 DAY)) * 1000 / CURRENT_DATE-update data UPDATE test_table SET rule_cost=null WHERE start_time > begin_time and start_time