Yappli Tech Blog

株式会社ヤプリの開発メンバーによるブログです。最新の技術情報からチーム・働き方に関するテーマまで、日々の熱い想いを持って発信していきます。

SQLite3で管理していたデータの一部をMySQLに移行した

サーバーサイドエンジニアの田実です!

YappliのサービスではコンテンツデータをSQLite3で管理しています。SQLite3を使っている理由や課題の詳細は以下の記事に書いています。

tech.yappli.io

コンテンツデータ以外にもアプリやアカウントのデータもコンテンツデータとは別のSQLite3ファイルで管理していました。上記のブログにも記載の通り、SQLite3を使ったデータ管理は閲覧性やマイグレーション、冗長性など多くの面で課題がありました。そこで、昨年このアプリ・アカウントのデータをSQLite3からMySQLに移行し、これらの課題を解消しました。SQLite3ファイルの管理は開発環境構築や本番運用で非常に複雑だったため、結果としてこれらの認知負荷も下げることができました。

今回は、このDB移行をどうやって完遂したのかを紹介したいと思います!

おおまかな流れ

以下のようなフローで移行を進めました。

  1. 影響範囲の洗い出し
  2. MySQLとSQLite3の両方に2重書きするようにコードを修正
  3. SQLite3からMySQLにデータを移行
  4. 読み取り先をSQLite3からMySQLに変更
  5. SQLite3に書き込む処理を停止し、MySQLのみ書き込むように修正

それぞれの工程で具体的に行ったことを説明していきます!

影響範囲の洗い出し

SQLite3はファイルベースのRDBで、ファイル共有のプロトコルを使わない限りは別のホストからアクセスできないため、SQLite3ファイルがあるホストからのみアクセスされます。つまり、そのホストで稼働しているアプリケーション・リポジトリが影響範囲になります。Yappliでは主に PHP + 独自フレームワーク PHP + Laravel Go + gRPC で書かれたアプリケーションがあり、言語やフレームワーク、設計、バッチの実行方法などそれぞれ異なった形で運用されています。これらのコードに対して影響範囲を1つずつ調査していきました。

アカウント情報を含むのは common.db という共通DBで、アプリ情報を含むのは {account_id}.db というアカウントID+拡張子の形式のファイル名となっています。Yappliでは多言語のアプリや無料・有料アプリなど1つのアカウントで複数のアプリを作成でき、すべてのアプリはアカウントに紐づいています。 {account_id}.db ではそのアカウントIDのアカウントが管理しているアプリ情報が含まれています。

共通DBの利用箇所は common.db という名前を検索してそれを利用する関数・クラスを辿っていくことで抽出することができました。{account_id}.db の方は、Goで書かれたアプリケーションのDBアクセスがRepositoryの構造体経由で行っているので、 {account_id}.db にアクセスしている構造体の関数が影響範囲になります。具体的には以下のような感じでSQLite3にアクセスしていました。

r := AppRepository()
r.FindByID("xxx")

PHP + Laravel の方は利用箇所がそもそも少なかったため愚直にそれっぽいコードをgrepして確認していきました。 PHP + 独自フレームワーク が少し大変で、こちらはDBアクセスがクラスや関数などで共通化されておらず PDO を直接使うコードとなっていました。イメージ的には以下のような感じです。

<?php

$db = "/path/to/dir/{$account_id}.db"
$pdo = new PDO("sqlite:$db");

そのため、こちらは .db という文字列でコードを検索して1つずつ確認していく方法を取りました。ただし、.db という拡張子はコンテンツデータを含む全てのSQLite3ファイルの拡張子になっていたので、 /path/to/dir の部分の文字列も検索対象にするなど実際には色々と工夫して抽出していました。

MySQLとSQLite3の両方に2重書きするようにコードを修正

影響範囲を確認した後は、SQLite3に書き込んでいる処理をMySQLにも書き込むようにアプリケーションコードを書き換えていきます。 前述の通り、GoのアプリケーションからSQLite3にアクセスする際はRepository構造体の関数経由になるのでそこにMySQLへの書き込み処理を追加しました。

type AccountRepository struct {
  accountSQLite3Client AccountSQLite3Client
  accountMySQLClient AccountMySQLClient
}

func (r *AccountRepository) Insert(account *Account) error {
  err := r.accountSQLite3Client.Insert(account)
  if err != nil {
    return err
  }
  err = r.accountMySQLClient.Insert(account)
  if err != nil {
    // 最初はエラーロギングするだけで後続処理は実行
    // しばらく動かしてみて問題なければエラーを返すように修正
  }
  return nil
}

PHPの方は以下のようにPDOを直接呼ぶような処理になっており、DBアクセスが共通化されていませんでした。

<?php

$pdo = new PDO('sqlite:/path/to/common.db');
$stmt = $pdo->prepare('SELECT id, name FROM account WHERE id = ?');
$stmt->execute(['xxx']);

このコードをメソッド・関数でラップしてそれ経由でクエリを発行するようなリファクタリングをしても良かったのですが、影響箇所が大きかったためPDOを拡張したクラスを定義して、そのクラスの中でMySQLとSQLite3に2重で書き込みするようなコードを入れて対応しました。具体的には以下のように実装して PDO を直接呼んでいる箇所を DoubleWritePDO に書き換えました。

<?php

class DoubleWritePDO
{
  public PDO $sqlite;
  public PDO $mysql;

  public function __construct($sqlite_dsn)
  {
    $this->sqlite = new PDO($sqlite_dsn);
    $this->mysql = new PDO($mysql_dsn); // ここのDSNはMySQLなので共通でENVから読み取るなどしている
  }

  public function prepare($query)
  {
    $sqlite_stmt = $this->sqlite->prepare($query);
    
    // 読み込みはSQLite3から行う
    if ($this->isSelect($query)) {
      return $sqlite_stmt;
    }
    // 書き込みはMySQLから行う
    $mysql_query = $this->convertToMySQLQuery($query);
    $mysql_stmt = $this->mysql->prepare(mysql_query);
    
    return new DoubleWriteStatement($mysql_stmt, $sqlite_stmt);
  }
}

class DoubleWriteStatement
{
  public Statement $mysql_stmt;
  public Statement $sqlite_stmt;

  public function __construct($mysql_stmt, $sqlite_stmt)
  {
    $this->mysql_stmt = $mysql_stmt;
    $this->sqlite_stmt = $sqlite_stmt;
  }

  public function execute($params)
  {
    $this->mysql->execute($params);
    $this->sqlite3->execute($params);
  }

  // ... fetchAll(), fetch() なども実装
}

prepare() ではMySQL用のSQL文を生成するために convertToMySQLQuery() のメソッドでSQLite3のSQL文から書き換える処理をしています。これによってSQLite3とMySQLのSQL文の違いを吸収しています。実装的には greenlion/php-sql-parser を利用してSQLをパース&ASTからSQLを生成しており、MySQL化によって必要になったWHERE句の追加も行っています。

<?php

$parser = new \PHPSQLParser\PHPSQLParser();
$parsedStatement = $parser->parse($statement);
$parsedStatement["WHERE"][] = ['expr_type' => 'colref', 'base_expr' => 'account_id', 'sub_tree' => null];
$parsedStatement["WHERE"][] = ['expr_type' => 'operator', 'base_expr' => '=', 'sub_tree' => null];
$parsedStatement["WHERE"][] = ['expr_type' => 'colref', 'base_expr' => "\"xxx\"", 'sub_tree' => null];

$creator = new \PHPSQLParser\PHPSQLCreator();
$mysqlStatement = $creator->create($parsedStatement);

アプリ情報に関してはプレビュー用と本番用のデータを切り替える必要があります。これを実現するため、 preview_apps production_apps という2つのテーブルを用意し、プレビューから本番に公開するときに preview_apps のデータを production_apps にUPSERTしています。MySQLのマルチバージョン対応は以下の記事でも紹介していますが、概ね同じ方法で実現しています。

tech.yappli.io

SQLite3からMySQLにデータを移行

SQLite3からMySQLにデータを移行するスクリプトを作成して移行しました。common.db の場合はカラムの型に注意するだけで良いのですが、 {account_id}.db の場合はグループごとにファイルが作成されているので、account_idに相当する情報をカラムに追加する必要があります。

-- 移行前(SQLite3)
CREATE TABLE app (
  name TEXT,
  code TEXT,
  created TEXT,
  updated TEXT
);

-- 移行後(MySQL)
CREATE TABLE app (
  name VARCHAR(255) NOT NULL,
  code VARCHAR(255) NOT NULL,
  account_id VARCHAR(255) NOT NULL, -- このカラムを追加
  created timestamp NOT NULL,
  updated timestamp NOT NULL 
);

読み取り先をSQLite3からMySQLに変更

2重書きやデータ移行の結果、SQLite3とMySQLのデータが同じになっているかどうかをスクリプトを動かして確認しました。 問題があれば移行漏れやロジックの考慮漏れを確認・修正し、データが同じになった段階でMySQLの方を読み込むように修正しました。

MySQLの読み込みは基本的にはDBの接続先を切り替えるだけなのですが、マイクロサービス側でもアカウント情報を参照している箇所があったのでその対応が必要でした。以下のように、APIサーバーだけではなくSQLite3アクセス用APIからもMySQLへのアクセスがあった感じです。

変更前のシーケンス図

SQLite3 アカウント情報 の列をそのままMySQLに置き換えるとマイクロサービス側がMySQLに依存をすることになります。インフラの管理性やアクセス管理の面でMySQLへの依存を減らしていきたいのと、このマイクロサービス自体をゆくゆく廃止していきたいのもあり、マイクロサービスからMySQLにアクセスするのではなく、予めAPIサーバー側でMySQLからデータを抽出し、マイクロサービスに渡すように変更しました。

変更後のシーケンス図

マイクロサービスへの接続にはgRPCを使っているので、以下のようにcontextにmetadataを入れてマイクロサービスにアカウントIDを渡しています。

md := metadata.New(map[string]string{
    "app_id":     appID,
    "account_id": accountID,
})
rctx := metadata.NewOutgoingContext(ctx, md)

ちゃんとアカウントIDのデータがパラメータとして渡ってきているか、SQLite3へのアクセスを外しても問題なく動きそうかなどの確認は、マイクロサービスの本番環境にログを入れて監視することで考慮漏れに気づけるようにしました。ログは以下のような感じで実装していました。

// メタデータにaccountIDが渡ってきていない場合は変更漏れなのでロギング
accountID, ok := md.GetAccountID()
if ok {
    if len(accountID) == 0 {
        log.Infof(ctx, "empty account id: method=%s", info.FullMethod)
    }
} else {
    log.Infof(ctx, "no set account id: method=%s", info.FullMethod)
}

// 元々行っていたMySQLからアカウントIDを取得する処理
oldAccountID, err := getRepository().GetAccountID(ctx)
if err != nil {
    return err
}
// 古い取得処理と新しい取得処理が一致しない場合は変更漏れなのでロギング
if len(accountID) > 0 {
    if accountID != oldAccountID {
        log.Infof(ctx, "oldID(%s) != newID(%s)", oldAccountID, accountID)
    }
}

SQLite3に書き込む処理を停止し、MySQLのみ書き込むように修正

本番反映して問題なく動くことを確認した後、二重書きを停止してMySQLのみ書き込むように修正しました。 SQLite3に接続するRepositoryクラスや関連するクラスも一括で削除しました。

まとめ

SQLite3で管理していたアプリ・アカウント情報をMySQLに移行した話を紹介しました。サラッと書いているのですが、実は完了まで2〜3年ほどかかった一大タスクだったりします…w

実際はこの手順を1順して終わりという感じではなく、考慮漏れに気づいて前の工程を行き来することがほとんどでした。 そのため、考慮漏れで不具合が発生していたとしても安全に迅速に切り戻せるようにタスクを分割して進めていました。データの変更が伴わない変更はリバートするだけで良く、データの変更が伴うものはバックアップを取っておくなど、不具合のパターンをある程度想定して準備していました。また、影響範囲が大きいため、正常系の動作保証ができるようAPIレベルのE2Eテストも拡充しました。実際に変更漏れ・考慮漏れを検知できており、改めて自動テストの効果を実感しました。リリース後の不具合に気付けるようにログやアラートを整備しておくことも重要です。各工程のリリース後はアラートやメトリクスの傾向変化に注意しておくことで不具合に迅速に対応でき、不具合の影響を最小限に抑えることができます。また、メンタル的にリリースは結構不安になるものなので、APMやリクエストログ、アプリケーションログなど計測可能なエビデンスを用意しておくと心理的安心を得ることができます…w

ヤプリではお客様により高い価値を提供できるよう、ワークアラウンドな対応だけで終わらせず、このようなデータレベルの抜本的な改善も多く行っています。 本記事を読んで、ヤプリのデータ管理や基盤改善に興味を持った方がいましたら、ぜひカジュアル面談でお話しましょう!

open.talentio.com