MySQL 5.5创建和查看数据库计划任务（event）

Dear 丶 2024-04-17 23:58 3阅读 0赞

\--查看相关参数  
 mysql> show variables like '%event%';  
 \+---------------------------------------------------+-------+  
 | Variable\_name                                     | Value |  
 \+---------------------------------------------------+-------+  
 | event\_scheduler                                   | OFF   |  
 | performance\_schema\_events\_waits\_history\_long\_size | 10000 |  
 | performance\_schema\_events\_waits\_history\_size      | 10    |  
 \+---------------------------------------------------+-------+  
 3 rows in set (0.00 sec)  
  
 \--开启相关参数  
 mysql> set global event\_scheduler = 1;  
 Query OK, 0 rows affected (0.00 sec)  
  
 mysql> show variables like '%event%';  
 \+---------------------------------------------------+-------+  
 | Variable\_name                                     | Value |  
 \+---------------------------------------------------+-------+  
 | event\_scheduler                                   | ON    |  
 | performance\_schema\_events\_waits\_history\_long\_size | 10000 |  
 | performance\_schema\_events\_waits\_history\_size      | 10    |  
 \+---------------------------------------------------+-------+  
 3 rows in set (0.00 sec)  
  
**--开启参数后，在线程中会出现event\_scheduler相关线程**  
 mysql> show processlist\\G  
  
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 7. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
      Id: 13  
    User: event\_scheduler  
    Host: localhost  
      db: NULL  
 Command: Daemon  
    Time: 474  
   State: Waiting for next activation  
    Info: NULL  
 7 rows in set (0.00 sec)  
  
**--创建计划任务**  
 mysql> CREATE EVENT myevent  
     ->     ON SCHEDULE AT CURRENT\_TIMESTAMP + INTERVAL 1 HOUR  
     ->     DO  
     ->       UPDATE fire.t12 SET sal = sal + 100;  
 Query OK, 0 rows affected (0.02 sec)  
  
**--查看数据库里面的计划任务**  
 mysql> show events from fire\\G  
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                   Db: fire  
                 Name: myevent  
              Definer: system@localhost  
            Time zone: SYSTEM  
                 Type: ONE TIME  
           Execute at: 2016-05-23 02:48:03  
       Interval value: NULL  
       Interval field: NULL  
               Starts: NULL  
                 Ends: NULL  
               Status: ENABLED  
           Originator: 100  
 character\_set\_client: gbk  
 collation\_connection: gbk\_chinese\_ci  
   Database Collation: utf8\_general\_ci  
 1 row in set (0.00 sec)  
  
**--查看计划任务的内容**  
 mysql> show create event myevent\\G  
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                Event: myevent  
             sql\_mode: STRICT\_TRANS\_TABLES,NO\_ENGINE\_SUBSTITUTION  
            time\_zone: SYSTEM  
         Create Event: CREATE DEFINER=\`system\`@\`localhost\` EVENT \`myevent\` ON SCHEDULE AT '2016-05-23 02:48:03' ON COMPLETION NOT PRESERVE ENABLE DO UPDATE fire.t12 SET sal = sal + 100  
 character\_set\_client: gbk  
 collation\_connection: gbk\_chinese\_ci  
   Database Collation: utf8\_general\_ci  
 1 row in set (0.00 sec)  
  
**--禁止计划任务**  
mysql> ALTER EVENT myevent  DISABLE;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2105493/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2105493/