[MySQL] Event Schedulerで定期的にデータ削除してみる

前置き

最近、サーバのログ情報を集中管理したくて、syslogからMySQLに直接送信するように設定しました。
数台のサーバから大量のデータがMySQLに飛ばされてくるわけですが、それを放置しておくと非力なサーバのディスクが枯渇してしまいます。そこで、MySQLの機能の１つであるEvent Schedulerを使用して定期的にデータを削除する方法を採用しました。
その他の選択肢としてはcronを用いる方法がありましたが、MySQLでそれができるならMySQL内で完結させたほうが管理がしやすいだろうと考えました。

それでは、どのようにイベントスケジューラを設定すればいいかを書いていきます！

環境

• Ubuntu 18.04 LTS
• MySQL 5.7

今回、例で使用するDBやユーザの権限などは以下の通りです。

DB	Table	User	Host	Privilege
syslog	example	rsyslog	localhost	all

Event Schedulerとは？

定期的に実行したいコマンド(イベント)を管理するオブジェクトです。
似たような機能にUNIX系のcronやMySQLのTriggerという機能があります。
Triggerと違う点は、TriggerはINSERT、DROPやUPDATEなどの引き金となるコマンド(イベント)によって実行されるものですが、Event Schedulerは時間を引き金としています。
Event Schedulerを使用することによって、定期的なイベントを自動的に実行することができます。
決まったスパンで実行するものがあるなら、この機能を使用しない手はありません。

煩雑な手動での実行と、おさらばしましょう！

設定

Event Schedulerの起動

ますは、スケジューラを起動させましょう。
スケジューラを起動させるにはevent_schedulerの変数にONを格納します。

SET GLOBAL event_scheduler = ON;

SHOW variables like 'event%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+

ValueがONになっていればOKです。

フォーマット

基本的なフォーマットは以下のようになります。

CREATE EVENT event_name ON SCHEDULE (AT timestamp | EVERY interval) DO command;

変数	説明
event_name	作成したいイベントの名前
timestamp	実行する日時
interval	実行する間隔
command	実行する処理

実行するタイミングにはAT timestampかEvery intervalのどちらかを指定します。
intervalで使用できるキーワードにはYEAR、MONTH、DAYなどがあります。
詳しくはMySQL :: MySQL 5.7 Reference Manual :: 13.1.12 CREATE EVENT Syntaxを参照してください。

ここで注意すべきケースは、実行したいコマンドが複数ある場合です。
区切り文字に;(セミコロン)を使用すると、そこでイベントの作成が終了するので、作成する前に区切り文字を変更しましょう。
変更するためのコマンドはdelimiter your_new_delimiterです。
また、実行したいコマンドが複数の場合はフォーマットが若干変わります。

DO
  BEGIN
    command1;
    command2;
    ....
  END your_new_delimiter

このようにDOのあとにBEGINとENDでコマンドを挟みます。

イベントの作成

それでは、イベントを作成してみましょう。
作成するイベントは「syslogのexampleテーブルのレコードを一日毎に削除する」というものです。

CREATE EVENT truncate_example1 ON SCHEDULE EVERY 1 DAY COMMENT 'Remove all record on example table' DO TRUNCATE TABLE syslog.example;

SELECT * FROM information_schema.EVENTS\G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: syslog
          EVENT_NAME: truncate_example1
             DEFINER: rsyslog@localhost
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: TRUNCATE TABLE syslog.example
          EVENT_TYPE: RECURRING
          EXECUTE_AT: NULL
      INTERVAL_VALUE: 1
      INTERVAL_FIELD: DAY
            SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
              STARTS: 2018-11-05 13:04:17
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: NOT PRESERVE
             CREATED: 2018-11-05 13:04:17
        LAST_ALTERED: 2018-11-05 13:04:17
       LAST_EXECUTED: 2018-11-05 13:04:17
       EVENT_COMMENT: Remove all record on example table
          ORIGINATOR: 0
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_general_ci
  DATABASE_COLLATION: utf8mb4_bin

開始日時の指定

先に作成したイベントは作成した直後に実行され、exampleテーブルの中身が削除されてしまいます。
なので、開始日時を指定して直後に実行されないようにします。
開始日時を指定する基本フォーマットは以下のようになります。

ON SCHEDULE ...
STARTS timestamp

例では現在から一週間後に開始されるように設定するので、STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEKを追加します。

CREATE EVENT truncate_example2 ON SCHEDULE EVERY 1 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK COMMENT 'Truncate example table every one week' DO TRUNCATE TABLE example;

SELECT * FROM information_schema.EVENTS\G
*************************** 2. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: syslog
          EVENT_NAME: truncate_example2
             DEFINER: rsyslog@localhost
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: truncate table example
          EVENT_TYPE: RECURRING
          EXECUTE_AT: NULL
      INTERVAL_VALUE: 1
      INTERVAL_FIELD: WEEK
            SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
              STARTS: 2018-11-12 13:39:23
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: NOT PRESERVE
             CREATED: 2018-11-05 13:39:23
        LAST_ALTERED: 2018-11-05 13:39:23
       LAST_EXECUTED: NULL
　　　　　　　　　（省略）

このようにスケジューラの開始日時を指定するとLAST_EXECUTED: NULLとなり、直後に実行されるのを防げることがわかります。
また、開始日時と同様に終了日時も指定できます。フォーマットは同じでSTARTSの代わりにENDSを使用します。

最後に

ここで紹介した方法を使用すれば、定期的なMySQLのバックアップなどにも応用できます。
冗長な作業はどんどん自動化させましょう！

参考

• MySQL :: MySQL 5.7 Reference Manual :: 13.1.12 CREATE EVENT Syntax
• MySQL :: MySQL 5.7 Reference Manual :: 13.2.2 DELETE Syntax
• MySQL :: MySQL 5.7 Reference Manual :: 13.1.34 TRUNCATE TABLE Syntax