How to use MySQL's event scheduler to implement scheduled task scheduling

How to use MySQL's event scheduler to implement scheduled task scheduling

When developing and managing databases, it is often necessary to perform certain tasks regularly, such as daily backup of the database, weekly statistical reports, etc. MySQL provides a very useful tool, the Event Scheduler, which can help us implement scheduled task scheduling. This article will introduce how to use MySQL's event scheduler to implement scheduled task scheduling, and provide corresponding code examples.

1. Turn on the event scheduler

Before using the event scheduler, you first need to confirm whether the MySQL event scheduler has been turned on. You can check the status of the event scheduler through the following command:

SHOW VARIABLES LIKE 'event_scheduler';

If the result is Off, you need to manually turn on the event scheduler. It can be turned on by the following command:

SET GLOBAL event_scheduler = ON;

After it is turned on successfully, you can confirm that the event scheduler has been turned on by executing the SHOW VARIABLES LIKE 'event_scheduler'; command again.

2. Create a scheduled task

Using the event scheduler to create a scheduled task requires the following steps:

1. Create an event scheduler

Use the CREATE EVENT statement to create an event scheduler. The specific syntax is as follows:

CREATE EVENT event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'string']
DO event_body;

where event_name is the name of the event, schedule is the scheduling period, and event_body is the specific operation of the event.

2. Set the scheduling period

The scheduling period can be set according to actual needs. The following are commonly used scheduling cycles:

• Execute once every second: EVERY 1 SECOND
• Execute once every minute: EVERY 1 MINUTE
• Execute once every hour: EVERY 1 HOUR
• Executed once a day: EVERY 1 DAY
• Executed once a week: EVERY 1 WEEK
• Executed once a month: EVERY 1 MONTH

You can choose the appropriate scheduling cycle according to your needs.

3. Write the specific operation of the event

Write the specific operation logic in the event_body part. You can use SQL statements to perform database operations, or you can call stored procedures or functions.

The following is an example of a scheduled task to back up the database at 3 a.m. every day:

CREATE EVENT backup_event
ON SCHEDULE EVERY 1 DAY
STARTS '2022-01-01 03: 00:00'
DO
BEGIN

DECLARE backup_file VARCHAR(255);
SET backup_file := CONCAT('/var/backup/db_', DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s'), '.sql');
SET @sql := CONCAT('mysqldump -hlocalhost -uroot -ppassword dbname > ', backup_file);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END;

The above code creates an event scheduler named backup_event, and sets the scheduling period to be executed once a day. In the event_body part, a variable backup_file is first defined to store the path of the backup file. Then use the CONCAT function to splice the backup file path, and then use the SET statement to assign the backup command to the @sql variable. Finally, use the PREPARE and EXECUTE statements to execute the backup command.

3. Manage scheduled tasks

After using the CREATE EVENT statement to create a scheduled task, you can manage it through the following command:

• View all event schedulers: SHOW EVENTS;
• View the information of the specified event scheduler: SHOW EVENT event_name;
• Enable the event scheduler: ALTER EVENT event_name ENABLE;
• Disable the event scheduler: ALTER EVENT event_name DISABLE;
• Modify the execution time of the event scheduler: ALTER EVENT event_name ON SCHEDULE AT 'date_time';

Through management commands, you can easily view and manage scheduled tasks.

Summary

This article introduces how to use MySQL's event scheduler to implement scheduled task scheduling. By creating an event scheduler, setting the scheduling cycle and writing specific operation logic, you can easily implement the function of regularly executing database tasks. Developers can customize the scheduling cycle and operation logic of scheduled tasks based on actual needs. MySQL's event scheduler provides more flexibility and convenience for our database development and management.

Reference code:

-- Create event scheduler
CREATE EVENT backup_event
ON SCHEDULE EVERY 1 DAY
STARTS '2022-01-01 03:00:00 '
DO
BEGIN

DECLARE backup_file VARCHAR(255);
SET backup_file := CONCAT('/var/backup/db_', DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s'), '.sql');
SET @sql := CONCAT('mysqldump -hlocalhost -uroot -ppassword dbname > ', backup_file);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END;

-- View all event schedulers
SHOW EVENTS;

-- View the specified event scheduler Information
SHOW EVENT backup_event;

--Enable event scheduler
ALTER EVENT backup_event ENABLE;

--Disable event scheduler
ALTER EVENT backup_event DISABLE;

-- Modify the execution time of the event scheduler
ALTER EVENT backup_event ON SCHEDULE AT '2023-01-01 03:00:00';

The above is the detailed content of How to use MySQL's event scheduler to implement scheduled task scheduling. For more information, please follow other related articles on the PHP Chinese website!