时间：2021-07-01 10:21:17 帮助过：31人阅读
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.rhel
[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中的事件调度器Event Scheduler类似于linux下的crontab计划任务的功能,它是由一个特殊的时间调度线程执行的
mysql -uroot -p
2) SELECT @@event_scheduler;
3) SHOW PROCESSLIST;(是否有State为：Waiting for next activation的进程，User为event_scheduler)
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;
说明：
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。
同时修改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
参考资料:
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;
###########################################################
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.
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 [ 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" 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 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 [ 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
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:
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.
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'.
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.
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.