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.
- 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.
In the left-side navigation pane of the page that appears, click Parameters.
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.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
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.
NoteWhen 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.
On the SQLConsole tab, execute the following SQL statement to create a test table named students.
NoteIf 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;
In the navigation tree of the SQLConsole tab, click Programmable Object, right-click Event, and then select New (event).
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
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.NoteIf the status of the event is
Enabled
in the output of theSHOW 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
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.
On the Programmable Objects tab, expand Events, right-click the event that you want to delete, and then select Delete (event).
In the dialog box that appears, click Execute.
You can also execute the DROP EVENT <Event name>;
statement on the SQLConsole tab to delete the event.