TL;DR
- たとえシングルプライマリモードだろうと、GTIDは「そのマシンが使うレンジ」が先に割り当てられて、その中からGTIDを払い出していく
- レンジの大きさは
group_replication_gtid_assignment_block_sizeデフォルト100万で決められる - 元プライマリが使っていたGTIDレンジは、割り当てられたレンジが使い切られない限り、再びプライマリに戻った時に再利用する。
yt-sandbox ってやつでグループレプリケーション環境のコンテナが簡単に立ち上げられるらしいよ（ステマ）
$ yt-sandbox -t gr 8.4
[2816233] NOTE: Generate Sandbox directry into /home/yoku0825/yt-sandbox/golf
[2816233] NOTE: Node1 Container Ipaddress: 172.17.0.2
[2816233] NOTE: Node2 Container Ipaddress: 172.17.0.3
[2816233] NOTE: Node3 Container Ipaddress: 172.17.0.4
Sandbox deployed into /home/yoku0825/yt-sandbox/golf
$ cd /home/yoku0825/yt-sandbox/golf
$ ll
total 28
-rwxr-xr-x. 1 yoku0825 yoku0825 485 Jun 26 08:26 check_group_replication
-rw-r--r--. 1 yoku0825 yoku0825 97 Jun 26 08:26 destroy_all
-rw-r--r--. 1 yoku0825 yoku0825 54 Jun 26 08:26 hosts
lrwxrwxrwx. 1 yoku0825 yoku0825 40 Jun 26 08:26 m -> /home/yoku0825/yt-sandbox/golf/node1/use
drwxr-xr-x. 3 yoku0825 yoku0825 101 Jun 26 08:26 node1
drwxr-xr-x. 3 yoku0825 yoku0825 101 Jun 26 08:26 node2
drwxr-xr-x. 3 yoku0825 yoku0825 101 Jun 26 08:26 node3
-rwxr-xr-x. 1 yoku0825 yoku0825 97 Jun 26 08:26 restart_all
lrwxrwxrwx. 1 yoku0825 yoku0825 40 Jun 26 08:26 s1 -> /home/yoku0825/yt-sandbox/golf/node2/use
lrwxrwxrwx. 1 yoku0825 yoku0825 40 Jun 26 08:26 s2 -> /home/yoku0825/yt-sandbox/golf/node3/use
-rwxr-xr-x. 1 yoku0825 yoku0825 95 Jun 26 08:26 start_all
-rwxr-xr-x. 1 yoku0825 yoku0825 94 Jun 26 08:26 stop_all
-rwxr-xr-x. 1 yoku0825 yoku0825 93 Jun 26 08:26 use_all
シングルプライマリーで上がってくるので、1号機に接続して操作。
$ ./m
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 8.4.9 MySQL Community Server - GPL
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
golf-1> SHOW BINARY LOG STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 158 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
golf-1> create database d1;
Query OK, 1 row affected (0.01 sec)
golf-1> create table d1.t1 (num serial, val varchar(32));
Query OK, 0 rows affected (0.02 sec)
golf-1> INSERT INTO d1.t1 VALUES (1, 'one');
Query OK, 1 row affected (0.00 sec)
golf-1>
golf-1>
golf-1> SHOW BINARY LOG STATUS;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 | 848 | | | 01234567-89ab-cdef-0123-456789abcdef:1-3 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
golf-1> ^DBye
2号機をプライマリーにして書き込み。
100万番単位でGTIDが割り振られるっぽく、100万飛んで1のGTIDが振られる。
レンジの大きさは
group_replication_gtid_assignment_block_size デフォルト100万で決められる。$ ./s1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 8.4.9 MySQL Community Server - GPL
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
golf-2> SELECT group_replication_set_as_primary(@@server_uuid);
+------------------------------------------------------------------+
| group_replication_set_as_primary(@@server_uuid) |
+------------------------------------------------------------------+
| Primary server switched to: b91f989d-7138-11f1-aa86-0242ac110003 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
golf-2>
golf-2> SHOW BINARY LOG STATUS;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 | 845 | | | 01234567-89ab-cdef-0123-456789abcdef:1-3 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
golf-2>
golf-2> INSERT INTO d1.t1 VALUES (3, 'three');
Query OK, 1 row affected (0.00 sec)
golf-2>
golf-2> SELECT * FROM d1.t1;
+-----+-------+
| num | val |
+-----+-------+
| 1 | one |
| 3 | three |
+-----+-------+
2 rows in set (0.01 sec)
golf-2>
golf-2> SHOW BINARY LOG STATUS;
+---------------+----------+--------------+------------------+--------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+--------------------------------------------------+
| binlog.000001 | 1137 | | | 01234567-89ab-cdef-0123-456789abcdef:1-3:1000001 |
+---------------+----------+--------------+------------------+--------------------------------------------------+
1 row in set (0.00 sec)
1号機にプライマリーを戻して書くと、3の次の4が使い回される。
$ ./m
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 8.4.9 MySQL Community Server - GPL
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
golf-1> SELECT group_replication_set_as_primary(@@server_uuid);
+------------------------------------------------------------------+
| group_replication_set_as_primary(@@server_uuid) |
+------------------------------------------------------------------+
| Primary server switched to: b24cf04e-7138-11f1-b6c3-0242ac110002 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
golf-1>
golf-1> SHOW BINARY LOG STATUS;
+---------------+----------+--------------+------------------+--------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+--------------------------------------------------+
| binlog.000001 | 1137 | | | 01234567-89ab-cdef-0123-456789abcdef:1-3:1000001 |
+---------------+----------+--------------+------------------+--------------------------------------------------+
1 row in set (0.00 sec)
golf-1>
golf-1> INSERT INTO d1.t1 VALUES (4, 'four');
Query OK, 1 row affected (0.00 sec)
golf-1>
golf-1> SHOW BINARY LOG STATUS;
+---------------+----------+--------------+------------------+--------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+--------------------------------------------------+
| binlog.000001 | 1428 | | | 01234567-89ab-cdef-0123-456789abcdef:1-4:1000001 |
+---------------+----------+--------------+------------------+--------------------------------------------------+
1 row in set (0.00 sec)
マルチプライマリなら疑問に思うこともないくらい自然な動作だし、シングルプライマリーでも super_read_only 外したら書けちゃうくらいなので、そのへんの動作は一緒なのであろう。
