MySQL Shellを用いたローカル環境DBへの高速なデータ投入

technology


estieの上久保です。昨年4月に入社してから不動産売買領域のプロダクト開発を主に担当しています。

背景

不動産ドメインの都合上、建物や登記簿といった多種多様で複雑なデータを扱う必要があることからプロダクトのテーブル構造は頻繁にアップデートが発生します。エンジニアはテーブル定義を最新の状態に更新した後に、手元の環境で立ち上げた開発用データベースへのmigrationの適用とseedデータ投入を行うことで、チーム全員が同じ状態のデータベースを見て開発を進めることができます。

私たちのチームのプロダクトでは当初mysqldumpを用いてstaging環境のデータを手元の開発環境に投入していたのですが、テーブル数やレコード数の増加に合わせてデータ投入にかかる時間も長くなり、DB内全テーブルのデータの投入に約1時間ほどかかるようになってしまいました。この問題は単に開発の生産性を下げるだけでなく、時間を惜しんで手元のデータベースの更新を怠った結果、避けられたはずのビルドのエラーやエンジニア間の認識齟齬につながるケースも発生してしまいました。

MySQL Shell

データ投入にかかる時間を短縮するためにMySQL Shellを採用することにしました。MySQL Shellは高機能なMySQLクライアントであり、SQL以外にもPythonやJavaScriptで書かれたコードでAPIを実行できます。

MySQL Shellの優れた特徴の1つとしてテーブルのインポートがマルチスレッドで実行されるために高速化が見込めます。またmysqldumpではデータ量の関係、通信環境が良くない場合に極端に処理が遅くなったり、途中で処理が止まってしまうことも発生していましたが、MySQL Shellではダンプしたデータファイルの圧縮をデフォルトで行うため、ローカルへのデータ転送速度という面でも有利です。

実装

ここからは実装内容の解説に入ります。

具体的にはDBデータをダンプする機能とローカルのDBに反映する機能の2つに分かれています。

ダンプ処理のユースケースとしてはDBのスキーマが変わった時にダンプデータも更新するといった場合が考えられますが、これはローカルDBの反映に比べて頻繁に実行する必要がないため実装を分けてそれぞれ独立して実行できるようにしています。

DBのダンプ

GitHub Actionsのワークフロー内でDockerfile（後述）のイメージをECRにビルドし、そのイメージを元にECSのタスクとしてコンテナを作成します。ECSの操作にはecspressoを使用しており、ecspresso runコマンドを実行することでタスクを単体のバッチ処理として実行できます。

Dockerfile: コンテナのイメージ作成

ベースイメージとなるbookworm-slimには最小限のパッケージしか含まれていないため、wgetやzipなどを追加でインストールしています。lsb-release, gnupgはMySQL Shellのインストール時に求められたため追加しました。

LinuxにMySQL Shellをインストールする方法には癖があり、事前にMySQL APT レポジトリを追加する必要があります。

そのためにAPTレポジトリ設定用のdebパッケージをダウンロードしてから実行するのですが、通常だとインタラクティブな設定画面が表示され、案内に従ってパラメータを入力して進める流れになります。

コンテナのビルド時に自動でこの作業を行うために設定を追加しています。まず環境変数DEBIAN_FRONTENDをnoninteractiveに設定し、非インタラクティブモードにします。次にdebconf-set-selectionsコマンドを用いて本来ユーザ入力が必要なパラメータを設定しています。

以上で準備は整い、apt installコマンドでMySQL Shellをインストールすることができます。

FROM debian:bookworm-slim

WORKDIR /app

# 非インタラクティブ（ユーザーの入力待ちをしなくなる）モードに設定
ENV DEBIAN_FRONTEND noninteractive

# 各種パッケージの追加
RUN apt update -y && apt install -y wget zip awscli lsb-release gnupg

# MySQL APT レポジトリ設定用のdebパッケージをダウンロード
RUN wget https://dev.mysql.com/get/mysql-apt-config_0.8.33-1_all.deb

# debconf-set-selectionsコマンドでインストール画面で入力するパラメータを設定
RUN echo 'mysql-apt-config mysql-apt-config/select-product select  Ok' | debconf-set-selections

# APT レポジトリの追加
RUN dpkg -i mysql-apt-config_0.8.33-1_all.deb

# MySQL Shellのインストール
RUN apt update -y && apt install -y mysql-shell

COPY dump.sh .

dump.sh: DBダンプの実行およびS3へ移送

タスク開始時にこのスクリプトが実行されます。MySQL Shellのコマンドでスキーマ内に存在する全テーブルデータのダンプ、ファイルの圧縮、S3へのコピーを順番に行なっています。

環境変数はecspressoのタスク定義側で設定してあげることでコンテナ内で利用できます。

注意すべき点として、MySQLのユーザにはスキーマに対してSELECTやLOCK TABLEなどの権限が必要になるので事前に設定しておきましょう。

#!/bin/bash -euo pipefail
DUMP_FILE_NAME=dump-data
S3_BUCKET_NAME=<S3_BUCKET_NAME>

echo "util.dumpSchemas([\"${DB_NAME}\"], \"${DUMP_FILE_NAME}\", {events: false, routines: false, triggers: false, dataOnly: true})" \
| mysqlsh --js ${DB_USER}@${DB_HOST}/${DB_NAME} -p${DB_PASSWORD}

zip -r "${DUMP_FILE_NAME}.zip" "${DUMP_FILE_NAME}"
aws s3 cp ${DUMP_FILE_NAME}.zip s3://${S3_BUCKET_NAME}/

ローカル環境DBへのデータ投入

ダンプしたDBのデータをS3からダウンロードし、ローカルのDBに反映しています。makefile等にコマンドを用意しておくと実行時に便利です。MySQL Shellを用いたデータのアップロード時にはloadDumpコマンドを使いますが、この時LOCAL_INFILEという環境変数の値をONに設定する必要があります。私はローカルDB用のコンテナの設定ファイル内で指定しました。

#!/bin/bash -e
DUMP_DIR=$(realpath $(dirname $0))/dumps
DUMP_FILE_PATH=${DUMP_DIR}/dump-data
DUMP_FILE_NAME=${DUMP_DIR}/dump-data.zip

source $(realpath $(dirname $0))/fetch.sh

# 1. ローカルにダンプしたデータが存在しない場合はstaging環境から取得する
if [ ! -d $DUMP_FILE_PATH ]; then
    echo "INFO: ${DUMP_FILE_PATH}が存在しません。S3から取得します"
    aws s3 sync <S3_BUCKET_PATH> $DUMP_DIR
    
    # ダウンロードしたファイルの解凍
    unzip $DUMP_FILE_NAME -d $DUMP_DIR
fi

# 2. ローカルDBにMySQL Shellでログインしてダンプしたデータを反映する
echo \
"util.loadDump('${DUMP_FILE_PATH}', {
  resetProgress: true,
  ignoreExistingObjects: true,
  deferTableIndexes: 'all',
  loadData: true,
});" \
| mysqlsh --js <DB_USER>@<DB_HOST>:<DB_PORT>/<DB_NAME> -p<DB_PASSWORD>

実行時間の計測結果がこちらです。ダンプしたデータの更新が不要な場合、ローカルDBへのデータ投入処理自体は1分ほどで終了し、mysqldumpを用いて最長1時間以上かかっていた頃から約60倍の高速化を達成しました。

実行時間
DBのダンプ (MySQL Shell)3 min 8 sec
ローカルDBへの投入 (MySQL Shell)1 min 3 sec

最後に

プロダクトの進化に合わせて開発のボトルネックは常に変化します。機能開発を進める中で発覚した開発者の生産性を低下させる問題に対して解決策を提案し、それをチーム内外のメンバーに共有することで組織全体に利益を還元する文化がestieでは深く根付いています。そんなestieの開発組織で一緒に働くエンジニアを募集しているので興味がある方は是非ご応募お待ちしています！

hrmos.co

© 2019- estie, inc.