Detailed explanation of graphic code of mysql event event scheduler

The following editor will bring you a commonplace storymysql event EventScheduler (must read). The editor thinks it is quite good, so I will share it with you now and give it a reference. Let’s follow the editor and take a look.

##Overview

MySQL also has its own event scheduler, which can simply be understood as the crontab job of

linux, but for SQL applications, its function is more Complete and easier to maintain. Personally, I feel that if too many are created, it may affect DB performance and make it difficult to debug#The main content of MySQL event scheduler##.

#Master switch

The parameter event_scheduler is the master switch of the event scheduler. Generally speaking, it is good to set it to ON or OFF. It is not recommended. Set to disabled, if set to ON, show processlistYou can see the thread

Create, modify, view, etc. Syntax

Regarding how to create and modify events, there is no description here. The creation syntax is as follows. For the specific meaning, please refer to the following introduction to the event information table. You can also refer to the official website document link, http://dev.mysql.com/doc/. refman/5.6/en/create-event.html

View the created event, after entering the current db, show create event xxx\G

Event information query and meaning

To view the status information of an event, you can Check mysql.event or information_schema.events, or simply switch to the current DB and execute show events; the contents of the three are basically the same. Information_schema cannot copy the data, and the following names and starts times have been changed for better reading. The information in information_schema.events is explained as an example

#EVENT_CATALOG:
is generally def, regardless of

EVENT_SCHEMA：The schema where the event is located

EVENT_NAME：The name of the event

DEFINER: The definer of event is consistent with the result of selectcurrent_user() by default when defining this event. If the user has

super

permissions, it can be specified as Other usersTIME_ZONE：The time zone used by event, the default is system, it is recommended not to modify it

EVENT_BODY：It’s usually SQL, don’t worry about it

EVENT_DEFINITION: The content of the event can be a specific SQL such as insert, or it can be a call

Operations of stored procedures

##EVENT_TYPE: This parameter is more important. It is specified when defining. There are two values: RECURRING and ONE TIME, RECURRING means that it will be executed repeatedly as long as the conditions are met, while ONE TIME will only call once

EXECUTE_AT: is valid for one-time type events, if it is RECURRING type The event is generally NULL

, indicating the estimated execution time of the event

INTERVAL_VALUE: is valid for RECURRING type events, indicating the execution interval length

INTERVAL_FIELD: Valid for RECURRING type events, indicating the unit of execution interval, usually SECOND, DAY and other values, please refer to the creation syntax

SQL_MODE:SQL_MODE used by the current event

STARTS:It is valid for RECURRING type events, indicating an event The point in time from which to start execution is similar to the one-time EXECUTE_AT function. If it is NULL, it means that execution will start as soon as the conditions are met

ENDS: Valid for RECURRING type events, indicating the point in time after which an event will no longer be executed. If it is NULL, it will never stop

STATUS:Generally there are three values, ENABLED, DISABLED and SLAVESIDE_DISABLED, where ENABLED means activating this event. The event will be executed as long as it meets other conditions; the event will not be executed if the DISABLED status is changed. SLAVESIDE_DISABLED means that the event will not be executed on the slave library. Special attention should be paid not to execute any form of event on the slave library, because if the main library executes it once, copies it to the slave library, and then executes it again from the slave library, the data will be inconsistent. Generally speaking, just disable the event on the slave library. Just turn on the main switch event_scheduler.

ON_COMPLETION: There are only two values, PRESERVE and NOT PRESERVE, PRESERVE

CREATED: The creation time of the event

LAST_ALTERED: The time when the event was last modified

LAST_EXECUTED: The last execution time of the event. If it is NULL, it means it has never been executed.

EVENT_COMMENT: Comment information of the event

ORIGINATOR: When the current event was created server-id, used for master-slave processing, such as SLAVESIDE_DISABLED

CHARACTER_SET_CLIENT: clientcharacter set when the event is created, that is, character_set_client

COLLATION_CONNECTION: when the event is created The connection character verification rules, that is, collation_connection

DATABASE_COLLATION: The database character set verification rules when the event is created

EVENT permission management

1 Set event_scheduler system variable, super_priv permission is required

2 Creating, modifying and deleting events requires the EVENT permission of the user, which is schema level

3 Corresponding to the specific content of the event, the corresponding permissions are required. For example, if there is an insert operation on a certain table in the event, then the user needs to perform an insert operation on the table, otherwise LAST_EXECUTED will always be in the state of NULL

EVENT Query

Use the following command to view event-related information statistics since the DB was started

mysql> showglobal status like '%event%';
+--------------------------+-------+
|Variable_name | Value |
+--------------------------+-------+
|Com_alter_event | 0 |
|Com_create_event | 2 |
|Com_drop_event | 2 |
|Com_show_binlog_events | 0 |
|Com_show_create_event | 191 |
|Com_show_events | 40 |
|Com_show_relaylog_events | 0 |
+--------------------------+-------+
7 rows in set(0.00 sec)

Usage suggestions

1 If the main library has been executed, the slave library must ensure that the event will not be executed (unless the event is intentionally created on the slave)

2 It is strictly prohibited to directly operate the mysql.event table for creation, deletion and other operations. , but implemented through formal syntax such as create, otherwise it will cause metadata confusion and various inexplicable problems will occur, such as events not being executed or being executed repeatedly. At this time, the problem can usually be solved only by restarting the DB.

3 If the created event involves massive data changes, sufficient testing must be done to ensure that it does not affect the existing network service

4 If you need to back up the DB with the event, you need to add it when mysqldump --event parameter

The above is the detailed content of Detailed explanation of graphic code of mysql event event scheduler. For more information, please follow other related articles on the PHP Chinese website!