測試環境為 CentOS 7 / CentOS 8 x86_64 (虛擬機)
在 Linux 環境可以透過 crontab (自動排程)來定期執行任務,如果是要自動執行 SQL 指令(如:定期刪除資料…),可以透過 SQL 的 Event Scheduler
啟動 Event Scheduler
SQL Event Scheduler 預設沒有啟動,須改設定(在 [mysqld] 下面新增 event_scheduler = ON).
設定檔 CentOS 7 檔案位於 /etc/my.cnf.d/server.cnf , CentOS 8 檔案位於 /etc/my.cnf.d/mariadb-server.cnf .
[root@localhost ~]# cat /etc/my.cnf.d/server.cnf # # These groups are read by MariaDB server. # Use it for options that only the server (but not clients) should see # # See the examples of server my.cnf files in /usr/share/mysql/ # # this is read by the standalone daemon and embedded servers [server] # this is only for the mysqld standalone daemon [mysqld] event_scheduler = ON # this is only for embedded server [embedded] # This group is only read by MariaDB-5.5 servers. # If you use the same .cnf file for MariaDB of different versions, # use this group for options that older servers don't understand [mysqld-5.5] # These two groups are only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] [mariadb-5.5]
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# systemctl status mariadb
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: active (running) since 二 2019-05-21 14:43:52 CST; 5s ago
Process: 8523 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 8491 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 8522 (mysqld_safe)
Tasks: 21
CGroup: /system.slice/mariadb.service
├─8522 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─8696 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql...
檢視一下 SQL Event Scheduler 已啟動 (ON).
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SELECT @@event_scheduler; +-------------------+ | @@event_scheduler | +-------------------+ | ON | +-------------------+ 1 row in set (0.00 sec)
暫時性開啟 EVENT SCHEDULER (mysql , mariadb 服務重啟後失效)
MariaDB [(none)]> SELECT @@event_scheduler; +-------------------+ | @@event_scheduler | +-------------------+ | OFF | +-------------------+ 1 row in set (0.001 sec) MariaDB [(none)]> SET GLOBAL event_scheduler = ON; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> SELECT @@event_scheduler; +-------------------+ | @@event_scheduler | +-------------------+ | ON | +-------------------+ 1 row in set (0.000 sec)
建立測試用資料
在測試 SQL Event Scheduler 功能前 (預計寫一個自動刪除超過七天資料的排程),先來建立一個資料庫 testdb 資料表 employee 欄位為使用者(Name) 與時間 (ts 為 timestamp 欄位,預設值為 current_timestamp ,日期時間函數,功能與 NOW 一樣,都是回傳目前系統的日期時間,on update CURRENT_TIMESTAMP 資料更新時,time stamp 也一同更新.
並建立3筆資料,一筆為目前時間,另外2筆為 7 天前.
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE TABLE employee (Name CHAR(20),ts TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)
MariaDB [testdb]> DESCRIBE employee;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| Name | char(20) | YES | | NULL | |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.03 sec)
MariaDB [testdb]> INSERT INTO employee (Name ,ts ) VALUES ('Ben1' , CURRENT_TIMESTAMP() ) , ('Ben2' , DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 7 DAY) ) , ('Ben3' , DATE_SUB(now() , INTERVAL 7 DAY) );
Query OK, 3 rows affected (0.002 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [testdb]> SELECT * FROM employee;
+------+---------------------+
| Name | ts |
+------+---------------------+
| Ben1 | 2020-04-08 18:09:05 |
| Ben2 | 2020-04-01 18:09:05 |
| Ben3 | 2020-04-01 18:09:05 |
+------+---------------------+
3 rows in set (0.00 sec)
MariaDB [testdb]> SELECT * FROM employee WHERE ts > DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 7 DAY) ;
+------+---------------------+
| Name | ts |
+------+---------------------+
| Ben1 | 2020-04-08 18:09:05 |
+------+---------------------+
1 row in set (0.00 sec)
MariaDB [testdb]> SELECT * FROM employee WHERE ts < DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 7 DAY) ;
+------+---------------------+
| Name | ts |
+------+---------------------+
| Ben2 | 2020-04-01 18:09:05 |
| Ben3 | 2020-04-01 18:09:05 |
+------+---------------------+
2 rows in set (0.00 sec)
函數說明:
- NOW() , CURRENT_TIMESTAMP()
NOW , CURRENT_TIMESTAMP 功能一樣,都是回傳目前系統的日期時間. - DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 2 YEAR)
將剪去指定的時間.INTERVAL 後面可以接的時間為 DAY, YEAR, SECOND, MINUTE_SECOND, DAY_SECOND, DAY_HOUR, SECOND_MICROSECOND.
裡面用到一些日期與時間的函數,詳細使用方式請參考 https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
建立 Event Scheduler
SQL Event Scheduler 語法
MariaDB [(none)]> HELP CREATE EVENT
Name: 'CREATE EVENT'
Description:
Syntax:
CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;
schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
建立排程
建立 SQL Event Scheduler (每天執行一次,時間為現在)來自動刪除 testdb.employee 裡面 ts (timestamp) 欄位超過七天的.
MariaDB [testdb]> CREATE EVENT testdb.remove7d
-> ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP
-> DO
-> DELETE FROM testdb.employee WHERE ts < DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 7 DAY) ;
Query OK, 0 rows affected (0.00 sec)
包含多個敘述子時可以使用以下的方式.
DELIMITER //
CREATE EVENT testdb.remove7d
ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP
DO
BEGIN
DELETE FROM testdb.employee WHERE ts < DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 7 DAY) ;
END;
//
DELIMITER ;
檢視一下目前所有 Scheduler 定義的 Event .
MariaDB [testdb]> SHOW EVENTS; +--------+----------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +--------+----------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ | testdb | remove7d | root@localhost | SYSTEM | RECURRING | NULL | 1 | DAY | 2019-05-21 17:55:23 | NULL | ENABLED | 0 | utf8 | utf8_general_ci | latin1_swedish_ci | +--------+----------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec)
詳細 event 可以透過下面方式來檢視.
MariaDB [testdb]> SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='remove7d'; +---------------+--------------+------------+----------------+-----------+------------+---------------------------------------------------------------------------------------+------------+------------+----------------+----------------+----------+---------------------+------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+ | EVENT_CATALOG | EVENT_SCHEMA | EVENT_NAME | DEFINER | TIME_ZONE | EVENT_BODY | EVENT_DEFINITION | EVENT_TYPE | EXECUTE_AT | INTERVAL_VALUE | INTERVAL_FIELD | SQL_MODE | STARTS | ENDS | STATUS | ON_COMPLETION | CREATED | LAST_ALTERED | LAST_EXECUTED | EVENT_COMMENT | ORIGINATOR | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION | +---------------+--------------+------------+----------------+-----------+------------+---------------------------------------------------------------------------------------+------------+------------+----------------+----------------+----------+---------------------+------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+ | def | testdb | remove7d | root@localhost | SYSTEM | SQL | DELETE FROM testdb.employee WHERE ts < DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 7 DAY) | RECURRING | NULL | 1 | DAY | | 2019-05-22 10:10:40 | NULL | ENABLED | NOT PRESERVE | 2019-05-22 10:10:40 | 2019-05-22 10:10:40 | 2019-05-22 10:10:40 | | 0 | utf8 | utf8_general_ci | latin1_swedish_ci | +---------------+--------------+------------+----------------+-----------+------------+---------------------------------------------------------------------------------------+------------+------------+----------------+----------------+----------+---------------------+------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+ 1 row in set (0.02 sec)
檢視一下資料,的確超過七天的資料都刪除了.
MariaDB [testdb]> SELECT * FROM employee; +------+---------------------+ | Name | ts | +------+---------------------+ | Ben1 | 2019-05-21 17:00:34 | +------+---------------------+ 1 row in set (0.00 sec)
暫停執行排程
要暫停執行排程可以透過下面方式.
MariaDB [testdb]> ALTER EVENT remove7d DISABLE; Query OK, 0 rows affected (0.00 sec)
移除排程
要移除排程可以透過下面方式.
MariaDB [testdb]> DELETE FROM mysql.event WHERE name = 'remove7d'; Query OK, 1 row affected (0.00 sec)
備份排程
備份資料庫時需要多加參數 events 才能把,event 備份下來.
[root@localhost ~]# mysqldump -u root -p --events testdb > testdb.sql
還需把 mysql.event 把 Event 相關訊息備份起來.
[root@localhost ~]# mysqldump -u root -p mysql event > mysql_event.sql
還原時則不需要額外參數
[root@localhost ~]# mysql -u root -p testdb < testdb.sql Enter password:
[root@localhost ~]# mysql -u root -p mysql < mysql.user.sql Enter password:
Event 權限
前面使用 root 登入,預設具有建立 Event 的權限,當建立時 其 DEFINER 就是該使用者 root@localhost
現在使用另外一個使用者 user1 登入,會發現他無法建立 EVENT.
[root@localhost ~]# mysql -u user1 -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CREATE EVENT testdb.remove7d ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP DO DELETE FROM testdb.employee WHERE ts < DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 7 DAY); ERROR 1044 (42000): Access denied for user 'user1'@'localhost' to database 'testdb'
我們只需要給該使用者 EVENT 的權限即可(具有建立,修改,刪除 EVENT 的權限).
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> GRANT EVENT ON testdb.* TO user1@localhost; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW GRANTS FOR user1@localhost; +--------------------------------------------------------------------------------------------------------------+ | Grants for user1@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' | | GRANT SELECT, EVENT ON `testdb`.* TO 'user1'@'localhost' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
回到剛剛 user1 使用者(需重新登出,登入).即可建立 EVENT 了.
MariaDB [(none)]> exit Bye [root@localhost ~]# mysql -u user1 -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 5 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CREATE EVENT testdb.remove7d ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP DO DELETE FROM testdb.employee WHERE ts < DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 7 DAY); Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> SHOW EVENTS FROM testdb; +--------+----------+-----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +--------+----------+-----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ | testdb | remove7d | user1@localhost | SYSTEM | RECURRING | NULL | 1 | DAY | 2020-01-08 16:27:54 | NULL | ENABLED | 0 | utf8 | utf8_general_ci | latin1_swedish_ci | +--------+----------+-----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec)
錯誤排除
設定好的 EVENT 沒有執行,可過下面幾種方式來確認,透過 SHOW PROCESSLIST 檢視 event_scheduler 是否正常運行,沒有的話可以重啟服務.
MariaDB [(none)]> SHOW PROCESSLIST; +----+-----------------+-----------+--------+---------+------+-----------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-----------------+-----------+--------+---------+------+-----------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 8 | root | localhost | testdb | Query | 0 | Init | SHOW PROCESSLIST | 0.000 | | 9 | event_scheduler | localhost | NULL | Daemon | 140 | Waiting for next activation | NULL | 0.000 | +----+-----------------+-----------+--------+---------+------+-----------------------------+------------------+----------+ 7 rows in set (0.000 sec)
檢視該 EVENT 的上次執行時間點.
MariaDB [(none)]> SELECT EVENT_NAME , LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='remove7d'; +------------+---------------+ | EVENT_NAME | LAST_EXECUTED | +------------+---------------+ | remove7d | NULL | +------------+---------------+ 1 row in set (0.001 sec)
沒有解決問題,試試搜尋本站其他內容