MariaDB / MySQL – Event Scheduler

測試環境為 CentOS 7 x86_64 (虛擬機)

在 Linux 環境可以透過 crontab (自動排程)來定期執行任務,如果是要自動執行 SQL 指令(如:定期刪除資料…),可以透過 SQL 的 Event Scheduler

SQL Event Scheduler 預設沒有啟動,須改設定(在 [mysqld] 下面新增 event_scheduler = ON).

[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)

在測試 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() );
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> INSERT INTO employee (Name ,ts ) VALUES ('Ben2' , DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 7 DAY) );
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> INSERT INTO employee (Name ,ts ) VALUES ('Ben3' , DATE_SUB(now() , INTERVAL 7 DAY) );
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> select * from employee;
+------+---------------------+
| Name | ts                  |
+------+---------------------+
| Ben1 | 2019-05-21 17:00:34 |
| Ben2 | 2019-05-14 17:03:02 |
| Ben3 | 2019-05-14 17:16:54 |
+------+---------------------+
3 rows in set (0.00 sec)

MariaDB [testdb]> SELECT * FROM employee where ts > DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 7 DAY) ;
+------+---------------------+
| Name | ts                  |
+------+---------------------+
| Ben1 | 2019-05-21 17:00:34 |
+------+---------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> SELECT * FROM employee where ts < DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 7 DAY) ;
+------+---------------------+
| Name | ts                  |
+------+---------------------+
| Ben2 | 2019-05-14 17:03:02 |
| Ben3 | 2019-05-14 17:16:54 |
+------+---------------------+
2 rows in set (0.00 sec)

函數說明:

  • CURRENT_TIMESTAMP()
    功能與 NOW 一樣,都是回傳目前系統的日期時間.
  • 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

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)

檢視一下目前所有 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

還原時則不需要額外參數

[root@localhost ~]# mysql -u root -p testdb < testdb.sql
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料