All Products
Search
Document Center

ApsaraDB RDS:Configure a cyclic event

Last Updated:Dec 28, 2023

If you want to execute scheduled or periodic tasks, such as periodically sending statistical reports, synchronizing data, or deleting expired data, on an ApsaraDB RDS for MySQL instance, you can use the event scheduler feature to execute events defined in databases. This topic describes how to use Data Management (DMS) to configure a cyclic event on an RDS instance. The cyclic event can be a scheduled or periodic task.

Prerequisites

An account that has event permissions on the destination database is created. The account can be a privileged account, a standard account that has read and write permissions, or a standard account that has only DML permissions.

Procedure

Step 1: Enable the event scheduler

By default, the event scheduler is disabled for your RDS instance. You must enable the event scheduler before you configure cyclic events on the RDS instance.

  1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
  2. In the left-side navigation pane of the page that appears, click Parameters.

  3. On the Editable Parameters tab of the page that appears, search for event_scheduler and change the value of the parameter to ON in the Running Parameter Value column.

  4. Click Apply Changes. In the dialog box that appears, select Take Effect Immediately and click OK.

Step 2: Configure a cyclic event on the RDS instance in DMS

  1. In the upper-right corner of the page, click Log On to Database. On the Log on to Database Instance page, use the account of the RDS instance to log on to the database instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.

    Note

    When you log on to the database instance, you must set the Control Mode parameter to Flexible Management. For more information about the DMS control modes, see Control modes.

  2. On the SQLConsole tab, execute the following SQL statement to create a test table named students.

    Note

    If you use an existing table in the database, you can skip this step and use the SQL statement for the Event statement(*) parameter in the subsequent operations.

    CREATE TABLE students (
      `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
      `name` varchar(64) NOT NULL COMMENT 'name',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  3. In the navigation tree of the SQLConsole tab, click Programmable Object, right-click Event, and then select New (event).

    image.png

  4. On the New (event) tab, configure event parameters.

    In this example, the Event name parameter is set to test01, the Scheduling Method parameter is set to Cycle time, the Interval parameter is set to 10 minutes, and the Event statement(*) parameter is set to the SQL statement that is used to insert data to the students table. For more information about event parameters, see Event parameters.

    Sample event statement:

    begin
    INSERT INTO students2(name) VALUES('zhangsan');
    end

    Event parameters

    Section

    Parameter

    Description

    Event Basic information

    Event name

    The name of the event.

    Status

    The status of the event. Valid values:

    • Enabled: The event is executable.

    • Disable: The event is not executable.

    • Disable from DB: The event can be executed only on the primary database.

    Due deletion

    • Specifies whether to delete the event after the event is executed at a fixed point in time.

    • Specifies whether to delete the event after the event is executed at specified intervals for the specified period of time.

    Note

    The annotation of the event.

    Execution time definition

    Scheduling Method

    • Fixed time: The event is executed at a fixed point in time. If you select this method, you must configure the Fixed Time parameter.

    • Cycle time: The event is executed at specified intervals. If you select this method, you must configure the Interval, Start time, and End time parameters.

      Note
      • Interval: the internal at which the event is executed. The value of this parameter consists of an integer and a time unit.

      • Start time: the start time of the event. If you do not configure this parameter, the system immediately executes the event.

      • End time: the end time of the event. If you do not configure this parameter, the system infinitely executes the event.

    Event statement

    None

    The SQL statement that needs to be executed in the event.

    Replace /**event body**/ with the actual SQL statements.

  5. Click Save. In the Submit dialog box, confirm that the SQL statement is correct and click Execute.

    On the Programmable Objects tab, you can click Events to view the new cyclic event. You can also execute the SHOW EVENTS; statement on the SQLConsole tab to view the details about the new cyclic event.

    Note

    If the status of the event is Enabled in the output of the SHOW EVENTS statement, the event is enabled.

Step 3: View the execution result of the cyclic event

You can check the SQL statement in the cyclic event to view the execution result.

  • If the SQL statement is successfully executed, you can check related tables to verify the result.

  • If the SQL statement fails to be executed, you can view error logs to obtain details.

In this example, the following statement is executed on the SQLConsole tab to view the event execution result:

SELECT * FROM students;

Step 4: Delete the cyclic event

Note

If you enable due deletion and configure the End Time parameter for the cyclic event, the event is automatically deleted after it expires. You do not need to manually delete the event.

  1. On the Programmable Objects tab, expand Events, right-click the event that you want to delete, and then select Delete (event).

  2. In the dialog box that appears, click Execute.

Note

You can also execute the DROP EVENT <Event name>; statement on the SQLConsole tab to delete the event.

References

What is DMS?