Mysql learning record (11) -- Event scheduler _ MySQL

I. theory: 1. Advantages: 1) reduce the operation risks of the system administrator and facilitate the overall database migration. During database migration, scheduling events can be migrated at the same time. Scenario 2: 1) collect statistics regularly; 2) clear historical data; 3) check the database. key points: 1) important data with high performance requirements I. theory:

1. advantages:
1) reduce the operation risks of the system administrator and facilitate the overall migration of the database. The scheduling event can be migrated simultaneously during database migration.
2. scenario:
1) collect statistics regularly
2) clear historical data
3) database check
3. key points:
1) exercise caution in deploying and enabling schedulers in databases with high performance requirements and importance
2) too complex and more suitable for applications
3) The Super User permission is required to enable and disable the event scheduler.

II. practice:

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