InnoDB Clusterの設定を行い、Group ReplicationはシングルマスターモードがDefaultなので、
Auto_Incrementの値も普段使いなれている値の1に設定し直して利用する事にしました。

Note: InnoDB Cluster = MySQL Group Replication + MySQL Router + MySQL Shell

もし、Group Replicationをシングルマスターモードで利用する予定の場合は、
実際にサーバーの初期設定時の段階で予め変更しておくと良いかと思います。
マルチマスターモードの場合は、ぶつからないように設定しておく必要があるので、
先ずは、Default設定の7で利用するのが良いでしょう。

念の為、構成がシングルマスターモードになっているか確認
Confirm is Group Replication configured as Single Master Mode.


mysql> show variables like 'group_replication_single_primary_mode';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON    |
+---------------------------------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'group_replication_enforce_update_everywhere_checks';
+----------------------------------------------------+-------+
| Variable_name                                      | Value |
+----------------------------------------------------+-------+
| group_replication_enforce_update_everywhere_checks | OFF   |
+----------------------------------------------------+-------+
1 row in set (0.00 sec)


mysql> SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME='group_replication_primary_member';
+----------------------------------+--------------------------------------+
| VARIABLE_NAME                    | VARIABLE_VALUE                       |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | bc653b5a-3b8b-11e7-94cd-080027d65c57 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> 

現在のグループ構成
Current Group Configuration.


-bash-4.2$ ./2_gr_status.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | bc653b5a-3b8b-11e7-94cd-080027d65c57 | replications |       63301 | ONLINE       |
| group_replication_applier | c68819f0-3b8b-11e7-958b-080027d65c57 | replications |       63302 | ONLINE       |
| group_replication_applier | d0a3d2c8-3b8b-11e7-97ef-080027d65c57 | replications |       63303 | ONLINE       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
-bash-4.2$ 

Enterprise Monitorのレプリケーショントポロジービューでの確認

DefaultでAuto_Incrementが7になっている事も確認出来る

Group Replication設定時のDefault値
マルチマスターモードであれば、このまま利用するが個人的には、
auto_incrementの値は1つずつ増えて欲しいので通常のMySQLの設定に変更。

mysql> show variables like '%increment%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| auto_increment_increment                   | 7     |
| auto_increment_offset                      | 1     |
| div_precision_increment                    | 4     |
| group_replication_auto_increment_increment | 7     |
| innodb_autoextend_increment                | 64    |
+--------------------------------------------+-------+
5 rows in set (0.00 sec)

値を変更:group_replication_auto_increment_increment=1
Change group_replication_auto_increment_increment for change auto increment vaule will be + 1.


mysql> show variables like '%increment%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| auto_increment_increment                   | 1     |
| auto_increment_offset                      | 1     |
| div_precision_increment                    | 4     |
| group_replication_auto_increment_increment | 1     |
| innodb_autoextend_increment                | 64    |
+--------------------------------------------+-------+
5 rows in set (0.01 sec)

設定変更後はMySQL Enterprise Monitorで確認しても、もちろんauto_incrementの値が1になっている

設定変更後のauto_incrementの動作確認
Confirm after change configuration.


mysql> CREATE TABLE `T_MEMO` (
    -> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    -> `comment` varchar(100) NOT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.39 sec)

mysql> insert into T_MEMO(comment) values('Change group_replication_auto_increment_increment from 7 to 1');
Query OK, 1 row affected (0.77 sec)

mysql> select * from T_MEMO;
+----+---------------------------------------------------------------+
| id | comment                                                       |
+----+---------------------------------------------------------------+
|  1 | Change group_replication_auto_increment_increment from 7 to 1 |
+----+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into T_MEMO(comment) values('Change id 1 possible only on Singale Master Mode');
Query OK, 1 row affected (0.11 sec)

mysql> select * from T_MEMO;
+----+---------------------------------------------------------------+
| id | comment                                                       |
+----+---------------------------------------------------------------+
|  1 | Change group_replication_auto_increment_increment from 7 to 1 |
|  2 | Change id 1 possible only on Singale Master Mode              |
+----+---------------------------------------------------------------+
2 rows in set (0.00 sec)

【メモ】シングルマスターモードなので、トランザクション分離レベルはREPEATABLE-READのままでOK。
Node: Since this is single master mode, user can keep transactiton isolation level as REPEATABLE-READ.


mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)


複合インデクス(インデクスの構成列が複数ある定義)で、二つ目の列に
auto_incrementを指定できるのは、ストレージエンジンがMyISAMかBDBの場合だけです。
最後にauto_incrementの値を1に戻す方法の検証を行いました。

===================================================================================
                   【MYISAM】
===================================================================================

CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES (“dog”),(“cat”),(“penguin”),
(“lax”),(“whale”),(“ostrich”);

mysql> INSERT INTO animals (name) VALUES (“dog”),(“cat”),(“penguin”),
-> (“lax”),(“whale”),(“ostrich”);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM animals;
+—-+———+
| id | name |
+—-+———+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+—-+———+
6 rows in set (0.00 sec)

mysql>

mysql> show create table animals;
+———–+——————————————————————-
| Table | Create Table
+———–+——————————————————————-
| animals | CREATE TABLE `animals` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`name` char(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
mysql>

=================================================================================
MyISAM テーブルと BDB テーブルでは、複合インデックスの2つめのカラムに
AUTO_INCREMENT を指定できます。この場合、AUTO_INCREMENT
カラムで生成される値は、MAX(auto_increment_column)+1) WHERE prefix=given-prefix
として計算されます。これは、データを順序付きのグループに分割する場合に便利です。
=================================================================================

mysql> CREATE TABLE animals02 (
-> grp ENUM(‘fish’,’mammal’,’bird’) NOT NULL,
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (grp,id)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO animals02 (grp,name) VALUES(“mammal”,”dog”),(“mammal”,”cat”),
-> (“bird”,”penguin”),(“fish”,”lax”),(“mammal”,”whale”),
-> (“bird”,”ostrich”);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM animals02 ORDER BY grp,id;
+——–+—-+———+
| grp | id | name |
+——–+—-+———+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+——–+—-+———+
6 rows in set (0.00 sec)

mysql>

mysql> desc animals02;
+——-+——————————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+——————————+——+—–+———+—————-+
| grp | enum(‘fish’,’mammal’,’bird’) | NO | PRI | NULL | |
| id | mediumint(9) | NO | PRI | NULL | auto_increment |
| name | char(30) | NO | | NULL | |
+——-+——————————+——+—–+———+—————-+
3 rows in set (0.00 sec)

mysql> show create table animals02;
+———–+——————————————————————-
| Table | Create Table
+———–+——————————————————————-
| animals02 | CREATE TABLE `animals02` (
`grp` enum(‘fish’,’mammal’,’bird’) NOT NULL,
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`name` char(30) NOT NULL,
PRIMARY KEY (`grp`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+———–+——————————————————————-+
1 row in set (0.00 sec)

===================================================================================
                  【INNODB】
===================================================================================

mysql> CREATE TABLE animals03 (
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (id)
-> )ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO animals03 (name) VALUES (“dog”),(“cat”),(“penguin”),
-> (“lax”),(“whale”),(“ostrich”);
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from animals03;
+—-+———+
| id | name |
+—-+———+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+—-+———+
6 rows in set (0.00 sec)

mysql>

mysql> show create table animals03;
+———–+——————————————————————-
| Table | Create Table
+———–+——————————————————————-
| animals03 | CREATE TABLE `animals03` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`name` char(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |

※ MYISAMだと可能でしたが、INNODBだと作成出来ない。
mysql> CREATE TABLE animals04 (
-> grp ENUM(‘fish’,’mammal’,’bird’) NOT NULL,
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (grp,id)
-> ) ENGINE=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column
and it must be defined as a key
mysql>

※INNODBでは、idのみをPKに設定すると作成できました。

mysql> CREATE TABLE animals04 (
-> grp ENUM(‘fish’,’mammal’,’bird’) NOT NULL,
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> INSERT INTO animals04 (grp,name) VALUES(“mammal”,”dog”),(“mammal”,”cat”),
-> (“bird”,”penguin”),(“fish”,”lax”),(“mammal”,”whale”),(“bird”,”ostrich”);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from animals04;
+——–+—-+———+
| grp | id | name |
+——–+—-+———+
| mammal | 1 | dog |
| mammal | 2 | cat |
| bird | 3 | penguin |
| fish | 4 | lax |
| mammal | 5 | whale |
| bird | 6 | ostrich |
+——–+—-+———+
6 rows in set (0.00 sec)

mysql>

■■■おまけ■■■
INCRIMENTは、TRUNCATEだと”0″からになるがDELETEだと他のDBと同じように
再度1から開始出来ない。どうすれば良いだろうか?

mysql> delete from animals04;
Query OK, 6 rows affected (0.01 sec)

mysql> select * from animals04;
Empty set (0.00 sec)

mysql> INSERT INTO animals04 (grp,name) VALUES(“mammal”,”dog”),(“mammal”,”cat”),
(“bird”,”penguin”),(“fish”,”lax”),(“mammal”,”whale”),(“bird”,”ostrich”);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from animals04;
+——–+—-+———+
| grp | id | name |
+——–+—-+———+
| mammal | 7 | dog |
| mammal | 8 | cat |
| bird | 9 | penguin |
| fish | 10 | lax |
| mammal | 11 | whale |
| bird | 12 | ostrich |
+——–+—-+———+
6 rows in set (0.00 sec)

mysql>

mysql> delete from animals04;
Query OK, 6 rows affected (0.01 sec)

mysql> select * from animals04;
Empty set (0.00 sec)

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
ALTER TABLE `テーブル名` PACK_KEYS =0 CHECKSUM =0 DELAY_KEY_WRITE =0 AUTO_INCREMENT =1;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

mysql> ALTER TABLE `animals04` PACK_KEYS =0 CHECKSUM =0 DELAY_KEY_WRITE =0 AUTO_
INCREMENT =1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> INSERT INTO animals04 (grp,name) VALUES(“mammal”,”dog”),(“mammal”,”cat”),
(“bird”,”penguin”),(“fish”,”lax”),(“mammal”,”whale”),(“bird”,”ostrich”);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

※再び1から開始出来ました。

mysql> select * from animals04;
+——–+—-+———+
| grp | id | name |
+——–+—-+———+
| mammal | 1 | dog |
| mammal | 2 | cat |
| bird | 3 | penguin |
| fish | 4 | lax |
| mammal | 5 | whale |
| bird | 6 | ostrich |
+——–+—-+———+
6 rows in set (0.00 sec)

mysql>

auto_increment