说MGR - 搭建单主三成员MGR

准备参数（三个实例做相应调整）

1.复制参数

server_id=1/2/3

gtid_mode=ON

enforce_gtid_consistency=ON

binlog_format=ROW

log_slave_updates=ON

binlog_checksum=NONE

log-bin=/home/mysql/mysql3308/log/bin

master_info_repository=TABLE

relay_log_info_repository=TABLE

2.MGR参数

plugin-load = "group_replication.so"

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

loose-group_replication_local_address= "10.0.2.5/6/7:33081"

loose-group_replication_group_seeds= "10.0.2.5:33081,10.0.2.6:33081,10.0.2.7:33081"

loose-group_replication_start_on_boot=OFF

loose-group_replication_bootstrap_group=OFF

loose-group_replication_single_primary_mode=ON

loose-group_replication_enforce_update_everywhere_checks=OFF

loose-group_replication_member_weight=50

loose-group_replication_recovery_retry_count=10

loose-group_replication_recovery_reconnect_interval=60

loose-group_replication_flow_control_period=1

loose-group_replication_flow_control_mode=DISABLED

loose-group_replication_unreachable_majority_timeout=10

loose-group_replication_exit_state_action=READ_ONLY

loose-group_replication_compression_threshold=1000000

loose-group_replication_transaction_size_limit=134217728

清除复制关系

[[e-mail]][(none)]> reset slave all;

Query OK, 0 rows affected (0.00 sec)

创建用户

[[e-mail]][(none)]> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';

Query OK, 0 rows affected (0.00 sec)

[[e-mail]][(none)]> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';

Query OK, 0 rows affected (0.00 sec)

[[e-mail]][(none)]> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';

Query OK, 0 rows affected, 2 warnings (0.29 sec)

安装插件

[[e-mail]][(none)]> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Query OK, 0 rows affected (0.01 sec)

清除GTIDs

[[e-mail]][(none)]> reset master;

Query OK, 0 rows affected (0.01 sec)

[[e-mail]][(none)]> show master status\G

*************************** 1. row ***************************

             File: bin.000001

         Position: 150

     Binlog_Do_DB:

 Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

初始化MGR第一个成员，即Primary节点。

[[e-mail]][(none)]> SET GLOBAL group_replication_bootstrap_group=ON;

Query OK, 0 rows affected (0.00 sec)

[[e-mail]][(none)]> START GROUP_REPLICATION;

Query OK, 0 rows affected (2.15 sec)

[[e-mail]][(none)]> SET GLOBAL group_replication_bootstrap_group=OFF;

Query OK, 0 rows affected (0.00 sec)

这时产生了第1个gtid，观察gtid的内容。

[[e-mail]][(none)]> show master status\G

*************************** 1. row ***************************

             File: bin.000001

         Position: 434

     Binlog_Do_DB:

 Binlog_Ignore_DB:

Executed_Gtid_Set: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1

1 row in set (0.00 sec)

[[e-mail]][(none)]> SHOW BINLOG EVENTS IN 'bin.000001';

+------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+

| Log_name   | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |

+------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+

| bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.25-log, Binlog ver: 4                             |

| bin.000001 | 123 | Previous_gtids |         1 |         150 |                                                                   |

| bin.000001 | 150 | Gtid           |         1 |         211 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1' |

| bin.000001 | 211 | Query          |         1 |         270 | BEGIN                                                             |

| bin.000001 | 270 | View_change    |         1 |         369 | view_id=15660928950555109:1                                       |

| bin.000001 | 369 | Query          |         1 |         434 | COMMIT                                                            |

+------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+

6 rows in set (0.00 sec)

查看复制通道group_replication_recovery的信息，无显示。

[[e-mail]][(none)]> show slave status\G

Empty set (0.00 sec)

[[e-mail]][(none)]> show slave status for channel 'group_replication_recovery'\G

Empty set (0.00 sec)

加入第二个成员，即Secondary节点。

清除复制关系、创建用户、安装插件、清除GTIDs，同MGR第一个成员。

[[e-mail]][(none)]> START GROUP_REPLICATION;

Query OK, 0 rows affected (3.17 sec)

这时产生了第2个gtid，发现gtid的内容，即View_change。

[[e-mail]][(none)]> show master status\G

*************************** 1. row ***************************

             File: bin.000001

         Position: 758

     Binlog_Do_DB:

 Binlog_Ignore_DB:

Executed_Gtid_Set: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-2

1 row in set (0.00 sec)

[[e-mail]][(none)]> SHOW BINLOG EVENTS IN 'bin.000001';

+------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+

| Log_name   | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |

+------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+

| bin.000001 |   4 | Format_desc    |         2 |         123 | Server ver: 5.7.25-log, Binlog ver: 4                             |

| bin.000001 | 123 | Previous_gtids |         2 |         150 |                                                                   |

| bin.000001 | 150 | Gtid           |         1 |         211 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1' |

| bin.000001 | 211 | Query          |         1 |         270 | BEGIN                                                             |

| bin.000001 | 270 | View_change    |         1 |         369 | view_id=15660944798315978:1                                       |

| bin.000001 | 369 | Query          |         1 |         434 | COMMIT                                                            |

| bin.000001 | 434 | Gtid           |         1 |         495 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2' |

| bin.000001 | 495 | Query          |         1 |         554 | BEGIN                                                             |

| bin.000001 | 554 | View_change    |         1 |         693 | view_id=15660944798315978:2                                       |

| bin.000001 | 693 | Query          |         1 |         758 | COMMIT                                                            |

+------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+

10 rows in set (0.00 sec)

查看复制通道group_replication_recovery的信息，如下。

[[e-mail]][(none)]> show slave status\G

Empty set (0.00 sec)

[[e-mail]][(none)]> show slave status for channel 'group_replication_recovery'\G

*************************** 1. row ***************************

               Slave_IO_State:

                  Master_Host: <NULL>

                  Master_User: rpl_user

                  Master_Port: 0

                Connect_Retry: 60

              Master_Log_File:

          Read_Master_Log_Pos: 4

               Relay_Log_File: relay-group_replication_recovery.000001

                Relay_Log_Pos: 4

        Relay_Master_Log_File:

             Slave_IO_Running: No

            Slave_SQL_Running: No

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 0

              Relay_Log_Space: 485

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 0

                  Master_UUID:

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State:

           Master_Retry_Count: 1

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-2

                Auto_Position: 1

         Replicate_Rewrite_DB:

                 Channel_Name: group_replication_recovery

           Master_TLS_Version:

1 row in set (0.00 sec)

加入第三个成员

同加入第二个成员。

通过上面的过程可见，三个成员的MGR最少要产生3个gtid，即向group加入一个成员，就产生1个gtid，其反应了拓扑的变化，可通过下面的SQL查看成员信息。

[[e-mail]][(none)]> SELECT `MEMBER_ID`, `MEMBER_HOST`, `MEMBER_PORT`, `MEMBER_STATE`, ((`VARIABLE_NAME` IS NOT NULL) AND (`VARIABLE_NAME` = 'group_replication_primary_member')) AS `MEMBER_IS_PRIMARY` FROM performance_schema.replication_group_members LEFT OUTER JOIN performance_schema.global_status ON (`replication_group_members`.`MEMBER_ID` = `global_status`.`VARIABLE_VALUE`) WHERE `MEMBER_STATE` = 'ONLINE';

+--------------------------------------+-------------+-------------+--------------+-------------------+

| MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_IS_PRIMARY |

+--------------------------------------+-------------+-------------+--------------+-------------------+

| 27a60549-a643-11e9-bc30-080027f22add | mysql01.com |        3308 | ONLINE       |                 1 |

| 77731d39-bc4c-11e9-956d-080027aa34ac | mysql03.com |        3308 | ONLINE       |                 0 |

| b48091b5-bc4c-11e9-84d6-0800270c3d91 | mysql02.com |        3308 | ONLINE       |                 0 |

+--------------------------------------+-------------+-------------+--------------+-------------------+

3 rows in set (0.00 sec)