MySQL 排程(Event Scheduler)

一段陣子沒清vps上的東西，同步到Dropbox的檔案瞬間爆增，每隔一段時間都要定期手動清一下，今天一有時間索性加入crontab -e排程一勞永逸，還有另外一個問題，由於我的股票精算師只留七日內的文章，也面臨相同的問題，為了不想讓資料庫文章量太大，造成效能低落，因此希望可以把定期清理資料庫的動作改為自動化，要自動化有很多種做法，也可以用上面提到的 crontab -e 加入排程，再透過 php 或其他方式執行清理的 SQL，Google一下發現 MySQL 有 Event 這個排程的東東，就來試用看看囉！

小蛙就直接記錄自己做了哪些事情，從官網上面以及參考資料中查到建立 event 的基本方法

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}

建立 MySQL Event

看起來有點複雜，用下面的簡單例子直接套用(這個部落格有非常詳細的說明)，小蛙要的功能是「馬上把7天以前的文章刪除，並且每天執行一次刪除動作」，這樣就簡單多了，不需要用到上面複雜的說明。

CREATE EVENT my_event
    ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP
    DO 
       DELETE FROM `mytable` WHERE `publish_date` < DATE_SUB(now(), INTERVAL 7 DAY)

Line 1: 建立一個叫做 my_event 的事件
Line 2: 從現在開始執行一次後，每天都會執行一次
Line 3: 要做的動作是刪除 mytable 中，小於現在時間減掉七天(也就是七天前)的資料

到這邊看起來似乎很簡單，但小蛙為了測試把時間設定成 5 分鐘，遲遲不見 my_event 去做相對應的工作 ><。

開啟 MySQL log (非必需)

這邊順便紀錄一下把 MySQL log 打開的過程(參考自這裡)

在 /etc/mysql/my.cnf 中的 [mysqld] 區塊內加入

general_log_file = /var/log/mysql.log
general_log = 0

原本被註解掉的話，把他打開，把general_log = 0 改成 1，到上述路徑檢查是否有該 log 檔案，如果沒有的話，建立一個

touch /var/log/mysqld.log
chown mysql.mysql /var/log/mysqld.log

為了避免 mysql 沒有權限寫入該檔案，順便把群組跟所有者改成 mysql，重新啟動 mysql 就可以看到 log 了。這邊要注意的是如果一天的流量很高的話，log 成長的速度會非常快速(會記錄每一條執行的SQL)。

設定 MySQL Event Scheduler

很多部落格包括官網提到開啟 event_scheduler 的方法，先利用以下指令檢查是否已開啟

SELECT @@event_scheduler;

如果是 OFF 的話，就用以下指令來開啟

SET GLOBAL event_scheduler := 1;

小蛙開啟了，但是這邊有一個很大的問題，下次去看的時候卻又是 OFF … 後來發現應該是因為重新啟動後，又會變回 OFF，這篇文章提到可以直接在 /etc/mysql/my.cnf 中直接設定。

event_scheduler = ON

一定要放在 [mysqld] 區塊中。小蛙一開始放在最底下，結果完全毫無動靜 ><，停止 Event Scheduler

ALTER EVENT my_event DISABLE;

修改 Scheduler

ALTER
    [DEFINER = { user | CURRENT_USER }]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    [DO event_body]

顯示所有 Events

show events;

顯示詳細 Events 內容

SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='mytable';

刪除 Events

DELETE FROM mysql.event WHERE db = 'mydb' AND name = 'mytable';

參考資料

1. 修改 Event Scheduler @ MySQL官網
   http://dev.mysql.com/doc/refman/5.1/en/alter-event.html
2. 建立 Event Scheduler @ MySQL官網
   http://dev.mysql.com/doc/refman/5.1/en/create-event.html
3. MySQL: 事件排程器 (Event Scheduler) @ Wax Way
   http://waxway.blogspot.tw/2011/12/mysql-event-schedule.html
4. Enabling the MySQL Event Scheduler @ LOGZILLA
   http://forum.logzilla.pro/index.php?topic=71.0
5. mysql event—-语法之CREATE EVENT @ 星期八的博客
   http://blog.sina.com.cn/s/blog_548668f1010007pu.html
6. MySQL日期函數應用 – @ No Money No Honey
   http://blog.infinity.idv.tw/index.php/2009/12/16/mysql%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B8%E6%87%89%E7%94%A8
7. [MySQL] 啟用query log 與 slow query log功能 @ Allen開放源碼研究室
   http://blog.permastyle.com/2010/12/mysql-%E5%95%9F%E7%94%A8query-log-%E8%88%87-slow-query-log%E5%8A%9F%E8%83%BD.html

MySQL / MariaDB / Oracle 相關文章：

• MariaDB JSON_EXTRACT: 從 JSON 欄位取值
• Oracle, MySQL 分組編號與全排序編號 row_number, over, partition by
• MySQL ERROR 2003 (HY000) 無法遠端連線
• JSP 連結 MySQL
• Java 連結 MySQL
• MySQL 排程(Event Scheduler)
• Java 連結遠端 MySQL
• MariaDB Replication 設定
• Java / PHP 插入 emoji 資料到 MariaDB