Use of the MySQL event scheduler event

Q: Suppose that there is a requirement that the system invoke a begin end at a certain point, and execute the begin end in 10 minutes. There is also a requirement for how often each time a recurring begin end is executed. So what should we do at this time?

A:

In Linux, you can use at, crontab to achieve the above requirements, MySQL also has this method, is the event object.

also known as the MySQL event Scheduler (Scheduler), you can execute an SQL statement or a block of statements at a point in time (BEGIN ...). END), or repeat every regular interval. similar to the task Scheduler under Linux under at, Crontab, or Windows.

So how to use the event, the steps are as follows:

1, open the database event execution scheduling

> See if the timer is turned on

' %event_scheduler% ' ; +-----------------+-------+| Variable_name   | Value |+-----------------+-------+| Event_scheduler | OFF   |+-----------------+-------+

> Open

0: off

1:on

Set Global event_scheduler=1, MySQL>~]# mysql-uroot-p123
MySQL'%event_scheduler%'; +-----------------+-------+| Variable_name   | Value |+-----------------+-------+| Event_scheduler | On    |+-----------------+-------+

Attention:

If the event plan is set to 0 or off, that is, when the event planning process is closed, no new events are executed, but the existing running events are executed.

For the online environment, when using even, notice that the timer is turned on on the main library, the timer is turned off from the library, the event trigger all operations are recorded binlog for master-slave synchronization, and the timer opening from the library is likely to cause the card library. After switching the main library, remember to turn on the timer on the new Main library.

2. Create Event creation

CREATE [Definer= {User |Current_User}] EVENT [IF not Exists]event_nameon SCHEDULE SCHEDULE [on completion [NOT] PRESERVE] [ENABLE| DISABLE |DISABLE on SLAVE] [COMMENT'Comment']do event_body;schedule:at Timestamp [+INTERVAL INTERVAL] ...|every interval [starts timestamp [+INTERVAL INTERVAL] ...] [ENDS timestamp [+INTERVAL INTERVAL] ...] interval:quantity { Year| QUARTER | MONTH | Day | HOUR | MINUTE |WEEK| SECOND | Year_month | Day_hour | Day_minute |Day_second| Hour_minute | Hour_second | Minute_second}

Detailed analysis:

①Definer: Indicates the user of the event, which is used by the server to check permissions when it executes.

The default user is the current user, i.e. Definer = Current_User;

If Definer is explicitly specified, the following rules must be followed:

1. If you do not have super privileges, the only allowed value is your current user and cannot be set to another user.

2. If you have super privileges, you can specify any existing user, and if the specified user does not exist, the event will be executed with an error.

② if notexists: If the event already exists, it will not be created and will not be an error.

③ onschedule clause: specifies when to execute the event, and how to execute the event

1) atTimestamp is used to create an event for a single execution , timestamp the time to execute the event (if the specified time is past, a warning is generated), The time can be a specific time string or an expression of a DateTime type (such as Current_timestamp):

If you want to specify a time in the future, use at timestamp directly, for example: at ' 2017-08-08 08:08:08 ';

If you want to specify a time interval in the future, you can use the INTERVAL keyword (INTERVAL keyword to combine, at timestamp + INTERVAL 2 HOUR, + INTERVAL, MINUTE)

2) Theevery clause is used to create recurring events , and if executed once per minute, you can: every 1 MINUTE.

Of course, the Every clause can specify a start event and an end time, represented by the starts and ends keywords, and the syntax is similar to the previous

For example: Every HOUR starts Current_timestamp + INTERVAL-MINUTE ENDS current_timestamp + INTERVAL 4 WEEK.

④ Typically, if an event expires, it is immediately deleted. However, the on completion preserve clause in the Create event definition can preserve the time that has expired.

Default: Oncompletion not PRESERVE, that is, do not save

⑤ By default, enable on slave the event once it is created, and you can disable the event by disable the keyword.

The ⑥comment clause is used to add comments to the event.

The ⑦ doclause is used to indicate what an event needs to do , either as an SQL statement or as a block of statements that are begin...end included, or to call a stored procedure in a statement block.

Basic format:

CREATE EVENT Event_Name

On SCHEDULE <schedule>

Do <event_body>;

Event my_event       Ten        -doset1;

Example: set up a scheduled task to add data to table T2 every minute (current time)

Mysql>Show events; EmptySet(0.02sec) MySQL> CREATE TABLE t2 (IDintauto_increment primary key,t_time datetime); MySQL>delimiter $ $mysql>CREATE EVENT e_daily-On SCHEDULEEvery1MINUTECOMMENT'Saves Total number of sessions then clears the table each day'- Do-BEGININSERT into T2 values (NULL, Current_timestamp); -END $ $mysql>delimiter; ... After a while ... MySQL>Select* fromT2;+----+---------------------+| ID | T_time |+----+---------------------+|1| .-Geneva-Geneva  -: Geneva: -||2| .-Geneva-Geneva  -:Geneva: -||3| .-Geneva-Geneva  -:Geneva: -|... ....

3. View new Scheduled Tasks

Mysql>Selectevent_name,last_executed frominformation_schema. EVENTS;+------------+---------------------+| Event_Name | last_executed |+------------+---------------------+| e_daily | .-Geneva-Geneva  -: Geneva: -|+------------+---------------------+MySQL>show Events\g;***************************1. Row ***************************db:db1 name:e_daily definer: [email protected] time Zone:sys TEM type:recurring Execute at:null Interval value:1Interval Field:minute starts: .-Geneva-Geneva  -: Geneva: -ends:null status:enabled Originator:0character_set_client:utf8collation_connection:utf8_general_ci Database Collation:latin1_swedish_ci

[[Email protected]~]# Tail-1/var/log/Mysqld.log .-Geneva-04t08: on: -.311514Z A[Note] Event Scheduler:scheduler thread started with ID A

View the MySQL log to see how it is performing.

4. Modify ALTER Event

 ALTER     = {User |  Current_User}] EVENT event_name     [on SCHEDULE SCHEDULE]     [on completion [NOT] PRESERVE]     [RENAME to New_event_ Name]     | DISABLE | DISABLE on SLAVE]      ' Comment '  ]     [do event_body]

The ALTER EVENT statement can modify one or more properties of an event, and the syntax is exactly the same as the Create EVENT statement, except that the event can be renamed with the Rename to clause.

For example:

　　ALTER EVENT olddb. MyEvent RENAME to newdb. MyEvent;

5. Delete the Drop event

DROP EVENT [IF EXISTS] event_name;

Deletes a defined event.

Use of the MySQL event scheduler event