MySQL Database events scheduling (event)

The event scheduler in MySQL can periodically increase, delete, and execute operations on the database, which is equivalent to temporary triggers in the database, as with the execution plan tasks in the Linux system, which can greatly reduce the workload.

1. Turn on Event Scheduler

[[email protected] ~]# VIM/USR/MY.CNF--Enable scheduler by adding the following statement to the configuration file

Event_scheduler=1

[Email protected] ~]#/etc/init.d/mysql restart
error! MySQL server PID file could not being found!
Starting MySQL ... success!

[Email protected] ~]#

2. See if event scheduling is turned on

[Email protected] ~]# mysql-u root-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 2
Server version:5.6.21 MySQL Community Server (GPL)

Copyright (c), the Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql> Show variables like "event_%"; --Check if the scheduler is enabled
+-----------------+-------+
| variable_name | Value |
+-----------------+-------+
| Event_scheduler | On |
+-----------------+-------+
1 row in Set (0.00 sec)

Mysql>?              Create event; --View the syntax for creating events
Name: ' CREATE EVENT '
Description:
Syntax:
CREATE
[definer = {User | Current_User}]
EVENT
[IF not EXISTS]
Event_Name
On 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}
mysql> CREATE DATABASE test123;
Query OK, 1 row Affected (0.00 sec)
Mysql> \u test123
Database changed

Mysql>

3. Create the event schedule 5 seconds after creating the T table

Mysql> Create event if not exists event_t on schedule at Current_timestamp + interval 5 second does create table T (A in T,b nchar (Ten), C timestamp);
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show events; --See if the event was created successfully
+---------+----------+----------------+-----------+-----------+------------+----------------+----------------+- --------------------+------+---------+------------+----------------------+----------------------+-------------- ------+
| Db | Name | Definer | Time Zone | Type | Execute at | Interval value | Interval Field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+---------+----------+----------------+-----------+-----------+------------+----------------+----------------+- --------------------+------+---------+------------+----------------------+----------------------+-------------- ------+
| test123 | Event_t1 |[email protected]| SYSTEM | Recurring | NULL | 5 | SECOND | 2014-11-12 15:29:13 | NULL |          ENABLED | 0 | UTF8 | Utf8_general_ci | Latin1_swedish_ci |
+---------+----------+----------------+-----------+-----------+------------+----------------+----------------+- --------------------+------+---------+------------+----------------------+----------------------+-------------- ------+
1 row in Set (0.00 sec)

Mysql> Show tables;
Empty Set (0.00 sec)

Mysql> Show tables; --Create TABLE success
+-------------------+
| tables_in_test123 |
+-------------------+
| T |
+-------------------+
1 row in Set (0.00 sec)

Mysql> desc t;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| A | Int (11) |     YES | |                             NULL | |
| B | char (10) |     YES | |                             NULL | |
| C | Timestamp |     NO | | Current_timestamp | On Update Current_timestamp |
+-------+-----------+------+-----+-------------------+-----------------------------+
3 rows in Set (0.01 sec)

Mysql>

4. Create event schedule insert data in table every 5 seconds

Mysql> Create event if not exists event_t1 on schedule every 5 second does insert into T values (1,1,sysdate ());
Query OK, 0 rows affected (0.01 sec)

Mysql> select * from T; --View Event execution data
+------+------+---------------------+
| A | B | C |
+------+------+---------------------+
| 1 | 1 | 2014-11-12 15:33:31 |
| 1 | 1 | 2014-11-12 15:33:36 |
| 1 | 1 | 2014-11-12 15:33:41 |
| 1 | 1 | 2014-11-12 15:33:46 |
| 1 | 1 | 2014-11-12 15:33:51 |
| 1 | 1 | 2014-11-12 15:33:56 |
| 1 | 1 | 2014-11-12 15:34:01 |
+------+------+---------------------+
7 Rows in Set (0.00 sec)

Mysql>

5. Create event dispatch 10 seconds after deleting all data in T table

Mysql> Create event if not exists event_t2 on schedule every ten second do truncate TABLE t;
Query OK, 0 rows Affected (0.00 sec)

Mysql> select * from T;
+------+------+---------------------+
| A | B | C |
+------+------+---------------------+
| 1 | 1 | 2014-11-12 15:36:36 |
| 1 | 1 | 2014-11-12 15:36:41 |
+------+------+---------------------+
2 rows in Set (0.00 sec)

Mysql> select * from T;
Empty Set (0.00 sec)

Mysql>

6. Delete t table data at specified time

Mysql> Create event if not exists event_t2 on schedule @ timestamp ' 2014-11-12 15:39:00 ' do truncate table t;;
Query OK, 0 rows affected, 1 Warning (0.02 sec)

Mysql> select * from T;
Empty Set (0.00 sec)

Mysql>

7. Create Event Scheduler to delete t table data daily

Mysql> Create event if not exists event_t2 on schedule every 1 day do truncate TABLE t;
Query OK, 0 rows affected, 1 Warning (0.00 sec)

Mysql> select * from T;
Empty Set (0.00 sec)

Mysql>

8. Create the event schedule 5 days after the deletion of data in the T table, one months after the stop

Mysql> Create event if not exists event_t2 on schedule every 1 day starts Current_timestamp + interval 5 day ends Curr Ent_timestamp + interval 1 month do truncate TABLE t;
Query OK, 0 rows affected, 1 Warning (0.00 sec)

Mysql>

9. Disable Event Scheduler

Mysql> alter event EVENT_T2 disable;
Query OK, 0 rows Affected (0.00 sec)

Mysql>

10. Enable Event Scheduler

Mysql> alter event EVENT_T2 enable;
Query OK, 0 rows Affected (0.00 sec)

Mysql>

11. Modify the event Scheduler after 10 days to enable

Mysql> alter event event_t2 on schedule every;
Query OK, 0 rows Affected (0.00 sec)

Mysql>

12. Renaming the event Scheduler

Mysql> Alter event event_t2 rename to Event_t1;
Query OK, 0 rows Affected (0.00 sec)

Mysql>

13. View the event Scheduler information

Mysql> Show events like "Event_t1" \g;
1. Row ***************************
Db:test123
Name:event_t1
Definer:[email protected]
Time Zone:system
Type:recurring
Execute At:null
Interval value:10
Interval Field:day
Starts:2014-11-12 15:47:31
Ends:null
Status:enabled
originator:0
Character_set_client:utf8
Collation_connection:utf8_general_ci
Database Collation:latin1_swedish_ci
1 row in Set (0.00 sec)

ERROR:
No query specified

Mysql>

14. View the contents of the event Scheduler

Mysql> Show Create event event_t1 \g;
1. Row ***************************
Event:event_t1
Sql_mode:strict_trans_tables,no_engine_substitution
Time_zone:system
Create event:create definer= ' root ' @ ' localhost ' Event ' event_t1 ' on SCHEDULE every ten day starts ' 2014-11-12 15:47:31 ' on Completion not PRESERVE ENABLE do truncate TABLE t
Character_set_client:utf8
Collation_connection:utf8_general_ci
Database Collation:latin1_swedish_ci
1 row in Set (0.00 sec)

ERROR:
No query specified

Mysql>

MySQL Database events scheduling (event)