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 oflinux, 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
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
superpermissions, 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 proceduresEXECUTE_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 eventINTERVAL_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!