その行がマスターにINSERTされてからスレーブでSELECTされるまで

その行が(平成に)マスターにINSERTされてから (令和に)スレーブでSELECTされるまで「それ、違うわよ」君はつぶやいた。「それは、村上龍」と続けたあと、僕の顔をのぞき込む。「村上龍だったっけ？」僕の言葉が聞こえなかったかのように、物憂げに窓の外に視線を移しながら君は話題を変えた。「レプリケーション」「レプリケーション？」「そう。レプリケーション。マルチマスターの」 ©sakaik 2019/10/02 yoku0825 日本OSS推進フォーラムアプリケーション部会
第11回勉強会

＼こんにちは／ yoku0825＠とある企業のDBA オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 生息域 Twitter:
@yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会 ‐ MySQL Casual ‐ 1/77

イベントの概要 OSSをITに活用したい技術者および技術者を目指す総ての人々を対象とした、技術力向上を目的とした技術セミナです。 https://ossforum.connpass.com/event/145515/ 2/77

というわけでレプリケーションの基本的な仕組みをおはなしします活用方法や構築方法には触れません痛い目を見た話もしませんグループレプリケーションは名前だけ出てきます 3/77

レプリケーションの基本的な仕組みだけをおはなしします（自戒） 4/77

レプリケーションの考え方（？）マスター（1台）に対してスレーブ（N台）を構成する最近はプライマリー, セカンダリーって呼んだりもするみたいですが ‐ マスターに対して更新SQLを投げるとスレーブにも更新がかかって同じデータになる 5/77

レプリケーションの基本的な仕組み 6/77

ふしぎなちから 9/77

さぞかし高度な技術を用いて実装されているんでしょう？ 10/77

仕組みそのものはシンプル 11/77

レプリケーションが構成されていない3台の別のサーバーでも 12/77

同じことをバックグラウンドでやってくれるのがレプリケーション 13/77

レプリケーションの基本的な仕組みふしぎなちからではない本質的には「マスターに適用された更新のSQLを伝達し、スレーブでリプレイする」 ‐ キーワードバイナリログ, リレーログ ‐ Binlog Dumpスレッド,
I/Oスレッド(Receiverスレッド), SQLスレッド(Applierスレッド) ‐ 17/77

その行がマスターにINSERTされてからスレーブでSELECTされるまで clientA connection executor storage_engine binlog binlog_dump io_thread relaylog slave_executor
sql_thread binlog storage_engine slave_connection clientB master slave 18/77

レプリケーションの区別あれこれ同期のスタイル非同期 ‐ 準同期 ‐ 仮想同期 ‐ トポロジーマスター/スレーブ
‐ マルチマスター ‐ イベントの粒度ステートメント単位 ‐ 行単位 ‐ 20/77

レプリケーションの区別あれこれ同期型トポロジーソリューション非同期マスター/スレーブ標準非同期マルチマスター (やりようはあるけど推奨しない)
準同期マスター/スレーブ semisyncプラグイン準同期マルチマスター semisyncプラグイン + (やりようはあるけど推奨しない) 仮想同期マスター/スレーブ Group Replication(シングルプライマリーモード) (*) 仮想同期マルチマスター Group Replication(マルチプライマリーモード) 仮想同期マルチマスター Galera Cluster (*) MySQL 8.0.17現在、 super_read_only オプションを利用して制御しているだけで実体はマルチプライマリーモードと同じ 21/77

‐ マルチマスター ‐ 22/77

非同期レプリケーション MySQL標準のレプリケーションマスターに対して実行されたSQLはマスターのバイナリログにのみ同期で書き込まれる 23/77

非同期レプリケーション 24/77

非同期レプリケーション(again) MySQL標準のレプリケーションマスターに対して実行されたSQLはマスターのバイナリログにのみ同期で書き込まれる 25/77

準同期レプリケーション MySQL 5.5とそれ以降に標準でバンドルされているsemisyncプラグインを使うレプリケーションの構築は標準のレプリケーションと同じ semisyncプラグインが有効化されているかされていないかだけで切り替え ‐ マスターに対して実行されたSQLはマスターのバイナリログに書き込まれ、スレーブのリレーログに書き込まれるまでCOMMITが遅延される 27/77

準同期レプリケーション 28/77

準同期レプリケーション(again) MySQL 5.5とそれ以降に標準でバンドルされているsemisyncプラグインを使うレプリケーションの構築は標準のレプリケーションと同じ semisyncプラグインが有効化されているかされていないかだけで切り替え ‐ マスターに対して実行されたSQLはマスターのバイナリログに書き込まれ、スレーブのリレーログに書き込まれるまでCOMMITが遅延される 29/77

仮想同期レプリケーション MySQL 5.7とそれ以降に標準でバンドルされているgroup_replicationプラグイン, またはGarela Replication対応MySQL(Galera Cluster for MySQL, Percona
XtraDB Cluster, MariaDB Galera Cluster)を使う原則マルチマスターで、グループを構成するノード同士で相互にメッシュ状にコミュニケーションパスを張る MySQL 8.0.17現在、Group Replicationのシングルプライマリーモードは super_read_only オプションを利用して制御しているだけで実体はマルチプライマリーモードと同じ ‐ あるノードに対して実行されたSQLはバイナリログ(Galera Clusterの場合は Galera Cacheという別領域)に書き込まれ、他のノードのリレーログに書き込まれてSQLスレッドが爪を引っ掛けるところまでコミットが遅延される 31/77

仮想同期レプリケーション 32/77

仮想同期レプリケーション(again) MySQL 5.7とそれ以降に標準でバンドルされているgroup_replicationプラグイン, またはGarela Replication対応MySQL(Galera Cluster for MySQL, Percona
XtraDB Cluster, MariaDB Galera Cluster)を使う原則マルチマスターで、グループを構成するノード同士で相互にメッシュ状にコミュニケーションパスを張る MySQL 8.0.17現在、Group Replicationのシングルプライマリーモードは super_read_only オプションを利用して制御しているだけで実体はマルチプライマリーモードと同じ ‐ あるノードに対して実行されたSQLはバイナリログ(Galera Clusterの場合は Galera Cacheという別領域)に書き込まれ、他のノードのリレーログに書き込まれてSQLスレッドが爪を引っ掛けるところまでコミットが遅延される 33/77

仮想同期レプリケーションの特徴他のスタイルの同期と違い、「更新の競合」を検出するところまでやる更新が競合したら、どちらか片方はロールバックされる（エラーが返る） ‐ 更新さえ競合しなければあとはスムーズに更新されるであろう、という楽観モデルではあるじゃあ更新が完了するまで待ったら？となると、SQLを受け付けたノードでCOMMIT完了の応答が返る前に他のノードで更新が完了した行が見えてしまうのでこれ以上はアレだと思う ‐
34/77

マスター/スレーブ構成 MySQL標準のレプリケーション構成スレーブは read_only, super_read_only オプションを利用して書き込みを禁止するのが良いスレーブに書き込んだSQLはその他のノードに伝搬されない ‐ マスターとスレーブでデータが違う場合、同じSQLをリプレイしても結果が同じになるとは限
らない ‐ 非対称構成なので往々にしてフェイルオーバーが難しいスレーブの切り離しは簡単だけれど ‐ マスターが倒れた場合、既存のスレーブからマスターを選出して新しくレプリケーション構成を構築しなおす必要がある ‐ 36/77

マスター/スレーブ構成 37/77

マスター/スレーブ構成(again) MySQL標準のレプリケーション構成スレーブは read_only, super_read_only オプションを利用して書き込みを禁止するのが良いスレーブに書き込んだSQLはその他のノードに伝搬されない ‐ マスターとスレーブでデータが違う場合、同じSQLをリプレイしても結果が同じになるとは限
らない ‐ 非対称構成なので往々にしてフェイルオーバーが難しいスレーブの切り離しは簡単だけれど ‐ マスターが倒れた場合、既存のスレーブからマスターを選出して新しくレプリケーション構成を構築しなおす必要がある ‐ 38/77

マルチマスター構成原則として「仮想同期」方式のみの構成全てのノードに書き込み, 全てのノードから読み取りが可能グループを構成するノード同士で相互にメッシュ状にコミュニケーションパスを張り、行の更新情報を相互にやり取りし続けるよってシャーディングのような書き込みのスケールはない MySQL NDB Clusterを除く（アレはバックエンド側でシャーディングしている、ついでにレプリケーション構成ではない）
‐ 対称構成なので単純にラウンドロビンするような構成でもHAが組みやすい 40/77

マルチマスター構成 41/77

マルチマスター構成(again) 原則として「仮想同期」方式のみの構成全てのノードに書き込み, 全てのノードから読み取りが可能グループを構成するノード同士で相互にメッシュ状にコミュニケーションパスを張り、行の更新情報を相互にやり取りし続けるよってシャーディングのような書き込みのスケールはない MySQL NDB Clusterを除く（アレはバックエンド側でシャーディングしている、ついでにレプリケーション構成ではない）
‐ 対称構成なので単純にラウンドロビンするような構成でもHAが組みやすい 42/77

レプリケーションの区別あれこれ同期型トポロジーソリューション非同期マスター/スレーブ標準非同期マルチマスター (やりようはあるけど推奨しない)
準同期マスター/スレーブ semisyncプラグイン準同期マルチマスター semisyncプラグイン + (やりようはあるけど推奨しない) 仮想同期マスター/スレーブ Group Replication(シングルプライマリーモード) (*) 仮想同期マルチマスター Group Replication(マルチプライマリーモード) 仮想同期マルチマスター Galera Cluster (*) MySQL 8.0.17現在、 super_read_only オプションを利用して制御しているだけで実体はマルチプライマリーモードと同じ 44/77

ひとやすみ 45/77

バイナリログイベントの粒度 binlog_format というオプションで設定する「存在しない行」に対するUPDATEステートメントは binlog_format= STATEMENT では成功する（0行が更新される、シンタックス的に正しいSQL） binlog_format= ROW では失敗する（存在しない行に対する更新だから）
‐ 46/77

ステートメント単位のバイナリログ # at 9201 #191001 18:01:49 server id 1080 end_log_pos
9279 CRC32 0x4cb8587f Query thread_id =66 exec_time=0 error_code=0 SET TIMESTAMP=1569920509/*!*/; BEGIN /*!*/; # at 9279 #191001 18:01:49 server id 1080 end_log_pos 9384 CRC32 0xad501739 Query thread_id =66 exec_time=0 error_code=0 SET TIMESTAMP=1569920509/*!*/; INSERT INTO t1 VALUES (1, 'one') /*!*/; # at 9384 #191001 18:01:49 server id 1080 end_log_pos 9415 CRC32 0x44cfc96a Xid = 1006236 COMMIT/*!*/; 47/77

行単位のバイナリログ # at 9494 #191001 18:01:58 server id 1080 end_log_pos
9567 CRC32 0x6b37180c Query thread_id=66 exec_time=0 error_code=0 SET TIMESTAMP=1569920518/*!*/; BEGIN /*!*/; # at 9567 #191001 18:01:58 server id 1080 end_log_pos 9621 CRC32 0x53a02174 Table_map: `d1`.`t1` mapped to number 176 # at 9621 #191001 18:01:58 server id 1080 end_log_pos 9669 CRC32 0xc3039a50 Write_rows: table id 176 flags: STMT_END_F BINLOG ' BhaTXRM4BAAANgAAAJUlAAAAALAAAAAAAAEAAmQxAAJ0MQACCA8CgAACAQGAAgP8/wB0IaBT BhaTXR44BAAAMAAAAMUlAAAAALAAAAAAAAEAAgAC/wACAAAAAAAAAAN0d29QmgPD '/*!*/; # at 9669 #191001 18:01:58 server id 1080 end_log_pos 9700 CRC32 0x37305aa1 Xid = 1006238 COMMIT/*!*/; 48/77

バイナリログイベントの粒度(again) binlog_format というオプションで設定する「存在しない行」に対するUPDATEステートメントは binlog_format= STATEMENT では成功する（0行が更新される、シンタックス的に正しいSQL） binlog_format= ROW では失敗する（存在しない行に対する更新だから）
‐ 49/77

バイナリログの中身 mysql80 68> SHOW BINLOG EVENTS IN 'bin.000017'; +------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------- ----------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------- ----------------+ | bin.000017 | 4 | Format_desc | 1080 | 124 | Server ver: 8.0.17, Binlog ver: 4 | | bin.000017 | 124 | Previous_gtids | 1080 | 235 | 03e3ad7d-ad03-11e9-9dc1-0201a32caf75:1-12, 552f89cd-aded-11e9-9af8-0201a32caf75:1-548869 | | bin.000017 | 235 | Gtid | 1080 | 314 | SET @@SESSION.GTID_NEXT= '552f89cd-aded-11e9-9af8-0201a32caf75:548870' | | bin.000017 | 314 | Query | 1080 | 392 | BEGIN | | bin.000017 | 392 | Query | 1080 | 497 | use `d1`; INSERT INTO t1 VALUES (1, 'one') | | bin.000017 | 497 | Xid | 1080 | 528 | COMMIT /* xid=1006254 */ | +------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------- ----------------+ 6 rows in set (0.00 sec) 50/77

バイナリログの中身ちょっと INSERT してから mysql80 68> SELECT * FROM t1;
+-----+------+ | num | val | +-----+------+ | 1 | one | +-----+------+ 1 row in set (0.00 sec) mysql80 68> INSERT INTO t1 VALUES (2, 'two'); Query OK, 1 row affected (0.01 sec) mysql80 68> INSERT INTO t1 VALUES (3, 'three'); Query OK, 1 row affected (0.00 sec) mysql80 68> SELECT * FROM t1; +-----+-------+ | num | val | +-----+-------+ | 1 | one | | 2 | two | | 3 | three | +-----+-------+ 3 rows in set (0.00 sec) 51/77

バイナリログの中身 mysql80 68> SHOW BINLOG EVENTS IN 'bin.000017'; +------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------+ |
Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------+ | bin.000017 | 4 | Format_desc | 1080 | 124 | Server ver: 8.0.17, Binlog ver: 4 | | bin.000017 | 124 | Previous_gtids | 1080 | 235 | 03e3ad7d-ad03-11e9-9dc1-0201a32caf75:1-12, 552f89cd-aded-11e9-9af8-0201a32caf75:1-548869 | | bin.000017 | 235 | Gtid | 1080 | 314 | SET @@SESSION.GTID_NEXT= '552f89cd-aded-11e9-9af8-0201a32caf75:548870' | | bin.000017 | 314 | Query | 1080 | 392 | BEGIN | | bin.000017 | 392 | Query | 1080 | 497 | use `d1`; INSERT INTO t1 VALUES (1, 'one') | | bin.000017 | 497 | Xid | 1080 | 528 | COMMIT /* xid=1006254 */ | | bin.000017 | 528 | Gtid | 1080 | 607 | SET @@SESSION.GTID_NEXT= '552f89cd-aded-11e9-9af8-0201a32caf75:548871' | | bin.000017 | 607 | Query | 1080 | 685 | BEGIN | | bin.000017 | 685 | Query | 1080 | 790 | use `d1`; INSERT INTO t1 VALUES (2, 'two') | | bin.000017 | 790 | Xid | 1080 | 821 | COMMIT /* xid=1006261 */ | | bin.000017 | 821 | Gtid | 1080 | 900 | SET @@SESSION.GTID_NEXT= '552f89cd-aded-11e9-9af8-0201a32caf75:548872' | | bin.000017 | 900 | Query | 1080 | 978 | BEGIN | | bin.000017 | 978 | Query | 1080 | 1085 | use `d1`; INSERT INTO t1 VALUES (3, 'three') | | bin.000017 | 1085 | Xid | 1080 | 1116 | COMMIT /* xid=1006264 */ | +------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------+ 14 rows in set (0.00 sec) 52/77

バイナリログの識別レプリケーションの構築は「スレーブからマスターに対して」「このバイナリログファイルのnバイト目以降をくれ」という要求 mysql80> CHANGE MASTER TO master_host = 'hostname.of.master',
master_port = 3306, -> master_user = 'replication_username', master_password = 'plain_ password_is_unsecure', -> master_log_file = 'bin.000017', master_log_pos = 1085; 53/77

雑に行とポジションを結びつける +-----+-------+ | num | val | +-----+-------+ | 1
| one | <--- bin.000017の235～528 | 2 | two | <--- bin.000017の528～821 | 3 | three | <--- bin.000017の821～1116 +-----+-------+ 55/77

こういうことをすると死ぬ +-----+-------+ | num | val | +-----+-------+ | 1
| one | <--- bin.000017の235～528 +-----+-------+ mysql80> CHANGE MASTER TO .., master_log_file= 'bin.000017', master_position = 821; +-----+-------+ | num | val | +-----+-------+ | 1 | one | <--- bin.000017の235～528 | 3 | three | <--- bin.000017の821～1116 +-----+-------+ 56/77

エラーも吐かずにDBAが死ぬ 57/77

都内のDBA 1.000000人に聞きました gtid_mode = ONは便利ですか？はい ‐ Yes ‐ 59/77

都内のDBA 1.000000人に聞きました gtid_mode = ONは便利ですか？はい (1) ‐ Yes (0)
‐ 60/77

圧倒的支持率（） 61/77

GTID b7200c05-1e8d-11e7-9e57-0201a32caf75:2006 ←こんな感じのフォーマットコロンの手前までが「サーバー識別子」（マスターの @@server_uuid ）、ソースを追っかけるならSIDNO（Source ID NO） datadir/auto.cnfに保管されるので、リストア時に注意
‐ コロンの後ろが「トランザクション識別子」（マスター上でトランザクションがコミットされた連番）、ソースを追っかけるならGNO（Group NO）トランザクションを一意に識別するだけで、バイナリログのイベントを一意に識別するわけではない ‐ 62/77

GTIDの表現 b7200c05-1e8d-11e7-9e57-0201a32caf75:2006 b7200c05-1e8d-11e7-9e57-0201a32caf75:1-2006 b7200c05-1e8d-11e7-9e57-0201a32caf75:1-2006, 758f64f0-1e93-11e7-a3fc-0201a32caf75:1-99 758f64f0-1e93-11e7-a3fc-0201a32caf75:1-99, 758f64f0-1e93-11e7-a3fc-0201a32caf75:1000-10000 63/77

GTIDとI/Oスレッド master_auto_position= 1の場合、「このファイルのこのポジションから寄越せ！」の代わりに、「この（=スレーブが既に適用済みの）GTID 以外のバイナリーログを頼む」になるサーバー側が動的にファイル名とポジションに変換して送信してくれる。 ‐ 64/77

GTIDとI/Oスレッド I/OスレッドとBinlog Dumpスレッドがよしなにやってくれるので、 master_log_file と master_log_pos を設定する必要はもうない mysql80> CHANGE MASTER
TO master_host = 'hostname.of.master', master_port = 3306, -> master_user = 'replication_username', master_password = 'plain_ password_is_unsecure', -> master_auto_position = 1; 65/77

雑に行とGTIDを結びつける +-----+-------+ | num | val | +-----+-------+ | 1
| one | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548870 | 2 | two | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548871 | 3 | three | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548872 +-----+-------+ 66/77

こういうことをしても +-----+-------+ | num | val | +-----+-------+ | 1
| one | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548870 +-----+-------+ mysql80> CHANGE MASTER TO .., master_auto_position = 1; +-----+-------+ | num | val | +-----+-------+ | 1 | one | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548870 | 2 | two | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548871 | 3 | three | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548872 +-----+-------+ 67/77

こんなことをしても +-----+-------+ | num | val | +-----+-------+ | 1
| one | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548870 | 3 | three | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548872 +-----+-------+ mysql80> CHANGE MASTER TO .., master_auto_position = 1; +-----+-------+ | num | val | +-----+-------+ | 1 | one | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548870 | 2 | two | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548871 | 3 | three | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548872 +-----+-------+ 68/77

死なない 69/77

GTIDとSQLスレッドこれから実行するトランザクションのGTID（ gtid_next ）が実行済み（スレーブ側の gtid_executed に含まれる）の場合、実行しない（スキップする）これによってスレーブクラッシュ時の「リレーログの二重適用」が完全に回避できる ‐ sql/sql_parse.cc:mysql_execute_commandの中で分岐するので、エグゼキューターがそも
そも（ほぼ）動かない。 ‐ エグゼキューターなので、SQLスレッドに限らずフツーのスレッドでも同じ動作。 ‐ 70/77

行 vs GTID vs ポジション +-----+-------+ | num | val
| +-----+-------+ | 1 | one | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548870, bin.000017の235～528 | 2 | two | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548871, bin.000017の528～821 | 3 | three | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548872, bin.000017の821～1116 +-----+-------+ 71/77

ポジションベースのレプリケーションやバイナリログを使ったロールフォワードをしても +-----+-------+ | num | val | +-----+-------+ |
1 | one | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548870, bin.000017の235～528 | 2 | two | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548871, bin.000017の528～821 | 3 | three | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548872, bin.000017の821～1116 +-----+-------+ mysql80> CHANGE MASTER TO .., master_log_file = 'bin.000017', master_log_pos = 821; +-----+-------+ | num | val | +-----+-------+ | 1 | one | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548870, bin.000017の235～528 | 2 | two | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548871, bin.000017の528～821 | 3 | three | <--- 552f89cd-aded-11e9-9af8-0201a32caf75:548872, bin.000017の821～1116 <-- 再実行が抑止される +-----+-------+ 72/77

死なない 73/77

GTID I/Oスレッドに対する「歯抜け防止」とSQLスレッドに対する「二重実行防止」はそれぞれ別機能と考えるとわかりやすいちなみに、I/Oスレッドで「マスターのbinlogがexpireされてこのままだと歯抜けになっちゃうよ？」となるとエラーでレプリケーションが開始できない。親切。 ‐ mysqlbinlogを使ったロールフォワードリカバリは「歯抜け防止」機能が完全には働かないので注意 gtid_executedが歯抜けになるからわかるっちゃわかる ‐
ポジションベースのレプリケーション構成を理解した上で、それを拡張するものと理解すると嫌悪感は少ない最近のMySQLから始めた人はどっちを使ってるんでしょうね… ‐ 74/77

その行がマスターにINSERTされてからスレーブでSELECTされるまで clientA connection executor storage_engine binlog binlog_dump io_thread relaylog slave_executor
sql_thread binlog storage_engine slave_connection clientB master slave 75/77

Any Questions and/or Suggestions? 77/77

その行がマスターにINSERTされてからスレーブでSELECTされるまで

その行がマスターにINSERTされてからスレーブでSELECTされるまで

Transcript

More Decks by yoku0825

Other Decks in Technology

Featured