スイッチ交換で データベースが すごく苦労した話 株式会社アイスタイル R&D部インフラグループ 鈴木利房 

自己紹介 • 鈴木利房 • 株式会社アイスタイル • テクノロジー本部R&D部インフラG所属 • データベースエンジニア 

お話しすること • スイッチ交換でMySQLのグループレプリケーションが壊れまし た • ことの顛末を共有したいと思います 

ある日こんな依頼がきました • L3スイッチ交換します • 迂回路用意するので、データベースをそっちに移動できせん か？ • サービスは止められないので無停止でお願いします • グループレプリケーションってこういう時も使えますよね 

What’s Group Replication? • MySQL5.7から提供された、 マルチマスター構成可能なレ プリケーション方式 • Active-Active型HAが構成可能 • 3台以上の奇数台での構築が推 奨される M2 M1 M3 同期 クライアント 

起こったこと 

やりたかったこと M2 M1 M3 L3SW 通常経路 代替SW 迂回路 同期中 

やりたかったこと M2 M1 M3 L3SW 通常経路 代替SW 迂回路 これだけ 同期停止 こっちは 同期継続 迂回路に 移動 

やりたかったこと M2 M1 M3 L3SW 通常経路 代替SW 迂回路 元通り！ 

実際に起きたこと M2 M1 M3 L3SW 通常経路 代替SW 迂回路 同期中 

実際に起きたこと M2 M1 M3 L3SW 通常経路 代替SW 迂回路 こっちは 同期停止 ポートが足りず 1台だけ移動 

実際に起きたこと M2 M1 M3 L3SW 通常経路 代替SW 迂回路 同期再開 できず！ 

劇的ビフォーアフター ビフォー M2 M1 M3 L3SW 手厚く保護されて いた本番DBが 

劇的ビフォーアフター ビフォー アフター M2 M1 M3 L3SW M2 M1 M3 L3SW 手厚く保護されて いた本番DBが まさかの無保護状 態に！ 

なぜ、こうなってしまったか 

同期再開できなくなった原因 • 本来更新されないはずだった同期停止側のDBが更新されてし まい、データに差異が生まれてしまった MySQL :: MySQL 5.6 リファレンスマニュアル :: 17.1.3.1 GTID の概念 https://dev.mysql.com/doc/refman/5.6/ja/replication-gtids-concepts.html グローバルトランザクション識別子 (GTID) は、発生元のサーバー (マスター) で作成され、そこでコ ミットされた各トランザクションに関連付けられる一意識別子です。 

同期再開失敗時のつれないメッセージ > start group_replication; ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log. サーバーは、グループのアクティブメンバーになるように正しく構成されて いません。エラーログの詳細をご覧ください。 

再同期失敗時のせつないエラーログ 2019-02-27T04:45:35.744126+09:00 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 1b7ca97b-6164-11e8-8035-246e96b82c98:1- 220905070:221248887-221249753,f2349267-60a7-11e8-b6ba-246e96b6ee40:1- 2 > Group transactions: 1b7ca97b-6164-11e8-8035-246e96b82c98:1- 220905070:221248887-221254343' 2019-02-27T04:45:35.744183+09:00 0 [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.' このメンバーは、グループ内に存在するトランザクションよりも多くの実行済みトランザ クションを持っています。 メンバーに、グループに存在しないトランザクションが含まれています。 メンバーはグループから脱退します。 

問題発生の原因になったDB設定 • グループレプリケーションには2種類の動作モードがある • 1台だけが更新可能なシングルプライマリモード • 全台更新可能なマルチマスターモード • 障害発生時に確実に更新を継続させたかったため、マルチマス ターモードで運用していた MySQL :: MySQL 5.7 Reference Manual :: 17.4.1 Deploying in Multi-Primary or Single-Primary Mode https://dev.mysql.com/doc/refman/5.7/en/group-replication-deploying-in-multi-primary- or-single-primary-mode.html 

教訓 • グループレプリケーションはシングルプライマリモードの方が 安全 

作業手順に問題は無かったのか • ネットワーク分断が起きることが事前に分かっていたため、分 断前に手動で同期を停止していた • 同期停止コマンドを実行すると、停止した方はリードオンリー に切り替わり保護されることを検証で確認していた • なのに、実際にはデータが更新され、再同期できなくなってし まった 

問題発生の原因になった作業手順 1. 待機系でプロセスリストを表示 2. 待機系でクエリログを出力し更新が無い事を確認 3. 同期停止コマンド実行 4. リードオンリーに切り替わること、同期グループから外れる 事を確認 

問題発生の原因になった作業手順 1. 待機系でプロセスリストを表示 2. 待機系でクエリログを出力し更新が無い事を確認 3. 同期停止コマンド実行 4. リードオンリーに切り替わること、同期グループから外れる 事を確認 1と2に問題があった 

待機系への接続を発見していた • プロセスリストでは、運用ルール上は存在しないはずの待機系 へのクライアント接続があった • 告知済みだし更新はしていないだろうと判断し、そのまま放置 して作業を続行してしまった 

クエリログで多数のcommitが出ていた • クエリログでは多数のcommitが実行されていたが、グループ レプリケーションでのcommitであろうと判断し、そのまま作 業を続行した • 待機系へのクライアント接続で実行されていたcommitがあっ たとしても、判別ができない状態だった 

教訓 • マルチマスターモードで同期停止を行うときは、確実にクライ アントからの接続が無い状態を作り出すべき 

復旧への道のり 

この時点から、どのように復旧したか 2019-02-27T04:45:35.744126+09:00 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 1b7ca97b-6164-11e8-8035-246e96b82c98:1- 220905070:221248887-221249753,f2349267-60a7-11e8-b6ba-246e96b6ee40:1- 2 > Group transactions: 1b7ca97b-6164-11e8-8035-246e96b82c98:1- 220905070:221248887-221254343' 2019-02-27T04:45:35.744183+09:00 0 [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.' 

データベース構成 M M M LB S S S グループレプリケーション マスタースレーブレプリケーション 

復旧への軌跡 1. 強制的に再同期を行う 2. グループレプリケーションを解体する 3. 生き残った更新系DBのダンプ＆リストア＆再同期 4. 生き残った更新系DBの全DBダンプ＆リストア＆再同期の再実 施 5. 全データベース、物理コピーを実施 

復旧への軌跡 1. 強制的に再同期を行う →コミット遅延が発生 2. グループレプリケーションを解体する →コミット遅延解消 3. 生き残った更新系DBのダンプ＆リストア＆再同期 → マスタースレーブレプリケーションが開始できない問題発生 4. 生き残った更新系DBの全DBダンプ＆リストア＆再同期の再実施 →マスタースレーブレプリケーションが開始できない問題解消せず 5. 全データベース、物理コピーを実施 → やっと復旧完了！ 

復旧処理を難しくした原因 • 生き残っているマスターが1台しかない • 当初の予定通り迂回路に2台移動していたら復旧は難しくな かった • 1台しかないと止めることができない 

教訓 • グループレプリケーションの構成台数は2台以下にしてはいけ ない 

復旧1回目：強制的な再同期を行う • データ量が多いDBでダンプ＆リストアには時間がかかるため、 グループレプリケーションを解体したくなかった • グローバル変数 「group_replication_allow_local_disjoint_gtids_join」を有効 化して、強制的に再同期を実施した • その時は上手く行ったかのように見えたのだが、数日後に問題 が発生した MySQL :: MySQL 5.7 Reference Manual :: 17.6 Group Replication System Variables https://dev.mysql.com/doc/refman/5.7/en/group-replication- options.html#sysvar_group_replication_allow_local_disjoint_gtids_join 

コミット遅延の発生 • 更新APIのタイムアウトが発生するようになってしまった • スローログを見るとcommitで遅延が発生している • グループレプリケーションは準同期的なcommitを実行しているのです # Time: 2019-03-04T03:45:24.296818+09:00 # User@Host: xxx[xxxxx] @ [xx.x.x.xxx] Id: 1248133 # Query_time: 25.366314 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 use xxxxx; SET timestamp=1551638724; commit; 

コミット遅延の解消 • 全てのデータベースでグループレプリケーションを停止する事 でコミット遅延が解消した • その結果、耐障害性がかなり下がった • マスタースレーブレプリケーションを行う参照系DBが存在するため、 耐障害性はゼロではないのが救いだった 

教訓 • グローバル変数 「group_replication_allow_local_disjoint_gtids_join」は使う べきではない 

復旧2回目：ダンプ＆リストア＆再同期 • ユーザーデータベースだけをダンプして待機系へリストア • グループレプリケーションは復旧できた • 復旧した更新系DBと、マスタースレーブレプリケーションし ている参照系DBとの間で、レプリケーションが開始できない 問題が発生 

その際のエラーログ 2019-03-08T06:08:22.937343+09:00 17 [ERROR] Error reading packet from server for channel '': Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replicated to the slave. Suggest to replicate any transactions that master has rolled back from slave to master, and/or commit empty transactions on master to account for transactions that have been (server_errno=1236) マスターのSERVER_UUIDを使用すると、スレーブはマスターよりも多くのGTIDを持ちま す。 

復旧3回目：全DBダンプ＆リストア＆再 同期 • mysqlデータベースもリストアすることにした • 全データベースをダンプして待機系へリストア • グループレプリケーションは復旧できた • 復旧した更新系DBと、マスタースレーブレプリケーションし ている参照系DBとの間で、レプリケーションが開始できない • またもやマスターにGTIDが足りないと言われる 

結局、GTIDの何に差があったのか root@localhost [(none)] > select * from mysql.gtid_executed; +--------------------------------------+----------------+--------------+ | source_uuid | interval_start | interval_end | +--------------------------------------+----------------+--------------+ | 1b7ca97b-6164-11e8-8035-246e96b82c98 | 1 | 227533311 | | 1b7ca97b-6164-11e8-8035-246e96b82c98 | 227533312 | 227679473 | | 1b7ca97b-6164-11e8-8035-246e96b82c98 | 228290055 | 228703862 | | 1b7ca97b-6164-11e8-8035-246e96b82c98 | 228703863 | 228703864 | | 1b7ca97b-6164-11e8-8035-246e96b82c98 | 229703863 | 229703864 | | 3cc7dc1a-60a9-11e8-835f-246e96b82cf8 | 1 | 714284 | | 60bc34af-60a7-11e8-83a2-246e96b82c98 | 1 | 3 | | 766c298c-60a9-11e8-84e0-246e96b82c30 | 1 | 411 | | 766c298c-60a9-11e8-84e0-246e96b82c30 | 412 | 413 | | f2349267-60a7-11e8-b6ba-246e96b6ee40 | 1 | 2 | +--------------------------------------+----------------+--------------+ • スレーブのGTID_EXECUTEDには、5種類のSERVER_UUIDが入っ ていた • ここがマスターと一致していなかった ここはグループレプ リケーションのUUID ここは個別サーバー のUUID 

教訓 • GTIDの仕組みをちゃんと理解しよう • レプリケーションの復旧は全コピーが基本で確実 MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.4.2.1 CHANGE MASTER TO 構文 https://dev.mysql.com/doc/refman/5.6/ja/change-master-to.html 

復旧4回目：物理コピーの実施 • 復旧作業に3回失敗したため、確実に復旧できる方法が求めら れた • 復旧が必要なDBとは別に、スナップショットを格納したDBを 構築 • 更新系DBを全停止して、物理コピーを実施 • 全DBを全く同一にしてグループレプリケーションとマスター スレーブレプリケーションを再構築 • スナップショットDBから更新系DBへ差分の書き戻しを行い データを復旧 

物理コピー実施時のデータベース構成 M LB M M S S S API 

物理コピー実施時のデータベース構成 M LB M M S S S M S S 一時利用DBを用意 API ①スナップショット ⑤差分書き戻し ②向き先変更 ④向き先を戻す ③物理コピーを実施 

発生した問題を整理すると • 2つの問題が発生していた • グループレプリケーションの問題 • 同期が再開できない • マスタースレーブレプリケーションの問題 • 特定のマスターとしか同期ができない • 結局、物理コピーで全DBを全く同じ状態にする事で復旧した 

教訓のまとめ • グループレプリケーションはシングルプライマリモードの方が 安全 • マルチマスターモードで同期停止を行うときは、確実にクライ アントからの接続が無い状態を作り出すべき • グループレプリケーションの構成台数は2台以下にしてはいけ ない • 「group_replication_allow_local_disjoint_gtids_join」は使う べきではない • GTIDの仕組みをちゃんと理解しよう • レプリケーションの復旧は全コピーが基本で確実 

教訓のまとめのまとめ • 教訓を見直すと当たり前のことしか書いてない • 一行にまとめると「基本に忠実に」 

朗報 • MySQL 8.0.17 でデータベースをクローンを作成する機能が追 加されたようです！ MySQL InnoDB Cluster – What’s new in Shell AdminAPI 8.0.17 release | MySQL Server Blog https://mysqlserverteam.com/mysql-innodb-cluster-whats-new-in-shell-adminapi-8-0-17- release/ しかしMySQL 8.0.17ではそれがようやく変わりました！MySQLでのクローン作成サポートの導入とグ ループレプリケーションによる統合により、ついにこのような制限を克服し、すぐに使用可能な完全 なHAソリューションに移行することができました。 クローンプラグインは、一般的には、データベースの物理的なスナップショットを作成し、それらを ネットワーク経由でプロビジョニングサーバーに転送することを可能にします。したがって、外部 ツールなしでリモートクローン作成が可能になります。 

一緒に働く仲間を募集中です！ • 笑いの絶えない明るい職場です！ • 福利厚生がととのっています！ • 化粧品を安く買えます！ 採用 [istyle 株式会社アイスタイル] https://www.istyle.co.jp/recruit/