MySQL数据库事件调度（Event）

MySQL中的事件调度器可以定时对数据库增加,删除和执行操作,相当于数据库中的临时触发器,与Linux系统中的执行计划任务一样,这样就可以大大降低工作量。 --------------------------------------分割线 --------------------------------------Ubuntu 14.04下安装MySQL http://www.linuxidc.com/Linux/2014-05/102366.htm《MySQL权威指南（原书第2版）》清晰中文扫描版 PDF http://www.linuxidc.com/Linux/2014-03/98821.htmUbuntu 14.04 LTS 安装 LNMP NginxPHP5 （PHP-FPM）MySQL http://www.linuxidc.com/Linux/2014-05/102351.htmUbuntu 14.04下搭建MySQL主从服务器 http://www.linuxidc.com/Linux/2014-05/101599.htmUbuntu 12.04 LTS 构建高可用分布式 MySQL 集群 http://www.linuxidc.com/Linux/2013-11/93019.htmUbuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb http://www.linuxidc.com/Linux/2013-08/89270.htmMySQL-5.5.38通用二进制安装 http://www.linuxidc.com/Linux/2014-07/104509.htm--------------------------------------分割线 --------------------------------------1.开启事件调度器[root@node1 ~]# vim /usr/my.cnf --在配置文件中加入以下语句启用调度器event_scheduler=1[root@node1 ~]# /etc/init.d/mysql restart
ERROR！ MySQL server PID file could not be found！
Starting MySQL... SUCCESS！[root@node1 ~]# 2.查看事件调度是否开启[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, 2014, 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_%"; --查看调度器是否启用
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set （0.00 sec）mysql> ？ create 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 | 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 changedmysql> 3.创建事件调度5秒钟后创建t表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; --查看事件是否创建成功
+---------+----------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| 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 | root@localhost | 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; --创建表成功
+-------------------+
| 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>更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2014-11/109462p2.htm

Oracle数据库中rman备份脚本非常实用Ubuntu 上如何将 MySQL 5.5 数据库迁移到 MariaDB 10相关资讯 MySQL事件调度本文评论查看全部评论（0）