MySQL database Event Scheduling)

MySQL database Event Scheduling)

The event scheduler in MySQL can regularly add, delete, and execute operations on the database, which is equivalent to a temporary trigger in the database. It is the same as the execution plan task in Linux, which can greatly reduce the workload.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

1. Enable the event Scheduler

[Root @ node1 ~] # Vim/usr/my. cnf -- Add the following statement to the configuration file to enable the Scheduler

Event_scheduler = 1

[Root @ node1 ~] #/Etc/init. d/mysql restart
ERROR! MySQL server PID file cocould not be found!
Starting MySQL... SUCCESS!

[Root @ node1 ~] #

 

2. Check whether event scheduling is enabled.

[Root @ node1 ~] # 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) 2000,201 4, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be 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 whether 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 an event
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 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 a table t five seconds after event Scheduling

Mysql> create event if not exists event_t on schedule at current_timestamp + interval 5 second do create table t (a int, B nchar (10), c timestamp );
Query OK, 0 rows affected (0.00 sec)

Mysql> show events; -- check whether the event is successfully created
+ --------- + ---------- + ---------------- + ----------- + ------------ + ---------------- + ------ + ----------- + ------------ + Accept +
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+ --------- + ---------- + ---------------- + ----------- + ------------ + ---------------- + ------ + ----------- + ------------ + Accept +
| Test123 | event_t1 | root @ localhost | SYSTEM | RECURRING | NULL | 5 | SECOND | 15:29:13 | NULL | ENABLED | 0 | utf8 | utf8_general_ci | latin1_swedish_ci |
+ --------- + ---------- + ---------------- + ----------- + ------------ + ---------------- + ------ + ----------- + ------------ + Accept +
1 row in set (0.00 sec)

Mysql> show tables;
Empty set (0.00 sec)

Mysql> show tables; -- table created successfully
+ ------------------- +
| 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>

For more details, please continue to read the highlights on the next page:

1 2 Next Page