MySQL のレプリケーションから10年間逃げてきた我々が学んだこと8選 - Cybozu Inside Out

こんにちは。クラウド運用チームで SRE をしている飯塚です。

今回は、MySQL のレプリケーション機能を約10年もの間ずっと使ってこなかった私たちが、レプリケーションを使った高可用性構成に移行するための取り組みの中で学んだことについて紹介します。

背景
巨大なテーブルへの primary key の付与
トランザクションサイズが大きい場合には tmpdir に注意
mysqldump で絵文字が消えていないか要チェック
mysqldump が Error 1412: Table definition has changed... で失敗する
mysqldump したデータのリストアが Duplicate entry 'xxx-yyy-PRIMARY-n_diff_pfx01' for key 'PRIMARY' で失敗することがある
mysqldump したデータのリストア時のディスク枯渇には要注意
mysqldump でユーザー情報もコピーした場合は FLUSH PRIVILEGES が必要
unknown error reading log event on the master でレプリケーションが停止したとき
おわりに

背景

当社が提供している cybozu.com では、お客さまのデータ保護の観点から、これまでデータロストのリスク回避の面でメリットが大きい共有ストレージ方式で MySQL の高可用性構成を構築していました。これは、サービス提供開始当時の MySQL は準同期レプリケーションのようなレプリケーションにおけるデータロスト回避のための仕組みが未成熟であると判断していたためです。詳細については以下の資料をご覧ください。

speakerdeck.com

一方で、共有ストレージ方式では VM を収容するホストサーバーの故障時にある程度のダウンタイムが発生することが避けられないといった問題や、ホストサーバーの停止を伴う作業が発生した場合にサービスの停止を伴うメンテナンスが必要になってしまうという問題がありました。このような問題を解決するために、cybozu.com の次世代アーキテクチャではレプリケーションを使った高可用性構成に移行することを計画しています。

この1年間はレプリケーションを使った構成に円滑に移行できるように準備を進めてきましたが、10年もの間レプリケーションを使ってこなかった私たちにとっては、いくつかのインスタンスで実験的にレプリケーションを運用してみるだけでも多数の落とし穴がありました。また、レプリケーション構成での動作に支障が無いように MySQL の使い方を変えていくのも単純な作業ではありませんでした。なぜなら、これまでの設計の中に意図せずレプリケーションに不適切な使い方をしてしまっている箇所がたくさん見つかったためです。

この記事では私たちが対応に苦慮した問題を中心に取り組みから得た学びを紹介します。内訳はスキーマの変更に関するものが1件、バイナリログに関するものが1件、mysqldump の使い方に関するものが5件、レプリケーションの運用中に発生した問題に関するものが1件です。

なお、以降の説明では Oracle MySQL Team での用語変更を踏まえて「マスター」や「プライマリ」と呼ばれていたものを「ソース」、「スレーブ」や「セカンダリ」と呼ばれていたものを「レプリカ」と表記します。

巨大なテーブルへの primary key の付与

行ベースレプリケーションを使用する場合には全てのテーブルに primary key または non-null unique key（ドキュメント上では primary key equivalent と呼称）を付けることが推奨されています。これはパフォーマンス上の問題を回避するためです。この問題を防ぐために MySQL 8.0.13 以降では sql_require_primary_key により primary key の付与を必須とするように設定することもできるようになりました。

これまでレプリケーションを使用してこなかったため、当社が提供しているサービス群には primary key が付与されていないテーブルが大量にあり、これを是正することが最初の取り組みでした。大半のテーブルについては大きな手間なく修正することができたのですが、中には数億行のレコードを含むテーブルがあり、単純な方法では primary key を付与できないものがありました。

既存のテーブルに primary key を付与する最も単純な方法として AUTO_INCREMENT 付きのカラムをサロゲートキーとして追加することが考えられます。しかし AUTO_INCREMENT 付きのカラムの追加は並行して DML の実行ができない、すなわちサービス停止が必要になるという点が問題になります。事前の検証により、月次のメンテナンスウィンドウを超えた許容できないダウンタイムが発生することが分かっていたため、この方法は採用できませんでした。

技術顧問の @yoku0825 さんに相談したところ、UUID() をサロゲートキーとして利用するテクニックを教えていただきました。AUTO_INCREMENT を追加する DDL は DML をブロックしますが、カラムを追加する DDL やカラムを primary key として設定する DDL は DML をブロックしないため、AUTO_INCREMENT に代わるサロゲートキーが用意できれば DML をブロックせずに primary key を追加することができます。MySQL の UUID() で生成される UUID v1 はタイムスタンプの巻き戻りが起きないことなどのいくつかの仮定のもとでは重複が起きないことが保証できるため、私たちの用途ではサロゲートキーとして利用できました。

一般的に、この方法で primary key を追加する手順は以下のようになります。

UUID を追加する nullable なカラムを追加する
テーブルに INSERT するときに UUID のカラムに UUID() を設定するような TRIGGER を作成する（TRIGGER を使わずに、アプリケーションから INSERT するときに UUID() を設定してもよい）
NULL が入っている既存のレコードに小刻みに UUID() を設定していく
NULL が入っているレコードが無くなったら primary key を設定する

3 の既存レコードへの UUID() の設定は少し面倒ですが、私たちが問題としていたテーブルはお客様操作の監査ログを保存するテーブルで、都合のよい性質を持っていました。

ログインなどの履歴などが記録されています。 — cybozu.com のお客様操作の監査ログの例

このテーブルは追記専用で、6週間経過すると CSV ファイルに書き出されて MySQL からは削除されるという仕様になっています。すなわち、新しく INSERT されるレコードに UUID() が設定されるようにさえしておけば、6週間後には NULL が入っているレコードは無くなってしまうのです。

この方法により、私たちは巨大なテーブルへの primary key の付与を無停止で完了することができました。巨大なテーブルに変更を加える場合のテクニックについては以下の記事もご覧ください。

blog.cybozu.io

トランザクションサイズが大きい場合には `tmpdir` に注意

tmpdir は様々な状況で一時ファイルを書き出すのに使われる領域で、MySQL のデータを保存する領域とは別のディスクを指定することで負荷分散することもできるようになっています。私たちの環境ではこれまでデータを保存している共有ストレージではなく、VM を収容するホストサーバーのローカルディスクから切り出された領域を tmpdir に指定していました。各 VM に割り当てられた領域は VM のフェイルオーバーの仕組みの都合上、数十 GiB といった小さい値になっています。

レプリケーションのためのバイナリログの出力を有効にしていなかったこれまでは問題なく動作していたのですが、バイナリログの出力を有効にした途端、いくつかのインスタンスでローカルディスクの容量警告が届くようになってしまいました。

原因はバイナリログの出力を有効にしたことで tmpdir に書き出されるデータの種類が増えてしまったことです。バイナリログを書き出すとき、トランザクションの大きさが binlog_cache_size で指定したサイズよりも大きい場合には tmpdir で指定したディレクトリに一時的にトランザクションを書き出すようになっています。当社が提供しているサービスの中にはかなり巨大なトランザクションを生成するものがあり、その影響で tmpdir に指定したローカルディスクの容量が圧迫されるようになってしまいました。

対策としては

tmpdir を容量が大きな共有ストレージ上に移す
ローカルディスクの容量を増やす
巨大なトランザクションを生成しないようにアプリケーションを改修する

などの方法が考えられます。MySQL の再起動は必要なものの、1 は最も手軽に変更できる方法です。しかし、本来は必要なかった共有ストレージの I/O 負荷が増えてしまうという問題があります。2 や 3 は性能面では望ましい方法と言えますが、それなりの対応コストが必要となります。私たちは苦渋の選択として 1 の方法を選びましたが、将来的に性能面での問題が現れた場合には再考が必要だと考えています。

最近の MySQL にはファイルやディレクトリを指定するオプションが多数存在しています。振り返ってみると、バイナリログの出力を有効にするといった影響の大きい設定変更を加える場合にはファイルの出力先のディスク容量にどのような変化があるかという観点についても事前によく調査するべきであったと考えています。

mysqldump で絵文字が消えていないか要チェック

皆様は utf8mb4 への移行は終わっているでしょうか？ MySQL に UTF-8 の文字列を格納するときの文字コードには utf8 と utf8mb4 の2種類があり、utf8 のカラムに絵文字のような4バイト以上の UTF-8 文字列を格納した場合にはその部分が保存できないという問題が起こります。ほとんどの場合は utf8 と utf8mb4 で迷うことなく utf8mb4 を選択することになると思います。

油断しがちなのですが、テーブルの定義だけではなくコマンドラインツールにも utf8mb4 を設定するべき箇所が多数あります。mysqldump には default-character-set というオプションがありますが、デフォルト値は utf8 であり、絵文字などを正しくコピーすることができません。以下は「私は🍣と🍺が大好きです」という文字列を含むテーブルを mysqldump した例ですが、デフォルト設定では 🍣 と 🍺 が ? になってしまっているのが分かります。

$ mysqldump --compact --default-character-set=utf8mb4 d1
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `c1` varchar(256) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `t1` VALUES ('私は🍣と🍺が大好きです');
$

$ mysqldump --compact d1
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `c1` varchar(256) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `t1` VALUES ('私は?と?が大好きです');
$

この問題はテストデータに意図して絵文字を入れるなどの準備なしでは気づきにくく、知らない間にデータが欠損していたという問題が起こりかねません。レプリケーションの運用経験がなかった我々は、当初この問題に気づいていませんでした。ある程度の量のデータを含むテスト環境のレプリカを作成しようとしたときに、絵文字を含むカラムが unique key に含まれていたことで Duplicate entry が発生して発覚しました。

mysqldump に付けるべきオプションは以下の資料を参照するのが確実です。

MySQL :: MySQL 8.0入門セミナー講演資料 (レプリケーション編)

mysqldump が Error 1412: Table definition has changed... で失敗する

これは mysqldump に --single-transaction を付けた一貫性のある読み取り中に特定の DDL を実行したときに発生する問題です。詳細は以下のドキュメントを参照してください。

MySQL :: MySQL 5.7 Reference Manual :: 14.7.2.3 Consistent Nonlocking Reads

当社のように B2B SaaS の形態でサービスを提供している環境では、テナントごとに MySQL の DATABASE を区切るように設計していることがあります。この設計には様々なメリットがある一方で、契約や解約によって利用環境が作成、削除されるたびに MySQL の DATABASE の作成、削除が行われるなど、日常的に DDL が実行されやすい状態にあるという点はレプリケーションの運用と相性が良くありません。

この問題についてはレプリケーション構成に本格的に移行する前に

アプリケーションではユーザーの操作起因での DDL が実行されないようにする
テナントに契約、解約に伴う DDL の実行タイミングは mysqldump の状態と歩調を合わせる
パーティショニングに関係する操作の実行タイミングを調整する

といった改善を続けていく必要があると考えています。

mysqldump したデータのリストアが Duplicate entry 'xxx-yyy-PRIMARY-n_diff_pfx01' for key 'PRIMARY' で失敗することがある

mysqldump したデータをレプリカ側でリストアしたとき、標記のエラーによりリストアに失敗することがありました。

これは Bug #71814 で報告されていた不具合でした。mysqldump のオプションに --all-databases を付けて全てのテーブルをレプリカにコピーしようとすると、オプティマイザで使用される統計情報が記録されたテーブルである mysql.innodb_index_stats をリストアするときに以下のようなレースコンディションが発生することがあります。

mysql.innodb_index_stats のリストア前にテーブルを初期化する
mysql.innodb_index_stats へのデータの INSERT を開始する
innodb_stats_auto_recalc によって統計情報が再計算される
mysql.innodb_index_stats へのデータの INSERT を完了する

ここで innodb_stats_auto_recalc によって統計情報の再計算が完了したあとに mysql.innodb_index_stats にデータを INSERT しようとすると Duplicate entry により INSERT に失敗します。

対処方法としては mysqldump のオプションに --ignore-table=mysql.innodb_index_stats を付与することで mysql.innodb_index_stats をレプリカにコピーしないようにしました。

この問題は MySQL 8.0.1 以降の mysqldump では修正済みです。

mysqldump したデータのリストア時のディスク枯渇には要注意

ソースの故障時にレプリカが昇格することを見据えて、レプリカの設定でもバイナリログやスロークエリログは有効にしておくことが多いかと思います。しかしながら、mysqldump したデータをレプリカでリストアするときだけは注意が必要です。リストア中に巨大なバイナリログやスロークエリログが生成されてディスク容量を圧迫することがあるためです。mysqldump の前後で設定を変更して、リストア時にはバイナリログやスロークエリログを生成しないように設定することをお勧めします。

バイナリログの設定は MySQL の再起動なしでは適用できないため、別のパラメータも一緒に変更するよい機会です。たとえば innodb_flush_log_at_trx_commit = 0 や skip-innodb_doublewrite は InnoDB のデータ保護機構を無効化する設定であり、通常の運用での利用は推奨されませんが、リストアの間だけ高速化のため利用することは許容できるかもしれません。リストア中の障害でレプリカのデータが破損してしまった場合には、レプリカを最初から作り直せばよいからです。レプリカに MySQL 8.0.21 以降を使用する場合には ALTER INSTANCE DISABLE INNODB REDO_LOG を使うことも検討できます。

mysqldump でユーザー情報もコピーした場合は `FLUSH PRIVILEGES` が必要

手動で実行することはほぼ無いと思いますが、CREATE USER などを使わずに mysql.user などのテーブルを書き換えることによってユーザー情報を書き換えた場合には FLUSH PRIVILEGES を実行して変更を MySQL に伝える必要があります。

気を付ける必要があるのは mysqldump のオプションに --all-databases を付けて mysql.user などのテーブルをコピーした場合です。この場合は mysql.user などのテーブルの書き換えが発生するため FLUSH PRIVILEGES が必要な条件に合致します。これを忘れるとレプリカ側で不可解なエラーが多数発生します。

前述のようにリストアの前後で MySQL の設定を変更するために再起動を行っている場合には、再起動後に mysql.user などのテーブルからユーザー情報が読み込まれるため FLUSH PRIVILEGES を実行する必要はありません。

unknown error reading log event on the master でレプリケーションが停止したとき

実験的に本番環境のいくつかの MySQL インスタンスでレプリケーションを開始して数か月経ったある日、あるレプリカでレプリケーションが突然停止しました。

IO スレッドのエラーには以下のようなメッセージが記録されていました。

Got fatal error 1236 from master when reading data from binary log: 'unknown error reading log event on the master; the first event 'mysql-bin.001471' at 216402234, the last event read from './mysql-bin.002208' at 2049, the last byte read from './mysql-bin.002208' at 2049.'

このエラーに関しては下記の資料が参考になりました。

speakerdeck.com

資料に記載の再現条件を踏まえて同様の事象であると判断しました。判断に使った基準は以下になります。