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

1 則留言

發佈留言

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