MySQL はどのようにしてマスターとスレーブの一貫性を確保するのでしょうか?

質問: ご存知のとおり、binlog はアーカイブやマスターとスレーブの同期に使用できますが、その内容は何ですか? binlog を実行した後、スタンバイ データベースがプライマリ データベースと一貫性を保つことができるのはなぜですか?

MySQLマスタースレーブの基本原理

図1 MySQLのマスタースレーブ切り替えプロセス

状態 1 では、クライアントの読み取りおよび書き込み操作はノード A に直接アクセスし、ノード B は A のバックアップ データベースです。A のすべての更新を同期し、ローカルで実行するだけです。これにより、ノード B とノード A のデータは同じままになります。
切り替えが必要な場合は、状態 2 に切り替えます。このとき、クライアントはノード B に対して読み取りと書き込みを行い、ノード A はノード B のバックアップ データベースとなります。
状態 1 では、ノード B に直接アクセスされませんが、ノード B (つまり、スタンバイ データベース) を読み取り専用モードに設定することをお勧めします。これを行うには、いくつかの考慮事項があります。

• 場合によっては、一部の操作クエリ ステートメントがクエリのためにスタンバイ データベースに配置されることがあります。これを読み取り専用に設定すると、誤操作を防ぐことができます。
• 切り替えプロセス中の二重書き込みなど、プライマリとスタンバイ間の不整合を引き起こす切り替えロジックのバグを防止します。
• 読み取り専用状態は、ノードの役割を決定するために使用できます。

バックアップ データベースを読み取り専用に設定しました。プライマリ データベースと同期して更新するにはどうすればよいでしょうか?
この問題について心配する必要はありません。読み取り専用設定はスーパー権限を持つユーザーに対して無効であり、同期更新に使用されるスレッドにはスーパー権限があるためです。

図2 アクティブ/スタンバイフローチャート

マスターデータベースは、クライアントから更新要求を受信すると、内部トランザクションの更新ロジックを実行し、同時にバイナリログを書き込みます。スタンバイ データベース B とプライマリ データベース A の間で長い接続が維持されます。マスター データベース A 内には、スタンバイ データベース B の長時間接続を処理するための専用のスレッドがあります。トランザクション ログ同期の完全なプロセスは次のとおりです。

• スタンバイ データベース B で、change master コマンドを使用して、マスター データベース A の IP、ポート、ユーザー名、パスワード、およびファイル名とログ オフセットを含む binlog の要求を開始する位置を設定します。
• スレーブ データベース B で start slave コマンドを実行します。スレーブ データベースは、図の io_thread と sql_thread という 2 つのスレッドを開始します。このうち、io_thread はメインライブラリとの接続を確立する役割を担っています。
• マスター データベース A は、ユーザー名とパスワードを確認した後、スタンバイ データベース B によって送信されたローカルの場所からバイナリ ログの読み取りを開始し、それを B に送信します。
• スタンバイ データベース B は、バイナリ ログを受信した後、それをリレー ログと呼ばれるローカル ファイルに書き込みます。
• sql_thread は転送ログを読み取り、ログ内のコマンドを解析して実行します。

その後、マルチスレッド レプリケーション ソリューションが導入され、sql_thread は複数のスレッドに進化しました。

3つのbinlog形式の比較

Binlog には 2 つの形式があり、1 つはステートメント、もう 1 つは行です。他の資料では、混合形式と呼ばれる 3 番目の形式も見られることがあります。実際には、これは最初の 2 つの形式が混合されたものです。

mysql> テーブル t を作成します (
	id int(11) NULLではない、
	int(11) デフォルト NULL、
	t_modifiedタイムスタンプ NULLではない デフォルト CURRENT_TIMESTAMP、
	主キー (id)、
	キーa（a）、
	キー t_modified(t_modified)
)ENGINE=InnoDB;

t値(1,1,'2018-11-13')に挿入します。
t値(2,2,'2018-11-12')に挿入します。
t値(3,3,'2018-11-11')に挿入します。
t値(4,4,'2018-11-10')に挿入します。
t値(5,5,'2018-11-09')に挿入します。

テーブル内のデータ行を削除する場合、この削除ステートメントの binlog がどのように記録されるかを確認しましょう。

mysql> t /comment/ から削除します。a>=4 かつ t_modified<='2018-11-10' 制限 1;

binlog_format=statement の場合、SQL ステートメントの元のテキストが binlog に記録されます。 mysql> show binlog events in 'master.000001'; コマンドを使用して、binlog の内容を表示できます。

図3. binlog ステートメント形式の例

それでは、図 3 の出力を見てみましょう。

• 最初の行 SET @@SESSION.GTID_NEXT='ANONYMOUS' は今のところ無視できます。これについては、この記事の後半でアクティブ/スタンバイ スイッチを紹介するときに説明します。
• 2 行目は BEGIN であり、4 行目のコミットに対応し、間にトランザクションがあることを示します。
• 3 行目は実際の実行ステートメントです。実際の削除コマンドが実行される前に、「use 'test'」コマンドがあることがわかります。このコマンドは私たちが積極的に実行するものではなく、操作対象のテーブルが配置されているデータベースに基づいて MySQL によって追加されます。これにより、ログが実行のためにスタンバイ データベースに転送されるときに、現在の作業スレッドがどのデータベースにあるかに関係なく、テスト データベース内のテーブル t を正しく更新できるようになります。
• use 'test' コマンドの後の delete ステートメントは、入力した元の SQL テキストです。ご覧のとおり、binlog は SQL コマンドだけでなくコメントも「忠実に」記録します。
• 最後の行は COMMIT です。内部に xid=61 と書かれています。

ステートメント形式と行形式の違いを説明するために、この削除コマンドの実行効果図を見てみましょう。

図4 削除実行の警告

この削除コマンドを実行すると、現在の binlog がステートメント形式に設定されており、ステートメントに制限があるため、警告が生成され、このコマンドは安全でない可能性があります。なぜそう言うのでしょうか?これは、削除コマンドには制限があり、プライマリ データとスタンバイ データの間に不整合が発生する可能性があるためです。

delete ステートメントでインデックス a を使用する場合、条件を満たす最初の行はインデックス a に基づいて検索されます。つまり、a=4 の行が削除されます。
ただし、インデックス t_modified を使用すると、t_modified='2018-11-09'、つまり a=5 の行が削除されます。

文の元のテキストは文の形式でバイナリログに記録されるため、プライマリ データベースで SQL 文を実行する場合はインデックス a が使用され、スタンバイ データベースで SQL 文を実行する場合はインデックス t_modified が使用されるという状況が発生する可能性があります。したがって、MySQL では、このように記述するのは危険であると見なされます。

binlog 形式を binlog_format='row' に変更すると、この問題は解消されますか?

図5 行形式のbinlogの例

ステートメント形式のbinlogと比較すると、前後のBEGINとCOMMITは同じです。ただし、SQL ステートメントの元のテキストは行形式のバイナリログには存在しなくなり、代わりに Table_map と Delete_rows の 2 つのイベントに置き換えられます。

• Table_map イベント。次に操作するテーブルがテスト データベース内のテーブル t であることを示すために使用されます。
• 削除の動作を定義するために使用される Delete_rows イベント。

mysqlbinlog ツールを使用して、次のコマンドを使用して binlog の内容を解析および表示します。このトランザクションのバイナリログは位置 8900 から始まるため、start-position パラメータを使用して、ログ解析がこの位置から開始されるように指定できます。

mysqlbinlog -vv data/master.000001 --start-position=8900;

図6 行形式のバイナリログの例の詳細

この図から、次の情報を確認できます。

• サーバー ID 1 は、このトランザクションが server_id=1 のデータベースで実行されることを意味します。
• 各イベントには CRC32 値があります。これは、パラメータ binlog_checksum を CRC32 に設定したためです。
• table_map イベントは図 5 に示されているものと同じで、次に開かれるテーブルが番号 226 にマップされていることを示しています。現在、SQL ステートメントは 1 つのテーブルのみを操作しますが、複数のテーブルを操作したい場合はどうすればよいでしょうか。各テーブルには対応する Table_map イベントがあり、異なるテーブルでの操作を区別するために別々の番号にマップされます。
• mysqlbinlog コマンドで -vv パラメータを使用してすべての内容を解析し、結果から各フィールドの値 (たとえば、@1=4、@2=4) を確認できるようにしました。
• binlog_row_image のデフォルト設定は FULL なので、Delete_event には削除された行のすべてのフィールドの値が含まれます。 binlog_row_image を MINIMAL に設定すると、必要な情報のみが記録されます。この例では、id=4 に関する情報のみが記録されます。

最後の Xid イベントは、トランザクションが正常にコミットされたことを示すために使用されます。

binlog_format が行形式を使用する場合、実際に削除された行の主キー ID が binlog に記録されます。このように、binlog がスレーブ データベースに転送されると、id=4 の行が確実に削除され、マスターとスレーブが異なる行を削除するという問題は発生しません。

混合形式のバイナリログが存在するのはなぜですか?

一部のステートメント形式のバイナリログではマスターとスレーブ間で不整合が発生する可能性があるため、行形式を使用する必要があります。

しかし、行形式の欠点は、多くのスペースを占有することです。たとえば、 delete ステートメントを使用して 100,000 行のデータを削除すると、 SQL ステートメントがbinlog に記録され、数十バイトのスペースを占有します。ただし、行形式で binlog を使用する場合は、100,000 件のレコードすべてを binlog に書き込む必要があります。そうすると、より多くのスペースが占有されるだけでなく、binlog を書き込むために IO リソースも消費され、実行速度に影響します。

そのため、MySQL は妥協案として、混合形式の binlog を採用しました。混合形式は、MySQL がこの SQL ステートメントによってマスターとスレーブの間で不整合が発生する可能性があるかどうかを判断します。可能な場合は行形式を使用し、そうでない場合はステートメント形式を使用します。 つまり、混合形式では、データの不整合のリスクを回避しながら、ステートメント形式の利点を活用できます。

現在、ますます多くのシナリオで、MySQL binlog 形式を row に設定することが必要になります。これを行う理由はたくさんありますが、すぐにわかる理由の 1 つは、データの回復です。

データ復旧の問題を、削除、挿入、更新の 3 つの SQL ステートメントの観点から見ていきます。

• 図 6 からわかるように、delete ステートメントを実行しても、行形式の binlog は削除された行の行情報全体を保存します。したがって、削除ステートメントを実行した後に間違ったデータを削除したことがわかった場合は、binlog に記録された削除ステートメントを直接挿入に変換し、挿入し直すことで削除されたデータを復元できます。
• 間違った挿入ステートメントを実行した場合はどうなりますか?それはもっと直接的です。行形式では、すべてのフィールド情報が挿入ステートメントのバイナリログに記録され、これを使用して、挿入されたばかりの行を正確に見つけることができます。このとき、挿入ステートメントを削除ステートメントに変換するだけで、誤って挿入されたデータ行を削除できます。
• 更新ステートメントが実行されると、変更前と変更後のデータ行全体が binlog に記録されます。したがって、誤って更新ステートメントを実行した場合は、イベントの前後の 2 行の情報を入れ替えてデータベースで実行するだけで、更新操作を復元できます。

削除、挿入、または更新ステートメントによって発生したデータ操作エラーでは、多くの場合、操作前の状態への回復が必要になります。 MariaDB の Flashback ツールは、上記の原則に基づいてデータをロールバックします。

mysql> t に値を挿入します (10,10,now());

binlog 形式を混合に設定した場合、MySQL はそれを行形式またはステートメント形式のどちらで記録すると思いますか?

図7 混合フォーマットとnow()

MySQL は実際にはステートメント形式を使用します。次に、mysqlbinlog ツールを使用して確認してみましょう。

図8 TIMESTAMPコマンド

binlog がイベントを記録するときに、SET TIMESTAMP=1546103491 という追加のコマンドが記録されることが判明しました。 SET TIMESTAMP コマンドを使用して、後続の now() 関数の戻り時刻を決定します。この SET TIMESTAMP コマンドにより、MySQL はプライマリ データとスタンバイ データの一貫性を保証します。

binlog データを再生する場合、次のようにします。mysqlbinlog を使用してログを解析し、ログ内のステートメントを直接コピーして実行します。
ご存知のとおり、このアプローチは危険です。一部のステートメントの実行結果はコンテキスト コマンドに依存するため、直接実行した結果は間違っている可能性があります。

したがって、 binlog を使用してデータを回復する標準的な方法は、mysqlbinlog ツールを使用してデータを解析し、解析された結果全体を MySQL に送信して実行することです。次のようなコマンド:

mysqlbinlog master.000001 --start-position=2738 --stop-position=2942 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

このコマンドは、master.000001 ファイルのバイト 2738 からバイト 2942 までの内容を解析し、MySQL に入れて実行することを意味します。

循環複製問題

binlog 機能により、スレーブ データベースで同じ binlog を実行すると、マスター データベースと同じステータスが達成されます。通常の状況では、プライマリ デバイスとバックアップ デバイスのデータは一貫していると想定できます。つまり、図 1 のノード A と B の内容は一貫しています。実は、図 1 に描いたのは MS 構造ですが、実際の生産ではデュアル M 構造の方が一般的に使用されており、図 9 に示すアクティブ スタンバイ切り替えプロセスとなっています。

図9 MySQLマスタースレーブ切り替えプロセス - デュアルM構造

ノード A と B は常に相互にマスターとスレーブの関係にあります。この方法では、切り替え時にマスターとスレーブの関係を変更する必要はありません。

しかし、ダブルM構造にはまだ解決すべき問題が残っています。

ビジネス ロジックはノード A 上のステートメントを更新し、生成されたバイナリ ログをノード B に送信します。ノード B も更新ステートメントを実行した後にバイナリ ログを生成します。 (パラメータ log_slave_updates をオンに設定することをお勧めします。これは、スタンバイ データベースがリレー ログの実行後に binlog を生成することを意味します)。

そして、ノード A がノード B のバックアップ データベースでもある場合、ノード B の新しく生成された binlog を取得して再度実行するのと同等になります。すると、この更新ステートメントがノード A と B の間で繰り返し実行され、循環レプリケーションが行われます。この問題をどうやって解決するのでしょうか?

上記の図 6 に示すように、 MySQL はこのコマンドが最初に実行されたインスタンスのサーバー ID を binlog に記録します。したがって、次のロジックを使用して、2 つのノード間の循環レプリケーションの問題を解決できます。

• 2 つのデータベースのサーバー ID は異なる必要があります。同じ場合は、プライマリとバックアップの関係として設定できません。
• スタンバイ データベースはバイナリ ログを受信し、再生プロセス中に元のバイナリ ログと同じサーバー ID を持つ新しいバイナリ ログを生成します。
• 各データベースは、メインデータベースから送信されたログを受信した後、まずサーバー ID を決定します。それが自身のものと同じであれば、ログは自身で生成されたものであることを意味し、ログをそのまま破棄します。

このロジックに従って、二重の M 構造を設定すると、ログの実行フローは次のようになります。

• ノード A から更新されたトランザクションの場合、A のサーバー ID が binlog に記録されます。
• ノード B に送信され、一度実行されると、ノード B によって生成されたバイナリログのサーバー ID は A のサーバー ID にもなります。
• その後、ログはノード A に送り返されます。A は、サーバー ID が自分のものと同じであると判断すると、ログを処理しなくなります。したがって、ここで無限ループは解消されます。

要約:

これで、MySQL がプライマリ サーバーとバックアップ サーバー間の一貫性を確保する方法についての説明は終了です。MySQL がプライマリ サーバーとバックアップ サーバー間の一貫性を確保する方法の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。