mysql学习记录（十一）

一、理论：
1.优势：
1)减少系统管理员的操作风险，有利于数据库整体迁移。因数据库迁移时即可同时迁移调度事件
2.场景：
1)定期收集统计信息
2)清理历史数据
3)数据库检查
3.要点：
1)在性能要求高且重要的数据库中要慎重部署和启用调度器
2)过于复杂的更适合用程序
3)开启和关闭事件调度器需要具有超级用户权限
二、实践：
mysql> use test1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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: 1character_set_client: utf8collation_connection: utf8_general_ci  Database Collation: utf8_general_ci1 row in set (0.01 sec)ERROR: No query specifiedmysql> 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: test1Command: Sleep   Time: 247901  State:    Info: NULL*************************** 2. row ***************************     Id: 21   User: root   Host: localhost     db: test1Command: Query   Time: 0  State: NULL   Info: show processlist*************************** 3. row ***************************     Id: 22   User: event_scheduler   Host: localhost     db: NULLCommand: Daemon   Time: 1  State: Waiting for next activation   Info: NULL3 rows in set (0.00 sec)ERROR: No query specifiedmysql> 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: test1Command: Sleep   Time: 248177  State:    Info: NULL*************************** 2. row ***************************     Id: 21   User: root   Host: localhost     db: test1Command: Query   Time: 0  State: NULL   Info: show processlist*************************** 3. row ***************************     Id: 22   User: event_scheduler   Host: localhost     db: NULLCommand: Daemon   Time: 2  State: Waiting for next activation   Info: NULL3 rows in set (0.00 sec)ERROR: No query specifiedmysql> 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: 1character_set_client: utf8collation_connection: utf8_general_ci  Database Collation: utf8_general_ci1 row in set (0.00 sec)ERROR: No query specified
上一篇：生产环境 xfs filesystem 上安装Mariadb_MySQL
下一篇：不同场景下 MySQL 的迁移方案_MySQL