Specific operations of MYSQL scheduled clearing of backup data

1|0 Background

Due to project requirements, each month's historical stock data needs to be archived and backed up, and some log tables need to have their detail fields cleared to save space on the MySQL database disk. Some scheduled tasks are needed to clean up this data regularly.

2|0 Technology Selection

• Java can use the scheduled task mechanism based on Quartz/Timer to write scheduled tasks, which requires relying on Java programs
• To build a scheduled task document based on cron expressions based on Crontab under Linux, you must write a corresponding script. However, in this script, since it is necessary to operate the database, it will inevitably involve data such as plain text passwords, which is not very safe.
• Since the MYSQL database is used and MYSQL supports scheduled tasks, you can use the MYSQL built-in scheduled tasks.

3|0 Specific operations

3|1mysql configuration

Since the scheduled task option is turned off in the default configuration of MySQL, for the sake of insurance, let's first check whether the 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 (valid for the currently started instance, invalid after restart)

set global event_scheduler=1;

​ 2. Configure my.cnf (my.ini in Windows) and add the following options

[mysqld]
event_scheduler=ON 

Then save it and restart mysql

3|2sql writing

Create a stored procedure (procedure) [Optional: If it is just a simple SQL, you can specify it directly in the scheduled task]

Stored procedure: A stored procedure, also known as a stored procedure (English: Stored Procedure), is a database object that stores complex programs in a database for external programs to call. It can be regarded as a function or subroutine of the database.

create PROCEDURE sched_clean() --name BEGIN
-- Define parameters 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;
-- Update data UPDATE test_table SET rule_cost=null WHERE start_time>begin_time and start_time<end_time;
END

Create a scheduled task

Once the stored procedure is created, we can call it regularly.

CREATE EVENT `demo`.`day_of_clean_event`
ON SCHEDULE EVERY '1' DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
ON completion preserve disable
DO call sched_clean();

Code Explanation:

• CREATE EVENT demo.day_of_clean_event creates an event named day_of_clean_event in the demo database
• Indicates the creation time timing cycle and the start time. This means that the task will be executed once a day starting from 1 o'clock tomorrow.
• The third line indicates that it will not take effect after creation
• The fourth line indicates the task to be performed (you can also write SQL statements directly here)

Related queries

Query all events of this machine

SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;

Enable/disable an event that has already been created

alter event event_name on completion preserve enable; //Open scheduled task alter event event_name on completion preserve disable; //Close scheduled task

4|0 Points worth noting

If your global parameters are not configured to start the scheduled task, you will still not be able to find any records in the information_schema.EVENTS table even though you have started the created event.

4|1 Common cycle timing rules

① Cycle execution – keyword EVERY

The units are: second, minute, hour, day, week, quarter, month, year, such as:

on schedule every 1 second //Execute once per second on schedule every 2 minute //Execute once every two minutes on schedule every 3 day //Execute once every three days

② Execute at a specific time – keyword AT, such as:

on schedule at current_timestamp()+interval 5 day // Execute in 5 days on schedule at current_timestamp()+interval 10 minute // Execute in 10 minutes on schedule at '2016-10-01 21:50:00' // Execute at 9:50 PM on October 1, 2016

③ Execute in a certain time period – Keywords STARTS ENDS, such as:

on schedule every 1 day starts current_timestamp()+interval 5 day ends current_timestamp()+interval 1 month //Starts executing every day after 5 days and ends at the end of the next month on schedule every 1 day ends current_timestamp()+interval 5 day //Starts executing every day for 5 days

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.