[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](https://dev.mysql.com/doc/refman/5.7/en/create-event.html)を参照してください。

ここで注意すべきケースは、実行したいコマンドが複数ある場合です。
区切り文字に***;***(セミコロン)を使用すると、そこでイベントの作成が終了するので、作成する前に区切り文字を変更しましょう。
変更するためのコマンドは`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](https://dev.mysql.com/doc/refman/5.7/en/create-event.html)
- [MySQL :: MySQL 5.7 Reference Manual :: 13.2.2 DELETE Syntax](https://dev.mysql.com/doc/refman/5.7/en/delete.html)
- [MySQL :: MySQL 5.7 Reference Manual :: 13.1.34 TRUNCATE TABLE Syntax](https://dev.mysql.com/doc/refman/5.7/en/truncate-table.html)