MySQL5.7.14のGroup Replication DMR版が先月リリースされていたので、基本的な動作確認をしました。
まだ、DMR版なので前回の5.7.10とまた微妙に異なっていて、
group_replication_peer_addressesがgroup_replication_group_seedsに代わっていたり、
group_replication_recovery_userやgroup_replication_recovery_passwordが無くなっていたりします。

MySQL Group Replication: A Quick Start Guide
http://mysqlhighavailability.com/mysql-group-replication-a-quick-start-guide/

MySQL5.7.10版での検証: MYSQL GROUP REPLICATION
http://variable.jp/2016/07/18/mysql-group-replication/

検証環境のオプションファイル設定
基本的にはserver_idをノード間で変更しているのみ。

# Binary logging and Replication
server_id                      = 1
log_bin                        = mysql-bin
binlog_cache_size              = 1M
binlog_stmt_cache_size         = 1M                                  # Since 5.5
max_binlog_size                = 64M                                 # Make bigger for high traffic to reduce number of files
sync_binlog                    = 0                                   # Set to 1 or higher to reduce potential loss of binary-log data
expire_logs_days               = 30                                  # We will survive easter holidays
#binlog_format                 = MIXED                               # Use MIXED if you experience some troubles
binlog_format                  = ROW
binlog_row_image               = MINIMAL                             # Since 5.6
binlog_rows_query_log_events   = 1                                   # Log Statement in ROW Base Binlog
# auto_increment_increment       = 1                                 # For Master/Master set-ups use 1 and 2
# auto_increment_offset          = 2                                 # For Master/Master set-ups use 2 for both nodes
log_timestamps = SYSTEM


# Add for GTID Mode
gtid-mode                      = on
enforce-gtid-consistency       = on
log-slave-updates

master_info_repository        = TABLE
relay_log_info_repository     = TABLE

# Group replication
# plugin-load = group_replication.so
group_replication_start_on_boot = ON
group_replication_bootstrap_group = OFF
# transaction-write-set-extraction=MURMUR32
transaction-write-set-extraction=XXHASH64
binlog-checksum=NONE
group_replication = FORCE_PLUS_PERMANENT
transaction-write-set-extraction = XXHASH64
group_replication_group_name= "00000000-1111-2222-3333-123456789ABC"
#group_replication_recovery_user='rpl_user'                             #commented out at upgrading to 5.7.14
#group_replication_recovery_password='rpl_pass'                         #commented out at upgrading to 5.7.14
group_replication_recovery_retry_count= 2
group_replication_recovery_reconnect_interval= 120
group_replication_local_address="192.168.56.101:6606"
#group_replication_peer_addresses= "192.168.56.101:18620,192.168.56.102:18620" #commented out at upgrading to 5.7.14
group_replication_group_seeds =  "192.168.56.101:6606,192.168.56.102:6606"

# 5.7.14
relay-log-recovery = ON
slave-parallel-type = LOGICAL_CLOCK
slave-preserve-commit-order = ON
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
slave-type-conversions = ALL_NON_LOSSY
sync-master-info = 1000
sync-relay-log = 1000
slave-parallel-workers = 0

■Boot Strap Nodeの起動

– It will not try and participate in any group communication when starting but will instead configure the group as consisting only of itself.
– Any subsequent member that attempts to join the group will sync itself up with the state of this instance.

We need to pick one member and declare that it is the bootstrap node by setting group_replication_bootstrap_group=ON
Just remember to turn group_replication_bootstrap_group=OFF again after the first member is up.

root@localhost [GR_TEST]> select @@version,@@hostname;
+-----------------------+------------+
| @@version             | @@hostname |
+-----------------------+------------+
| 5.7.14-labs-gr080-log | misc01     |
+-----------------------+------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | OFFLINE      |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> show variables like 'group_replication_bootstrap_group';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| group_replication_bootstrap_group | OFF   |
+-----------------------------------+-------+
1 row in set (0.03 sec)

root@localhost [GR_TEST]> SET GLOBAL group_replication_bootstrap_group= 1;
Query OK, 0 rows affected (0.00 sec)

root@localhost [GR_TEST]> show variables like 'group_replication_bootstrap_group';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| group_replication_bootstrap_group | ON    |
+-----------------------------------+-------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> START GROUP_REPLICATION;
Query OK, 0 rows affected (1.07 sec)

root@localhost [GR_TEST]> SET GLOBAL group_replication_bootstrap_group= 0;
Query OK, 0 rows affected (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> select * from performance_schema.replication_group_member_stats\G
*************************** 1. row ***************************
                      CHANNEL_NAME: group_replication_applier
                           VIEW_ID: 14732520384730018:1
                         MEMBER_ID: 29ea17bc-3848-11e6-9900-0800279ca844
       COUNT_TRANSACTIONS_IN_QUEUE: 0
        COUNT_TRANSACTIONS_CHECKED: 0
          COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
TRANSACTIONS_COMMITTED_ALL_MEMBERS: 
    LAST_CONFLICT_FREE_TRANSACTION: 
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 

■2番目のノード追加
Specify valid MySQL credentials that this node will use when requesting GTIDs from existing members of the
group (a seed or donor) necessary to perform an automated recovery (such as when joining the group, which we’ll do next):
CHANGE MASTER TO MASTER_USER=’rpl_user’, MASTER_PASSWORD=’rpl_pass’ FOR CHANNEL ‘group_replication_recovery’;
Have the new node join to become a member with: STOP GROUP_REPLICATION; START GROUP_REPLICATION; (STOP is necessary because we have start_on_boot enabled).

root@localhost [GR_TEST]>  select @@version,@@hostname;
+-----------------------+------------+
| @@version             | @@hostname |
+-----------------------+------------+
| 5.7.14-labs-gr080-log | misc02     |
+-----------------------+------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 5b07d5d8-4057-11e6-a315-0800279cea3c | misc02      |        3306 | OFFLINE      |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

root@localhost [GR_TEST]> STOP GROUP_REPLICATION;START GROUP_REPLICATION;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (7.04 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | ONLINE       |
| group_replication_applier | 5b07d5d8-4057-11e6-a315-0800279cea3c | misc02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> select * from performance_schema.replication_group_member_stats\G
*************************** 1. row ***************************
                      CHANNEL_NAME: group_replication_applier
                           VIEW_ID: 14732520384730018:2
                         MEMBER_ID: 5b07d5d8-4057-11e6-a315-0800279cea3c
       COUNT_TRANSACTIONS_IN_QUEUE: 0
        COUNT_TRANSACTIONS_CHECKED: 0
          COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
TRANSACTIONS_COMMITTED_ALL_MEMBERS: 
    LAST_CONFLICT_FREE_TRANSACTION: 
1 row in set (0.00 sec)

root@localhost [GR_TEST]> select user_name, user_password from mysql.slave_master_info where channel_name = 'group_replication_recovery';
+-----------+---------------+
| user_name | user_password |
+-----------+---------------+
| rpl_user  | rpl_pass      |
+-----------+---------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 

■ 追加の確認と設定(NODE2を追加後にBootstrapノードで実行)

You should also now execute step 3 (the CHANGE MASTER TO) on the node we used to bootstrap the group, if you haven’t previously done so.

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | ONLINE       |
| group_replication_applier | 5b07d5d8-4057-11e6-a315-0800279cea3c | misc02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

root@localhost [GR_TEST]> select user_name, user_password from mysql.slave_master_info where channel_name = 'group_replication_recovery';
+-----------+---------------+
| user_name | user_password |
+-----------+---------------+
| rpl_user  | rpl_pass      |
+-----------+---------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 

以下、基本動作確認

■マルチマスターの基本動作確認 (NODE1とNODE2からINSERT)

NODE1)

root@localhost [GR_TEST]> desc T01;
+--------------+-------------+------+-----+-------------------+-------+
| Field        | Type        | Null | Key | Default           | Extra |
+--------------+-------------+------+-----+-------------------+-------+
| ID           | int(11)     | NO   | PRI | NULL              |       |
| MEMO         | varchar(30) | NO   |     |                   |       |
| created_time | datetime    | YES  |     | CURRENT_TIMESTAMP |       |
+--------------+-------------+------+-----+-------------------+-------+
3 rows in set (0.01 sec)

root@localhost [GR_TEST]> insert into T01(ID,MEMO) values(1,@@hostname);
Query OK, 1 row affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
+----+--------+---------------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE2)

root@localhost [GR_TEST]> select * from T01;
Empty set (0.00 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
+----+--------+---------------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> insert into T01(ID,MEMO) values(2,@@hostname);
Query OK, 1 row affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
+----+--------+---------------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]>

blog2

■トランザクションの確認
NODE1)

root@localhost [GR_TEST]> start transaction;insert into T01(ID,MEMO) values(3,@@hostname);
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

root@localhost [GR_TEST]> commit;
Query OK, 0 rows affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE2) 上記を実行中に同じテーブルに対してINSERTは問題ない。

root@localhost [GR_TEST]> start transaction;insert into T01(ID,MEMO) values(4,@@hostname);
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
3 rows in set (0.00 sec)

root@localhost [GR_TEST]> commit;
Query OK, 0 rows affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

■ 同じ行を更新してみる(where ID=4)
NODE1)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> start transaction;update T01 set MEMO = 'MISC02' where ID = 4;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | MISC02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> commit;
Query OK, 0 rows affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | MISC02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> start transaction;update T01 set MEMO = 'misc02' where ID = 4;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [GR_TEST]> commit;
Query OK, 0 rows affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE2) NODE1のトランザクション中に実行。
※トランザクション発行の順番では無く、先にcommitした方が優先される。

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> start transaction;update T01 set MEMO = 'MISC01' where ID = 4;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | MISC01 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> select * from T01;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
root@localhost [GR_TEST]> commit;
Query OK, 0 rows affected (0.00 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | MISC02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | MISC02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> start transaction;update T01 set MEMO = 'MISC01' where ID = 4;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [GR_TEST]> commit;
ERROR 1180 (HY000): Got error 149 during COMMIT
root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

Comments are closed.

Post Navigation