【Aurora×S3】S3を経由してDBのデータを別DBにコピーする

この記事はGRIPHONE Advent Calendar 2022 19日目の記事です。

Webサービスなどにおいて、「開発環境」と「本番環境」とでデータベースのインスタンスは別のものを利用していることが多いかと思います。例えばAWSを使っている場合、AWSのアカウント自体を分けて使っているよ、という方もいるかもしれません。

弊社ではスマホゲームの開発をしていますが、開発環境と本番環境をネットワーク上でも完全に分離した環境として用意しているプロダクトがほとんどです。

開発環境から本番環境へのデータのコピーがしたい

そうなると、開発環境で作成したデータを本番環境にコピーしたい、なんてこともあるかもしれません。

例えばスマホゲームの場合、ゲーム内お知らせを開発環境で入力・確認をしたけど、同じ状態（記事内容、公開日時など）を本番環境にも適用したい、みたいなことがあるかもしれません。そういったときの1つのアイデアとして、Amazon AuroraとAmazon S3を利用して実現する方法を紹介したいと思います。

自分自身がPHPを使うことが多いので、今回はPHPのソースコードを載せながら、また、PHPのフレームワークで知名度が高いLaravelを利用した書き方で紹介します。この記事ではソースコードがメインで、AWS側のS3やIAMポリシーや、MySQLの設定については省略します。

データの流れ

では、大まかなデータの流れを紹介します。だいたいは上で説明した流れにはなります。

開発環境のMySQLで扱っているデータ（レコード単位）を本番環境にS3を経由してコピーしていき、Webサーバから参照できるようにしようという構成です。前提としてDBにはAmazon Aurora MySQL DBクラスターを利用しています。

手順としては以下の3つに分かれると思います。

開発環境のAurora→S3のデータ保存
アカウント間のS3のデータシンク
本番環境でのS3→Auroraのデータのロード

これらについて説明していきます。

Amazon Aurora MySQL DB クラスターから Amazon S3 バケット内のテキストファイルへのデータの保存

まずは、1つ目の開発環境でのAuroraからS3にデータを保存（アップロード）するところです。ここではデータのエクスポートと呼ぶことにします。エクスポートには以下のAWSのドキュメントを参考にします。

参考：Amazon Aurora MySQL DB クラスターから Amazon S3 バケット内のテキストファイルへのデータの保存 – Amazon Aurora

使用するSELECT INTO OUTFILE S3ステートメントの構文については、ドキュメントでは以下のように書かれています。

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
        [HIGH_PRIORITY]
        [STRAIGHT_JOIN]
        [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
        [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
        [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
        [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
         [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
INTO OUTFILE S3 's3_uri'
[CHARACTER SET charset_name]
    [export_options]
    [MANIFEST {ON | OFF}]
    [OVERWRITE {ON | OFF}]

export_options:
    [FORMAT {CSV|TEXT} [HEADER]]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
]

これに合わせてPHPのソースコードも書いていきたいと思います。

今回の例では、idをprimary keyとして、対象の1レコードをコピーしたいと思います。

/**
 * @param  Model       $eloquent
 * @param  int         $id
 * @param  null|string $connection
 * @param  null|array  $columns
 * @return array
 */
public function intoOutfileS3ById(
    Model $eloquent,
    int $id,
    ?string $connection = null,
    ?array $columns = null,
): array {
    $table = $eloquent->getTable();
    $connection ??= $eloquent->getConnectionName();
    $s3_url = $this->getS3Url($table, $id);
    $columns_string = implode(',', $columns ?? $eloquent->getFillable());
    $sql = <<<SQL
        SELECT
            {$columns_string}
        FROM {$table}
        WHERE id = {$id}
        INTO OUTFILE S3 '{$s3_url}'
            FIELDS TERMINATED BY ','
            LINES TERMINATED BY '\\n'
            MANIFEST ON
            OVERWRITE ON
        SQL;

    return DB::connection($connection)->select($sql);
}

パラメータは最低限必要そうなものを設定してみました。

OVERWRITEをOFFにしておくと、既にS3にエクスポートしている場合にこのクエリを実行すると以下のように既に存在するよというエラーが返ってきます。

ERROR 63985 (HY000): S3 API returned error: S3 file(s) with the given prefix already exists. Please delete them first before running the query, or run the query with 'OVERWRITE ON'.

なので、このエラーをキャッチしてハンドリングするか、OVERWRITEをONにして毎回上書きしてしまうという方法が考えられると思います。上のソースコードの例ではOVERWRITEをONにしてしまっています。

SELECTするときの対象のカラムの指定は*でもよかったのですが、必要なものだけに絞れるようにしてみました。

LaravelのconnectionもEloquentから取得できると思いますが、外から指定できるように引数で渡せるようにしてみました。

開発環境からはこのメソッドを利用することで、データをエクスポートすることができます。

また、上のソースコードで利用しているgetS3Url()は以下のようにしました。

/**
 * @param  string $table
 * @param  int    $id
 * @return string
 */
protected function getS3Url(string $table, int $id): string
{
    return sprintf(
        's3-%s://%s/%s/%s',
        config('aws.region'),
        config('bucket_name'),
        $table,
        $id,
    );
}

今回の記事では1レコードだけを取ってくる前提なので、file-prefixにはidをそのまま入れることにしました。複数レコードを同時にエクスポートしたい場合などはユニークな文字列になるように例えばuuidを生成してfile-prefixに利用する必要がありそうですね。

環境間のS3のデータのシンク

2つ目の開発環境と本番環境のデータのシンクについてです。これはいろいろなやり方があるかなと思います。

例えば、同じAWSアカウントを利用しているのであれば、開発環境と本番環境とでバケットを分けずに同じバケットを利用すればシンプルな構成・設定で実現できそうですね。

もし、異なるAWSアカウントの場合は、クロスアカウントアクセスを許可することで、別のアカウントからでもバケットにアクセスできるようになります。

参考：例 2: バケット所有者がクロスアカウントのバケットのアクセス許可を付与する – Amazon Simple Storage Service

いやいや、開発環境と本番環境とでバケットは分けたいよ、という場合は、レプリケーションを使用することで、開発環境のバケットのデータを本番環境のバケットに同期させることができます。

参考：オブジェクトのレプリケーション – Amazon Simple Storage Service

このように、開発環境でエクスポートしたデータを本番環境からもアクセスできる状態を作っておきます。

Amazon S3 バケットのテキストファイルから Amazon Aurora MySQL DB クラスターへのデータのロード

3つ目の本番環境でのS3からAuroraへのデータのロードについてです。ここではデータのインポートと呼ぶことにします。インポートには以下のAWSのドキュメントを参考にします。

参考：Amazon S3 バケットのテキストファイルから Amazon Aurora MySQL DB クラスターへのデータのロード – Amazon Aurora

使用するLOAD DATA FROM S3ステートメントの構文については、ドキュメントでは以下のように書かれています。

LOAD DATA FROM S3 [FILE | PREFIX | MANIFEST] 'S3-URI'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

これに合わせてPHPのソースコードも書いていきたいと思います。

エクスポート時に1レコード単位で行っていたので、インポートも1レコード単位でできるように実装してみました。

/**
 * @param  Model       $eloquent
 * @param  int         $id
 * @param  null|string $connection
 * @param  null|array  $columns
 * @param  ?array      $set_columns
 * @return bool
 */
public function loadDataFromS3ById(
    Model $eloquent,
    int $id,
    ?string $connection = null,
    ?array $columns = null,
    ?array $set_columns = [],
): bool {
    $set_columns += [
        'created_at' => Carbon::now(),
        'updated_at' => Carbon::now(),
    ];
    $table = $eloquent->getTable();
    $connection ??= $eloquent->getConnectionName();
    $s3_url = $this->getS3Url($table, $id);
    $columns_string = implode(',', $columns ?? $eloquent->getFillable());
    $bindings = collect();

    $sql = <<<SQL
        LOAD DATA FROM S3 MANIFEST '{$s3_url}.manifest'
            REPLACE
            INTO TABLE {$table}
            FIELDS TERMINATED BY ','
            LINES TERMINATED BY '\\n' ({$columns_string})
        SQL;

    if (false === empty($set_columns)) {
        $columns_and_values = collect($set_columns)
            ->each(fn ($value) => $bindings->push((string) $value))
            ->map(fn ($value, $column) => $column . '=?')->implode(',');
        $sql .= <<<SQL

            SET
                $columns_and_values
            SQL;

    }

    return DB::connection($connection)->statement($sql, $bindings->all());
}

created_atやupdated_atはPHP側で入れてあげないといけないので、Carbon::now()で現在時刻を入れるようにしてみました。

何度も同じレコードを読み込んで上書きできるようにREPLACEを指定しています。

また、SETでロード時に上書きで値をセットすることができるようにしています。ゲーム内のお知らせの例でいうと、例えば開発環境でお知らせを公開した状態のデータでエクスポートしてしまっても、インポート時にその公開ステータスを非公開の状態に必ず変更するようにSETを書いてあげれば、誤ってインポートしたお知らせがすぐに公開されてしまうという問題を回避できそうです。

本番環境からこのメソッドを呼ぶことでデータをインポートすることができます。

さいごに

以上のようにして、開発環境と本番環境のような環境間でのデータの同期ができました。

エンジニアから運用をフォローする仕組みづくりのアイデアの一つになればいいなと思っております。