Commonplace talk about MySQL event scheduler (must read)

Overview

MySQL also has its own event scheduler, which can be simply understood as Linux's crontab job. However, for SQL applications, it has more complete functions and is easier to maintain. Personally, I feel that if too many are created, it may affect DB performance and be difficult to debug.

Main contents of MySQL event scheduler

Main switch

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

Create, modify, view, etc. syntax

How to create and modify events is not described 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

To view the created event, after entering the current db, use show create event xxx\G

Information query and meaning of event

To view the status information of an event, you can view 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 make data copies. The following names and start times are changed for better reading. Here we use the information in information_schema.events as an example to explain

EVENT_CATALOG: Generally, it is def, regardless of

EVENT_SCHEMA: The schema where the event is located

EVENT_NAME: The name of the event

DEFINER: The definer of the event. This is consistent with the default result of selectcurrent_user() when defining the event. If the user has super privileges, you can specify another user.

TIME_ZONE: The time zone used by the event. The default is system. It is recommended not to change it.

EVENT_BODY: Usually SQL, don't worry about it

EVENT_DEFINITION: The content of the event can be a specific SQL such as insert, or an operation that calls a stored procedure

EVENT_TYPE: This parameter is very important and is specified when defining. It has two values: RECURRING and ONE TIME. RECURRING means that the event will be executed repeatedly as long as the conditions are met, while ONE TIME will only be called once.

EXECUTE_AT: Valid for one-time events. If it is a RECURRING event, it is usually NULL, indicating the estimated execution time of the event.

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

INTERVAL_FIELD: valid for RECURRING type events, indicating the unit of the execution interval, usually SECOND, DAY, etc. For more information, refer to the creation syntax

SQL_MODE: The SQL_MODE used by the current event

STARTS: Valid for RECURRING type events, indicating the time point at which an event starts to execute, similar to the one-time EXECUTE_AT function. NULL means execution starts as soon as the condition is met.

ENDS: Valid for RECURRING type events, indicating the time point at which an event will stop executing. If it is NULL, it will never stop.

STATUS: Generally, there are three values: ENABLED, DISABLED, and SLAVESIDE_DISABLED. ENABLED means activating the event. The event will be executed as long as other conditions are met. The DISABLED state means that the event will not be executed. SLAVESIDE_DISABLED means that the event will not be executed on the slave library. Special attention should be paid to not executing any form of event on the slave database, because if the master database executes it once, copies it to the slave database, and then executes it again on the slave database, the data will be inconsistent. Generally speaking, you can just disable the main switch event_scheduler on the slave database.

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

CREATED: The time when the event was created

LAST_ALTERED: The time when the event was last modified

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

EVENT_COMMENT: event comment information

ORIGINATOR: The server-id when the current event was created, used for processing on the master and slave, such as SLAVESIDE_DISABLED

CHARACTER_SET_CLIENT: The client character set when the event was created, that is, character_set_client

COLLATION_CONNECTION: The connection character validation rule when event is created, i.e. collation_connection

DATABASE_COLLATION: Database character set validation rules when event is created

EVENT Permission Management

1 Setting the event_scheduler system variable requires super_priv privilege

2 Creating, modifying, and deleting events requires the user's EVENT permission, which is at the 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 table in the event, then the user needs to insert the table, otherwise LAST_EXECUTED will always be NULL.

EVENT status query

Use the following command to view the event-related 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 master has already executed it, the slave must ensure that the event will not be executed (unless the event is intentionally created on the slave)

2. It is strictly forbidden to directly operate the mysql.event table for creation, deletion and other operations. Instead, they should be implemented through regular syntax such as create. Otherwise, metadata confusion will occur, and various inexplicable problems will arise, such as event not being executed or being executed repeatedly. At this time, the only solution is to restart the DB.

3 If the event you create involves massive data changes, you must conduct sufficient testing to ensure that it does not affect the existing network services.

4 If you need to back up a DB with events, you need to add the --event parameter when running mysqldump.

The above article, "MySQL event scheduler (must read)," is all I want to share with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.