Mysql Linux安装和事件调度器(Event Scheduler)

最新推荐文章于 2024-05-31 11:39:17 发布

zzhongcy

最新推荐文章于 2024-05-31 11:39:17 发布

阅读量5k

点赞数

分类专栏：数据库文章标签：数据库

数据库专栏收录该内容

33 篇文章 3 订阅

订阅专栏

MySQL安装

一、下载MySQL安装文件

完全安装MySQL需要下面6个文件：

MySQL-server-community-5.1.26-0.rhel4.i386.rpm
MySQL-client-community-5.1.26-0.rhel4.i386.rpm
MySQL-shared-community-5.1.26-0.rhel4.i386.rpm
MySQL-devel-community-5.1.26-0.rhel4.i386.rpm
MySQL-test-community-5.1.26-0.rhel4.i386.rpm
MySQL-community-debuginfo-5.1.26-0.rhel4.i386.rpm

下载地址为：http://dev.mysql.com/downloads/mysql/5.1.html#linux-rhel4- x86-32bit-rpms，打开此网页(因为没有找到Fedora版本的，就用Redhat Enterprise 4来代替了，没有问题)，下载所有的需要的上述rpm文件。

二、MySQL安装

rpm文件是Red Hat公司开发的软件安装包，rpm可让Linux在安装软件包时免除许多复杂的手续。该命令在安装时常用的参数是 –ivh ,其中i表示将安装指定的rmp软件包，V表示安装时的详细信息，h表示在安装期间出现“#”符号来显示目前的安装过程。这个符号将持续到安装完成后才停止。

1) MySQL安装服务器端

在有两个rmp文件的目录下运行如下命令：

[root@test1 local]# rpm -ivh MySQL-server*.rpm(*为通配符，省掉了很多的输入，很方便-:)))

显示如下信息：Failed dependencies:perl(DBI) is needed by MySQL-server-5.1.26-0.rhel4.i386出错了!

去 Google下，发现可能是该版本的rpm里增加了很多附加依赖的检查，特别是对所有perl modules的依赖检查，因此用“--nodeps”来强制安装是安全的，我正是这么做的：rpm -ivh MySQL-server*.rpm --nodeps;结果成功：

Preparing... [100%]

1:MySQL-server [100%]

2) MySQL安装客户端

运行如下命令：

[root@test1 local]# rpm -ivh MySQL-client*.rpm
Preparing... [100%]
1:MySQL-client [100%]

如上安装其他的4个rpm文件，应该没有什么问题。

三、MySQL登录

MySQL登录的命令是mysql， mysql 的使用语法如下： mysql [-u username] [-h host] [-p[password]] [dbname] username 与 password 分别是 MySQL用户名与密码，mysql的初始管理帐号是root，没有密码，注意：这个root用户不是Linux的系统用户。MySQL默认用户是 root，由于初始没有密码，第一次进时只需键入mysql即可。

[root@test1 local]# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1 to server version: 4.0.16-standard
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>

出现了“mysql>”提示符，恭喜你，安装成功!

增加了密码后的登录格式如下：

mysql -u root -p

Enter password: (输入密码)

其中-u后跟的是用户名，-p要求输入密码，回车后在输入密码处输入密码。

可是我输入mysql却出错了：

初始密码是空的，我输入的也是空的还是错误!

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

MySQL安装解决方法：重改密码

# /etc/init.d/mysql stop

# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

# mysql -u root mysql

mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root';

mysql> FLUSH PRIVILEGES;

mysql> quit

# /etc/init.d/mysqld restart
# mysql -uroot -p

Enter password:

mysql>搞定!

##########################################################

Mysql中的事件调度器Event Scheduler类似于linux下的crontab计划任务的功能,它是由一个特殊的时间调度线程执行的

一、登录服务器

mysql -uroot -p

二、查看当前是否开启了event scheduler三种方法:

1) SHOW VARIABLES LIKE ‘event_scheduler’; #或：show variables like '%sche%';

2) SELECT @@event_scheduler;
3) SHOW PROCESSLIST;(是否有State为：Waiting for next activation的进程，User为event_scheduler)

三、启动关闭event scheduler方法:

时间调度器是否开启由全局变量event_scheduler决定，它有三个可以设定的值： – OFF : 事件调度器是关闭的，调度线程并没有运行，并且在SHOW PROCESSLIST中不显示，默认值是OFF – ON ：事件调度器是开启的，调度线程并没有运行，并且执行所有的调度事件，通过SHOW PROCESSLIST可以查看Waiting for next activation的进程 – DISABLED : 设定这个值表示Event Scheduler是被禁止的，无法在Mysql运行状态下改变其值。

注：在Mysql启动时如果在my.cnf设置了event_scheduler=ON（OFF or 1 or 0）时，就不能在运行时修改撑DISABLED，如果设置event_scheduler=DISABLED时，就不能在运行时修改其值为ON （ OFF or 1 or 0）

mysql> SELECT @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| DISABLED |
+-------------------+
1 row in set (0.00 sec)

mysql> SET @@global.event_scheduler = 1;
ERROR 1290 (HY000): The MySQL server is running with the --event-scheduler=DISABLED or --skip-grant-tables option so it cannot execute this statement

在mysql运行时开启Event（4种方法均可）：
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

在mysql运行时关闭Event（4种方法均可）：
SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;

四、创建Event:

语法：
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}

说明：
DEFINER默认是CREATE EVENT的用户,可以理解为DEFINER=CURRENT_USER,指该event的用户，服务器在执行该事件时，使用该用户来检查权限；如果设置语法:‘user_name’@‘host_name’，如果当前CREATE EVENT用户没有supser权限，则无法将该event指派给其他用户；如果有super权限，则可以指定任意存在的用户，若不存在，时间执行时报错
IF NOT EXISTS ：如果在同一个schema创建一个已经存在的event_name时不会做任何操作，也不会出错，但会出现warings：该event已经存在；如果不增加此关键词已经存在的话提示ERROR： 1537 (HY000): Event ‘countsum’ already exists
ON SCHEDULE ：用于设置什么时间执行，执行的频率及执行多久的问题
AT timestamp ：表示在给定的datetime或者timestamp的时间执行一次
+ INTERVAL interval：表示从AT timestamp多久之后执行
EVERY interval ：有规律的重复执行
[ENABLE | DISABLE]可是设置该事件创建后状态是否开启或关闭，默认为ENABLE
[COMMENT ‘comment’]可以给该事件加上注释。

event创建时间的3周2天后：
AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 DAY

2分钟10秒：
+ INTERVAL '2:10' MINUTE_SECOND

每6周：
EVERY 6 WEEK

从现在开始30分钟后每12小时执行一次到从现在到4周后结束执行：
EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK

实例：
前提：创建EVENT的用户需要只少对应schema的EVENT权限
最基本的create event只需要三个部分：
1. create event关键字以及一个event名称
2. on schedule子句
3. do子句

1. 在创建事件myevent1小时后执行，执行一条更新

CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
UPDATE myschema.mytable SET mycol = mycol + 1;

2.2014年3月20日12点整清空test表：

CREATE EVENT e_test
ON SCHEDULE AT TIMESTAMP '2014-03-20 12:00:00'
DO TRUNCATE TABLE test.aaa;

3.5天后开启每天定时清空test表：

CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;

4.每天定时清空test表，5天后停止执行

CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;

5.5天后开启每天定时清空test表，一个月后停止执行：

CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
DO TRUNCATE TABLE test.aaa;

6.每天定时清空test表(只执行一次，任务完成后就终止该事件)：

CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ON COMPLETION NOT PRESERVE
DO TRUNCATE TABLE test.aaa;

[ON COMPLETION [NOT] PRESERVE]可以设置这个事件是执行一次还是持久执行，默认为NOT PRESERVE。

五、修改Event:

ALTER
[DEFINER = { user | CURRENT_USER }]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
[DO event_body]
说明：
对于任何一个拥有定义在database里面事件的event权限的用户都可以修改event，并且成功需改后，那个用户就会成为此event的definer

实例：
CREATE EVENT myevent
ON SCHEDULE
EVERY 6 HOUR
COMMENT 'A sample comment.'
DO
UPDATE myschema.mytable SET mycol = mycol + 1;

将上面的event从开始之后每6个小时执行一次改为从开始4个小时后每12小时执行一次

只修改schedule
ALTER EVENT myevent
ON SCHEDULE
EVERY 12 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 4 HOUR;

同时修改schedule和body
ALTER EVENT myevent
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
TRUNCATE TABLE myschema.mytable;
关闭、启动、别名、移动、删除event：

临时关闭某个event
ALTER EVENT myevent DISABLE;

开启某个event
ALTER EVENT myevent ENABLE;

别名某个event
ALTER EVENT olddb.myevent RENAME TO newdb.myevent;

将myevent从olddb库移动到newdb库
ALTER EVENT olddb.myevent RENAME TO newdb.myevent;

删除event
DROP EVENT [IF EXISTS] event_name

五、查询Event信息:

Event信息相关表：
information_schema.events mysql.event

查看事件的创建信息
show create event countsum \G

查看sem库的events信息
USE sem；
SHOW EVENTS \G
或
SHOW EVENTS FROM sem;

参考资料:

http://my.oschina.net/geekwolf/blog/267621

###########################

创建存储过程test
CREATE PROCEDURE test ()
BEGIN
update userinfo set endtime = now() where id = '110';
END;

创建event e_test
create event if not exists e_test
on schedule every 30 second
on completion preserve
do call test();

每隔30秒将执行存储过程test

关闭事件任务
alter event e_test ON COMPLETION PRESERVE DISABLE;

开户事件任务
alter event e_test ON COMPLETION PRESERVE ENABLE;

###########################################################

New Event Feature

By Trudy Pelzer

In this article, I'll give a preliminary description of a new MySQL feature for scheduling and executing tasks. In version 5.1.6, MySQL has added support for events. That is, you can now say: "I want the MySQL server to execute this SQL statement every day at 9:30am, until the end of the year" -- or anything similar that involves any number of SQL statements, and a schedule.

Note that events are new and still in alpha, so there is still a good chance that we'll have to make adjustments as people experiment with them. This article describes the state of affairs only for the 5.1.6 release of MySQL.

While we at MySQL prefer the term "events" to describe this feature, another name is "temporal triggers". Don't confuse this with "temporary" triggers though -- events are triggers that are executed at a specific time, rather than at a specific event on a specific table. Here's a simple example:

CREATE EVENT
e                               /* Event name */
ON SCHEDULE
EVERY 1 WEEK                    /* Interval */
DO
INSERT INTO t VALUES (0);       /* SQL statement */

The result of this statement is that, once every week starting today, MySQL will add a row to table t. This feature will remind Unix/Linux people of the "crontab job" concept; Windows users will recognize it as a "task scheduler". There's no official standard for events in the DBMS world, but MySQL's concept is reminiscent of Oracle's "Job Scheduler" and our syntax is occasionally the same as Sybase SQL Anywhere's CREATE EVENT syntax.

This feature is intended for database administrators. But anyone can use it for one-shot tasks that run in the background, or start at some low-system-activity moment. In that sense, it's like programming a videotape recorder. In other words, it can be tricky, so try to learn first, program later.

To create an event, use the CREATE EVENT statement. But before you do, make sure you turn the event_scheduler on.

event_scheduler

The MySQL event scheduler is a thread that runs in the background looking for events to execute. It spends a lot of time sleeping -- and won't do anything unless the new global variable "event_scheduler" is set to ON (1). So if you want to take advantage of this feature, do the following:

SET GLOBAL event_scheduler = 1;

To turn the feature off, do:

SET GLOBAL event_scheduler = 0;

As with all "SET GLOBAL" statements, you need the SUPER privilege to change the setting of the event_scheduler variable.

It is also possible to start the server with:

mysqld ... --event_scheduler=1

You can see what the current state of affairs is with

SHOW VARIABLES LIKE 'event_scheduler';
or
SELECT @@event_scheduler;

Now let's look at the syntax for events.

CREATE EVENT

CREATE EVENT [ IF NOT EXISTS ] event_name
ON SCHEDULE schedule
[ ON COMPLETION [ NOT ] PRESERVE ]
[ ENABLED | DISABLED ]
[ COMMENT 'comment' ]
DO sql_statement;

The CREATE EVENT statement, um, creates an event.

The "event_name" must be a valid identifier of up to 64 characters (The_Main_Event, e44), with delimiters allowed (`Something To Do`), possibly qualified (database1.event1). Events are database objects, so they are stored within a database and event names must be unique within that database. When checking for uniqueness, MySQL uses case-insensitive comparisons.

The "schedule" can be a timestamp in the future, a recurring interval, or a combination of recurring intervals and timestamps.

The possibilities are:

AT timestamp [+ interval integer_value time_keyword ]
EVERY interval
EVERY interval STARTS timestamp
EVERY interval ENDS timestamp
EVERY interval STARTS timestamp ENDS timestamp

"AT timestamp" means "Do this once at the specified time". The timestamp must contain both date and time (that is, it must be a DATETIME or TIMESTAMP value) and must be in the future -- you cannot make an event which is supposed to have already occurred. To specify an exact time, you can also add an interval to the timestamp (using + INTERVAL, a positive integer and one of YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND); this makes sense only when you're using the CURRENT_TIMESTAMP function. Here are two examples:

CREATE EVENT `Something To Do`
 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
 DO DROP TABLE t;

This event makes the MySQL server drop a table exactly 5 days from now.

CREATE EVENT The_Main_Event
 ON SCHEDULE AT TIMESTAMP '2006-01-20 12:00:00'
 DO DROP TABLE t;

This event makes the MySQL server drop a table on January 20, 2006 at exactly 12 o'clock.

"EVERY interval" means "Do this repeatedly". A recurring interval starts with EVERY, followed by a positive integer plus one of the keywords YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND.

For example:

CREATE EVENT e
 ON SCHEDULE EVERY 1 YEAR
 DO DROP TABLE t;

This event makes MySQL drop table t once each year, starting now.

"EVERY interval [ STARTS timestamp1 ] [ ENDS timestamp2 ]" means "Do this repeatedly, starting at timestamp1 if it's specified, ending at timestamp2 if it's specified". The ENDS value must be later than the STARTS value, of course. For example, this event:

CREATE EVENT e
 ON SCHEDULE EVERY 1 YEAR
   STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
 DO DROP TABLE t;

makes the DBMS drop a table once each year, starting exactly 5 days from now.

This event:

CREATE EVENT e
 ON SCHEDULE EVERY 1 YEAR
   ENDS CURRENT_TIMESTAMP + INTERVAL 5 YEAR
 DO DROP TABLE t;

ensures the table is dropped once each year for five years, starting now.

And this event:

CREATE EVENT e
 ON SCHEDULE EVERY 1 YEAR
   STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
   ENDS CURRENT_TIMESTAMP + INTERVAL 5 YEAR
 DO DROP TABLE t;

causes MySQL to drop a table once each year for five years, starting exactly 5 days from now.

ON COMPLETION [ NOT ] PRESERVE means "When the event execution finishes, then either preserve [or do not preserve] the event". So if you say ON COMPLETION NOT PRESERVE, then the event is a one-timer; it gets dropped once there is no further use for it. If you say ON COMPLETION PRESERVE, then the completed event stays in existence until you drop it deliberately. The default is NOT PRESERVE.

ENABLED | DISABLED means "Create the event in an enabled state | create in a disabled state". When an event is enabled, which is the default, then it is activated as soon as the schedule specifies. When an event is disabled, it is always inactive -- so, even if the scheduled execution time occurs, nothing happens. Sometimes it's useful to create an event in one state, and change the state using another statement, ALTER EVENT.

The "comment" is a string literal. The optional COMMENT clause is analogous to the COMMENT clause in CREATE TABLE ... COMMENT ...; it stores the string in the metadata for documentation purposes.

The "sql_statement" specifies what action will be taken when the event is executed. This is a single statement, but it can be a compound statement, as with stored routines and triggers -- i.e. BEGIN statement1; statement2; END. The general rule is: any SQL statement that can be executed from within a stored routine, can also be executed by an event. Here's a simple example:

CREATE EVENT e
 ON SCHEDULE EVERY 5 SECOND
 DO
  BEGIN
    DECLARE v INTEGER;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
    SET v = 0;
    WHILE v < 5 DO
      INSERT INTO t1 VALUES (0);
      UPDATE t2 SET s1 = s1 + 1;
      SET v = v + 1;
    END WHILE;
  END //

Note the delimiter at statement end: as is usual with a compound statement, you'll need to change the statement terminator from semicolon (;) if you want to create events using the mysql client (use the DELIMITER statement to do so). Otherwise, the semicolon terminators at the end of each SQL statement within the compound statement will be treated as if they terminate the CREATE EVENT statement.

ALTER EVENT

ALTER EVENT event_name
[ ON SCHEDULE schedule ]
[ RENAME TO event_name2 ]
[ ON COMPLETION [ NOT ] PRESERVE ]
[ COMMENT 'comment' ]
[ ENABLED | DISABLED ]
[ DO sql_statement ]

To change an existing event, use the ALTER EVENT statement.

ALTER EVENT must name an event that already exists. The statement's clauses are the same as the CREATE EVENT clauses, but they are all optional. The settings of any clauses that you omit from ALTER EVENT stay as they were specified in the original CREATE EVENT statement (or the last ALTER EVENT statement executed for the event); the settings of any clauses you include change to the new values you specify.

Thus, if you write an ALTER EVENT statement with an ON SCHEDULE clause then the event's schedule changes; if you don't, the schedule remains the same.

ALTER EVENT also includes one clause that is not found in CREATE EVENT -- namely "RENAME TO event_name2". Use this clause to change the name of an event -- there is no RENAME EVENT statement.

Here's an example of an ALTER EVENT that changes schedule, comment, and sql_statement:

ALTER EVENT event1
  ON SCHEDULE EVERY 5 WEEK
  COMMENT 'This happens every 5 weeks'
  DO DROP TABLE t1;

DROP EVENT

DROP EVENT [ IF EXISTS ] event_name;

The DROP EVENT statement should name an event that has already been created by CREATE EVENT. The event is destroyed.

As is usual with MySQL, if you omit the IF EXISTS clause and the event does not exist, the server returns an error:

mysql> DROP EVENT e6;
ERROR 1513 (HY000): Unknown event 'e6'

If you're not sure that an event exists but want to drop it without getting a possible error message, do this:

mysql> DROP EVENT IF EXISTS e6;
Query OK, 1 row affected, 1 warning

mysql.event

To store event metadata, there is a new table in the mysql database: mysql.event. If you don't see this table in your MySQL installation, run the mysql_fix_privilege_tables script to set it up. (When migrating from earlier versions of MySQL, you should always run this script to set up new objects and privileges that might have been added in the new version.)

The effect of CREATE EVENT is to "insert" a new row in mysql.event; the effect of ALTER EVENT is to "update" a row in mysql.event; the effect of DROP EVENT is to "delete" a row in mysql.event. But it's never a good idea to change the mysql.event table directly -- you should always use CREATE, ALTER, or DROP EVENT to make use of the event feature.

To get metadata information about all existing events, execute this statement:

SELECT * FROM mysql.event;

There is no other way to get event metadata. We do not support statements like SHOW EVENTS, SHOW EVENT STATUS, or SELECT ... FROM INFORMATION_SCHEMA.EVENTS.

For example:

mysql> CREATE EVENT e
    ->  ON SCHEDULE EVERY 5 SECOND
    ->  STARTS TIMESTAMP '2006-01-01 16:00:00'
    ->  ENDS TIMESTAMP '2006-12-31 12:00:00'
    ->  ON COMPLETION PRESERVE
    ->  COMMENT 'runs every 5 seconds in 2006'
    ->  DO INSERT INTO tx VALUES (CURRENT_TIMESTAMP, 10);
Query OK, 1 row affected

mysql> SELECT * FROM mysql.event\G
*************************** 1. row ***************************
            db: tp
          name: e
          body:  INSERT INTO tx VALUES (CURRENT_TIMESTAMP, 10)
       definer: tp@localhost
    execute_at: NULL
interval_value: 5
interval_field: SECOND
       created: 2005-12-20 14:53:03
      modified: 2005-12-20 14:53:03
 last_executed: NULL
        starts: 2006-01-01 23:00:00
          ends: 2006-12-31 20:00:00
        status: ENABLED
 on_completion: PRESERVE
       comment: runs every 5 seconds in 2006
1 row in set (0.00 sec)

Here's how to read the result:

the db column shows the name of the database that contains the event
the name column holds the name of the event; recall that it must be unique within the database
the definer column shows whose privileges will be checked when the event is executed; if the user shown no longer has the proper privileges to execute the event's SQL statement, an error will be returned
the execute_at column shows when the event should be executed; this is always NULL unless the ON SCHEDULE clause uses the AT timestamp option
the interval_value column shows the numeric value of how often the event will be executed; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
the interval_field columns shows the datetime value of how often the event will be executed; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
the created column shows the timestamp when the event was created
the modified column shows the timestamp when the event was last modified; this will be the same as the created column if ALTER EVENT has never changed the event
the last_executed column shows the timestamp of the last time the event was executed; in this example, it is NULL because the newly-created event hasn't been executed yet
the starts column shows the timestamp when the event will start executing; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
the ends column shows the timestamp when the event will stop executing; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
the status column shows whether the event is currently enabled or disabled; in this example, it shows ENABLED, the default value
the on_completion column shows whether the event will be preserved or dropped when it is finished; in this example, it shows that the event will be preserved, as specified in the CREATE EVENT statement
the comment column shows the comment included in the CREATE EVENT statement

Look closely at the "STARTS TIMESTAMP '2006-01-01 16:00:00'" and ENDS TIMESTAMP '2006-12-31 12:00:00'" clauses in the example. In mysql.event, the "starts" and "ends" column values are 2006-01-01 23:00:00 and 2006-12-31 20:00:00 respectively, because the values are converted to UTC values.

Here's another example:

mysql> CREATE EVENT e1
    ->  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 MINUTE
    ->  DISABLED
    ->  DO INSERT INTO tx VALUES (CURRENT_TIMESTAMP, 20);
Query OK, 1 row affected

mysql> SELECT * FROM mysql.event\G
*************************** 2. row ***************************
            db: tp
          name: e1
          body:  INSERT INTO tx VALUES (CURRENT_TIMESTAMP, 20)
       definer: tp@localhost
    execute_at: 2006-01-13 03:42:00
interval_value: NULL
interval_field: NULL
       created: 2006-01-12 19:40:00
      modified: 2006-01-12 19:40:00
 last_executed: NULL
        starts: NULL
          ends: NULL
        status: DISABLED
 on_completion: DROP
       comment:
1 row in set (0.00 sec)

Note that the status column shows DISABLED, as specified in the CREATE EVENT statement and that the on_completion column shows DROP, to indicate that the event will not be preserved when it is finished; this is the default.

Privileges

There is a new privilege for events: the EVENT privilege. You can GRANT it for one database, or for all databases:

GRANT EVENT ON database_name.* TO user [ , user ... ];
GRANT EVENT ON *.* TO user [ , user ... ];

You can also REVOKE it for one or all databases:

REVOKE EVENT ON database_name.* FROM user [ , user ... ];
REVOKE EVENT ON *.* FROM user [ , user ... ];

If you do not have the EVENT privilege, you cannot CREATE an event.

To find out who has an EVENT privilege, search the mysql.user table and look for users with Event_priv = 'Y'.

Finding the thread

Every event executes in a thread of its own. Let's create a bad event that causes an infinite loop to run in the background. We'll see how to find that thread and stop the execution.

First make a procedure that has an infinite loop:

DELIMITER //

CREATE PROCEDURE pe () BEGIN  x: LOOP ITERATE x; END LOOP; END//

Next create an event that will invoke the procedure:

CREATE EVENT ee
  ON SCHEDULE EVERY 2 SECOND
  COMMENT 'This is a bad idea'
  DO CALL tp.pe() //

Now get the scheduler going:

DELIMITER ;

SET GLOBAL event_scheduler = 1;

You won't see anything on your client screen when you do this; the event thread doesn't have a screen of its own. But, if you can go back to the console on which you started the mysqld server, you will see the event executing -- there will be a message indicating this.

You might wonder: why does this happen only once? Why doesn't the scheduler start a new thread every minute? Well, if we did things that way, then a bad event like this one would cause the scheduler to try to set up an infinite number of threads. So we decided that if a recurring event is still executing when it's time to do it again, the server won't open another thread and try to execute the event again.

You can see both the scheduler and the executing event with SHOW PROCESSLIST:

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost
     db: tp
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 2
   User: event_scheduler
   Host:
     db: NULL
Command: Connect
   Time: 1
  State: Sleeping
   Info: NULL
*************************** 3. row ***************************
     Id: 31
   User: root
   Host:
     db: tp
Command: Connect
   Time: 0
  State: NULL
   Info: CALL tp.pe(
3 rows in set (0.00 sec)

Since the event is in a loop, it's a good thing that SHOW PROCESSLIST tells us what the process number is. To stop this rogue-elephant process in its tracks, do this:

mysql> KILL 31;           /* the process number */

Naturally this exercise is strictly theoretical, since nobody would ever make a bad event. That's why database administrators sleep well at night.

Things we threw in to make it harder

You can create two events on the same schedule, but there's no way to ensure the order in which they will occur. That is, if you say that event e1 should happen at 2007-01-01 00:00:00, and event e2 should start now and recur every 1 second, then e1 might happen before e2, or e1 might happen after e2 -- there is no way to force a specific order.

An event always runs with definer privileges. So if user Joe creates (defines) the event, then the thread which executes the event will act as if it's Joe. It will have whatever Joe's current privileges are, and the value of the CURRENT_USER variable will be 'Joe'.

You can put SHOW or SELECT statements in the event, but you'll never see the result. In Unix terms, the output is "directed to dev/null".

As with stored routines, execution of an event statement doesn't change the count of times that statement has occurred. So there is no effect on SHOW STATISTICS.

zzhongcy

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql Linux安装和事件调度器(Event Scheduler)

MySQL安装一、下载MySQL安装文件完全安装MySQL需要下面6个文件： MySQL-server-community-5.1.26-0.rhel4.i386.rpm MySQL-client-community-5.1.26-0.rhel4.i386.rpm MySQL-shared-community-5.1.26-0.rhel4.i386.rpm M
复制链接

扫一扫