mysql 学习记要（十一）-事件调度器_mysql

mysql 学习记录（十一）–事件调度器

一、理论：

1.优势：
1)减少系统管理员的操作风险，有利于数据库整体迁移。因数据库迁移时即可同时迁移调度事件
2.场景：
1)定期收集统计信息
2)清理历史数据
3)数据库检查
3.要点：
1)在性能要求高且重要的数据库中要慎重部署和启用调度器
2)过于复杂的更适合用程序
3)开启和关闭事件调度器需要具有超级用户权限

二、实践：

mysql> use test1; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A  Database changed mysql> create table test(id1 varchar(10),create_time datetime); Query OK, 0 rows affected (0.02 sec)  mysql> create event test_event_1     -> on schedule     -> every 5 second      -> do      -> insert into test1.test(id1,create_time)     -> values ('test',now()); Query OK, 0 rows affected (0.01 sec)  mysql> show events /G; *************************** 1. row ***************************                   Db: test1                 Name: test_event_1              Definer: root@localhost            Time zone: SYSTEM                 Type: RECURRING           Execute at: NULL       Interval value: 5       Interval field: SECOND               Starts: 2015-10-14 06:33:17                 Ends: NULL               Status: ENABLED           Originator: 1 character_set_client: utf8 collation_connection: utf8_general_ci   Database Collation: utf8_general_ci 1 row in set (0.01 sec)  ERROR:  No query specified  mysql> select * from test; Empty set (0.00 sec)  mysql> show variables like '%scheduler%'; +-----------------+-------+ | Variable_name   | Value | +-----------------+-------+ | event_scheduler | OFF   | +-----------------+-------+ 1 row in set (0.00 sec)  mysql> set global event_scheduler = 1 ; Query OK, 0 rows affected (0.00 sec)  mysql> show variables like '%scheduler%'; +-----------------+-------+ | Variable_name   | Value | +-----------------+-------+ | event_scheduler | ON    | +-----------------+-------+ 1 row in set (0.00 sec)  mysql> show processlist /G; *************************** 1. row ***************************      Id: 17    User: root    Host: localhost      db: test1 Command: Sleep    Time: 247901   State:     Info: NULL *************************** 2. row ***************************      Id: 21    User: root    Host: localhost      db: test1 Command: Query    Time: 0   State: NULL    Info: show processlist *************************** 3. row ***************************      Id: 22    User: event_scheduler    Host: localhost      db: NULL Command: Daemon    Time: 1   State: Waiting for next activation    Info: NULL 3 rows in set (0.00 sec)  ERROR:  No query specified  mysql> select * from test; +------+---------------------+ | id1  | create_time         | +------+---------------------+ | test | 2015-10-14 06:33:57 | | test | 2015-10-14 06:34:02 | | test | 2015-10-14 06:34:07 | | test | 2015-10-14 06:34:12 | | test | 2015-10-14 06:34:17 | | test | 2015-10-14 06:34:22 | +------+---------------------+ 6 rows in set (0.00 sec)  mysql> create event trunc_test     -> on schedule every 1 minute     -> do truncate table test; Query OK, 0 rows affected (0.00 sec)  mysql> select * from test; +------+---------------------+ | id1  | create_time         | +------+---------------------+ | test | 2015-10-14 06:34:52 | | test | 2015-10-14 06:34:57 | | test | 2015-10-14 06:35:02 | | test | 2015-10-14 06:35:07 | | test | 2015-10-14 06:35:12 | | test | 2015-10-14 06:35:17 | | test | 2015-10-14 06:35:22 | | test | 2015-10-14 06:35:27 | | test | 2015-10-14 06:35:32 | | test | 2015-10-14 06:35:37 | +------+---------------------+ 10 rows in set (0.00 sec)  mysql> select * from test; +------+---------------------+ | id1  | create_time         | +------+---------------------+ | test | 2015-10-14 06:37:52 | | test | 2015-10-14 06:37:57 | +------+---------------------+ 2 rows in set (0.00 sec)  mysql> show processlist /G; *************************** 1. row ***************************      Id: 17    User: root    Host: localhost      db: test1 Command: Sleep    Time: 248177   State:     Info: NULL *************************** 2. row ***************************      Id: 21    User: root    Host: localhost      db: test1 Command: Query    Time: 0   State: NULL    Info: show processlist *************************** 3. row ***************************      Id: 22    User: event_scheduler    Host: localhost      db: NULL Command: Daemon    Time: 2   State: Waiting for next activation    Info: NULL 3 rows in set (0.00 sec)  ERROR:  No query specified  mysql> alter event trunc_test disable; Query OK, 0 rows affected (0.02 sec)  mysql> select * from test; +------+---------------------+ | id1  | create_time         | +------+---------------------+ | test | 2015-10-14 06:38:52 | | test | 2015-10-14 06:38:57 | | test | 2015-10-14 06:39:02 | | test | 2015-10-14 06:39:07 | | test | 2015-10-14 06:39:12 | | test | 2015-10-14 06:39:17 | | test | 2015-10-14 06:39:22 | | test | 2015-10-14 06:39:27 | | test | 2015-10-14 06:39:32 | | test | 2015-10-14 06:39:37 | +------+---------------------+ 10 rows in set (0.00 sec)  mysql> select * from test; +------+---------------------+ | id1  | create_time         | +------+---------------------+ | test | 2015-10-14 06:38:52 | | test | 2015-10-14 06:38:57 | | test | 2015-10-14 06:39:02 | | test | 2015-10-14 06:39:07 | | test | 2015-10-14 06:39:12 | | test | 2015-10-14 06:39:17 | | test | 2015-10-14 06:39:22 | | test | 2015-10-14 06:39:27 | | test | 2015-10-14 06:39:32 | | test | 2015-10-14 06:39:37 | | test | 2015-10-14 06:39:42 | | test | 2015-10-14 06:39:47 | | test | 2015-10-14 06:39:52 | | test | 2015-10-14 06:39:57 | | test | 2015-10-14 06:40:02 | | test | 2015-10-14 06:40:07 | +------+---------------------+ 16 rows in set (0.00 sec)  mysql> alter event test_event_1 disable; Query OK, 0 rows affected (0.00 sec)  mysql> select * from test; +------+---------------------+ | id1  | create_time         | +------+---------------------+ | test | 2015-10-14 06:38:52 | | test | 2015-10-14 06:38:57 | | test | 2015-10-14 06:39:02 | | test | 2015-10-14 06:39:07 | | test | 2015-10-14 06:39:12 | | test | 2015-10-14 06:39:17 | | test | 2015-10-14 06:39:22 | | test | 2015-10-14 06:39:27 | | test | 2015-10-14 06:39:32 | | test | 2015-10-14 06:39:37 | | test | 2015-10-14 06:39:42 | | test | 2015-10-14 06:39:47 | | test | 2015-10-14 06:39:52 | | test | 2015-10-14 06:39:57 | | test | 2015-10-14 06:40:02 | | test | 2015-10-14 06:40:07 | | test | 2015-10-14 06:40:12 | | test | 2015-10-14 06:40:17 | | test | 2015-10-14 06:40:22 | | test | 2015-10-14 06:40:27 | +------+---------------------+ 20 rows in set (0.00 sec)  mysql>  drop event test_event_1; Query OK, 0 rows affected (0.01 sec)  mysql> show events /G; *************************** 1. row ***************************                   Db: test1                 Name: trunc_test              Definer: root@localhost            Time zone: SYSTEM                 Type: RECURRING           Execute at: NULL       Interval value: 1       Interval field: MINUTE               Starts: 2015-10-14 06:34:48                 Ends: NULL               Status: DISABLED           Originator: 1 character_set_client: utf8 collation_connection: utf8_general_ci   Database Collation: utf8_general_ci 1 row in set (0.00 sec)  ERROR:  No query specified 

欢迎大家阅读《mysql 学习记要（十一）-事件调度器_mysql》,跪求各位点评，by 搞代码

喜欢 (0)赏

[搞代码]

分享 (0)