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

0deae06ab5d86b39feeec2e23a30b88a?s=47 yoku0825
October 02, 2019

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

2019/10/02 日本OSS推進フォーラム アプリケーション部会 第11回勉強会
https://ossforum.connpass.com/event/145515/

0deae06ab5d86b39feeec2e23a30b88a?s=128

yoku0825

October 02, 2019
Tweet

Transcript

  1. 21.

    レプリケーションの区別あれこれ 同期のスタイル 非同期 ‐ 準同期 ‐ 仮想同期 ‐ トポロジー マスター/スレーブ

    ‐ マルチマスター ‐ イベントの粒度 ステートメント単位 ‐ 行単位 ‐ 20/77
  2. 22.

    レプリケーションの区別あれこれ 同期型 トポロジー ソリューション 非同期 マスター/スレーブ 標準 非同期 マルチマスター (やりようはあるけど推奨しない)

    準同期 マスター/スレーブ semisyncプラグイン 準同期 マルチマスター semisyncプラグイン + (やりようはあ るけど推奨しない) 仮想同期 マスター/スレーブ Group Replication(シングルプライマ リーモード) (*) 仮想同期 マルチマスター Group Replication(マルチプライマ リーモード) 仮想同期 マルチマスター Galera Cluster (*) MySQL 8.0.17現在、 super_read_only オプションを利用して制御しているだ けで実体はマルチプライマリーモードと同じ 21/77
  3. 32.

    仮想同期レプリケーション 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
  4. 34.

    仮想同期レプリケーション(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
  5. 37.

    マスター/スレーブ構成 MySQL標準のレプリケーション構成 スレーブは read_only, super_read_only オプションを利用して書き込みを禁止す るのが良い スレーブに書き込んだSQLはその他のノードに伝搬されない ‐ マスターとスレーブでデータが違う場合、同じSQLをリプレイしても結果が同じになるとは限

    らない ‐ 非対称構成なので往々にしてフェイルオーバーが難しい スレーブの切り離しは簡単だけれど ‐ マスターが倒れた場合、既存のスレーブからマスターを選出して新しくレプリケーション構成 を構築しなおす必要がある ‐ 36/77
  6. 39.

    マスター/スレーブ構成(again) MySQL標準のレプリケーション構成 スレーブは read_only, super_read_only オプションを利用して書き込みを禁止す るのが良い スレーブに書き込んだSQLはその他のノードに伝搬されない ‐ マスターとスレーブでデータが違う場合、同じSQLをリプレイしても結果が同じになるとは限

    らない ‐ 非対称構成なので往々にしてフェイルオーバーが難しい スレーブの切り離しは簡単だけれど ‐ マスターが倒れた場合、既存のスレーブからマスターを選出して新しくレプリケーション構成 を構築しなおす必要がある ‐ 38/77
  7. 45.

    レプリケーションの区別あれこれ 同期型 トポロジー ソリューション 非同期 マスター/スレーブ 標準 非同期 マルチマスター (やりようはあるけど推奨しない)

    準同期 マスター/スレーブ semisyncプラグイン 準同期 マルチマスター semisyncプラグイン + (やりようはあ るけど推奨しない) 仮想同期 マスター/スレーブ Group Replication(シングルプライマ リーモード) (*) 仮想同期 マルチマスター Group Replication(マルチプライマ リーモード) 仮想同期 マルチマスター Galera Cluster (*) MySQL 8.0.17現在、 super_read_only オプションを利用して制御しているだ けで実体はマルチプライマリーモードと同じ 44/77
  8. 48.

    ステートメント単位のバイナリログ # 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
  9. 49.

    行単位のバイナリログ # 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
  10. 51.

    バイナリログの中身 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
  11. 52.

    バイナリログの中身 ちょっと 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
  12. 53.

    バイナリログの中身 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
  13. 54.

    バイナリログの識別 レプリケーションの構築は「スレーブからマスターに対して」「このバイナリログ ファイルの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
  14. 55.

    バイナリログの中身 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) 54/77
  15. 56.

    雑に行とポジションを結びつける +-----+-------+ | num | val | +-----+-------+ | 1

    | one | <--- bin.000017の235~528 | 2 | two | <--- bin.000017の528~821 | 3 | three | <--- bin.000017の821~1116 +-----+-------+ 55/77
  16. 57.

    こういうことをすると死ぬ +-----+-------+ | 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
  17. 59.

    バイナリログの中身 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) 58/77
  18. 63.

    GTID b7200c05-1e8d-11e7-9e57-0201a32caf75:2006 ←こんな感じのフォーマット コロンの手前までが「サーバー識別子」(マスターの @@server_uuid )、ソースを 追っかけるならSIDNO(Source ID NO) datadir/auto.cnfに保管されるので、リストア時に注意

    ‐ コロンの後ろが「トランザクション識別子」(マスター上でトランザクションがコ ミットされた連番)、ソースを追っかけるならGNO(Group NO) トランザクションを一意に識別するだけで、バイナリログのイベントを一意に識別するわけで はない ‐ 62/77
  19. 66.

    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
  20. 67.

    雑に行と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
  21. 68.

    こういうことをしても +-----+-------+ | 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
  22. 69.

    こんなことをしても +-----+-------+ | 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
  23. 72.

    行 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
  24. 73.

    ポジションベースのレプリケーションやバイナリログを使ったロールフォワー ドをしても +-----+-------+ | 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