MySQL group replication MGR 单主/多主模式搭建

忘是亡心i 2022-01-28 15:39 232阅读 0赞

**实例规划**  
在一台主机上初始化三个MySQL实例，端口分别为3306，3307，3308，以3306为写入主库，其余两个实例为read-only。  
以下演示过程为单主模式搭建，但多主模式操作步骤基本一致，只是比单主模式在my.cnf中多了以下两个参数：

loose-group_replication_single_primary_mode = off  #关闭单主模式的参数
    loose-group_replication_enforce_update_everywhere_checks = on #开启多主模式的参数

**1.配置文件**  
各实例配置文件如下

[root@master ~]# cat /etc/my.cnf1
    [mysql]
    user=root
    password=sam123
    socket=/usr/local/mysql/mysql1.sock
    port=3306
    
    [client]
    socket=/usr/local/mysql/mysql1.sock
    
    [mysqld]
    #soft setting#
    basedir=/usr/local/mysql
    pid-file=/usr/local/mysql/mysql1.pid
    socket=/usr/local/mysql/mysql1.sock
    log-error=/mysqlLog/logs1/error.log
    
    #server setting#
    server_id=128
    port=3306
    user=mysql
    datadir=/mysqlData/data1
    tmpdir=/mysqlData/tmp1
    default_storage_engine=innodb
    
    #undo setting#
    innodb_undo_directory=/mysqlData/undo1
    innodb_undo_tablespaces=2
    
    #binlog setting#
    log-bin=/mysqlLog/logs1/mysql-bin
    log-bin-index=/mysqlLog/logs1/mysql-bin.index
    
    #group replication
    gtid_mode=ON
    enforce_gtid_consistency=ON
    binlog_checksum=NONE
    
    log_slave_updates=ON
    binlog_format=ROW
    master_info_repository=TABLE
    relay_log_info_repository=TABLE
    
    transaction_write_set_extraction=XXHASH64
    loose-group_replication_group_name="29ff7ba4-7c68-11e9-8d6b-000c2999b048"
    loose-group_replication_start_on_boot=off
    loose-group_replication_local_address= "127.0.0.1:3406"
    loose-group_replication_group_seeds= "127.0.0.1:3406,127.0.0.1:3407,127.0.0.1:3408"
    loose-group_replication_bootstrap_group=off
    [root@master ~]# 
    [root@master ~]# 
    [root@master ~]# 
    [root@master ~]# cat /etc/my.cnf2 
    [mysql]
    user=root
    password=sam123
    socket=/usr/local/mysql/mysql2.sock
    port=3307
    
    [client]
    socket=/usr/local/mysql/mysql2.sock
    
    [mysqld]
    #soft setting#
    basedir=/usr/local/mysql
    pid-file=/usr/local/mysql/mysql2.pid
    socket=/usr/local/mysql/mysql2.sock
    log-error=/mysqlLog/logs2/error.log
    
    #server setting#
    server_id=129
    port=3307
    user=mysql
    datadir=/mysqlData/data2
    tmpdir=/mysqlData/tmp2
    default_storage_engine=innodb
    
    #undo setting#
    innodb_undo_directory=/mysqlData/undo2
    innodb_undo_tablespaces=2
    
    #binlog setting#
    log-bin=/mysqlLog/logs2/mysql-bin
    log-bin-index=/mysqlLog/logs2/mysql-bin.index
    
    #group replication
    gtid_mode=ON
    enforce_gtid_consistency=ON
    binlog_checksum=NONE
    
    log_slave_updates=ON
    binlog_format=ROW
    master_info_repository=TABLE
    relay_log_info_repository=TABLE
    
    transaction_write_set_extraction=XXHASH64
    loose-group_replication_group_name="29ff7ba4-7c68-11e9-8d6b-000c2999b048"
    loose-group_replication_start_on_boot=off
    loose-group_replication_local_address= "127.0.0.1:3407"
    loose-group_replication_group_seeds= "127.0.0.1:3406,127.0.0.1:3407,127.0.0.1:3408"
    loose-group_replication_bootstrap_group=off
    [root@master ~]# 
    [root@master ~]# 
    [root@master ~]# 
    [root@master ~]# cat /etc/my.cnf3
    [mysql]
    user=root
    password=sam123
    socket=/usr/local/mysql/mysql3.sock
    port=3308
    
    [client]
    socket=/usr/local/mysql/mysql3.sock
    
    [mysqld]
    #soft setting#
    basedir=/usr/local/mysql
    pid-file=/usr/local/mysql/mysql3.pid
    socket=/usr/local/mysql/mysql3.sock
    log-error=/mysqlLog/logs3/error.log
    
    #server setting#
    server_id=130
    port=3308
    user=mysql
    datadir=/mysqlData/data3
    tmpdir=/mysqlData/tmp3
    default_storage_engine=innodb
    
    #undo setting#
    innodb_undo_directory=/mysqlData/undo3
    innodb_undo_tablespaces=2
    
    #binlog setting#
    log-bin=/mysqlLog/logs3/mysql-bin
    log-bin-index=/mysqlLog/logs3/mysql-bin.index
    
    #group replication
    gtid_mode=ON
    enforce_gtid_consistency=ON
    binlog_checksum=NONE
    
    log_slave_updates=ON
    binlog_format=ROW
    master_info_repository=TABLE
    relay_log_info_repository=TABLE
    
    transaction_write_set_extraction=XXHASH64
    loose-group_replication_group_name="29ff7ba4-7c68-11e9-8d6b-000c2999b048"
    loose-group_replication_start_on_boot=off
    loose-group_replication_local_address= "127.0.0.1:3408"
    loose-group_replication_group_seeds= "127.0.0.1:3406,127.0.0.1:3407,127.0.0.1:3408"
    loose-group_replication_bootstrap_group=off

参数解释：  
开启MGR，必须包含以下参数：

server_id=128
    gtid_mode=ON
    enforce_gtid_consistency=ON
    master_info_repository=TABLE
    relay_log_info_repository=TABLE
    binlog_checksum=NONE
    log_slave_updates=ON
    log_bin=binlog
    binlog_format=ROW

`transaction_write_set_extraction`设置write\_set的哈希算法，write\_set功能用于MGR实例之间的数据检验；  
`loose-group_replication_group_name`定义一个group name，必须是uuid的格式，可以自定义；  
`loose-group_replication_start_on_boot=OFF`禁止实例启动时自动开启组复制；  
`loose-group_replication_local_address`配置当前实例的IP和组复制内部交流的端口，各组员之间的端口要区别开来；  
`loose-group_replication_group_seeds`设置种子成员，即将来组成员各自的IP和组复制内部交流的端口，包括本实例；  
`loose-group_replication_bootstrap_group=OFF`关闭引导组复制；

**2.实例初始化**  
mysqld --defaults-file=/etc/my.cnf1 --initialize --user=mysql  
mysqld --defaults-file=/etc/my.cnf2 --initialize --user=mysql  
mysqld --defaults-file=/etc/my.cnf3 --initialize --user=mysql  
mysqld\_safe --defaults-file=/etc/my.cnf1 --user=mysql &  
mysqld\_safe --defaults-file=/etc/my.cnf2 --user=mysql &  
mysqld\_safe --defaults-file=/etc/my.cnf3 --user=mysql &

**3.登录3306实例，创建复制用户**

[root@master ~]# mysql -uroot -psam123 -S /usr/local/mysql/mysql1.sock
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3
    Server version: 5.7.23-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    
    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.
    
    mysql> set sql_log_bin=0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> create user repl@'%' identified by 'sam123';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> grant replication slave on *.* to repl@'%';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set sql_log_bin=1;
    Query OK, 0 rows affected (0.00 sec)

其中设置sql\_log\_bin=0是为了不记录创建用户的binlog，以免在另外两个实例创建相同用户后，开启MGR时发生数据冲突。

**4.3306实例配置MGR通道**

mysql> change master to master_user='repl',master_password='sam123' for channel 'group_replication_recovery';
    Query OK, 0 rows affected, 2 warnings (0.10 sec)

**5.3306实例启动MGR**

后台日志如下：

2019-05-24T02:23:09.479280Z 24 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
    2019-05-24T02:23:09.479313Z 24 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7'
    2019-05-24T02:23:09.479324Z 24 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 128'
    2019-05-24T02:23:09.479447Z 30 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './master-relay-bin-group_replication_applier.000001' position: 4
    2019-05-24T02:23:09.481624Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3'
    2019-05-24T02:23:09.481662Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 3406'
    2019-05-24T02:23:10.493184Z 0 [Note] Plugin group_replication reported: 'Group membership changed to master:3306 on view 15586645904912986:1.'
    2019-05-24T02:23:10.493413Z 33 [Note] Plugin group_replication reported: 'Only one server alive. Declaring this server as online within the replication group'
    2019-05-24T02:23:10.497326Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'
    2019-05-24T02:23:10.497670Z 0 [Note] Plugin group_replication reported: 'A new primary with address master:3306 was elected, enabling conflict detection until the new primary applies all relay logs.'
    2019-05-24T02:23:10.497771Z 35 [Note] Plugin group_replication reported: 'This server is working as primary member.'

**6.3306实例创建测试数据**

mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.06 sec)
    
    mysql> create database sam;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> use sam;
    Database changed
    mysql> create table test(id int primary key,name varchar(10));
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> insert into test values (1,'sam');
    Query OK, 1 row affected (0.04 sec)
    
    mysql> select * from test;
    +----+------+
    | id | name |
    +----+------+
    |  1 | sam  |
    +----+------+
    1 row in set (0.00 sec)

**7.登录3307实例，创建复制用户**

[root@master ~]# mysql -uroot -psam123 -S /usr/local/mysql/mysql2.sock
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3
    Server version: 5.7.23-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    
    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.
    
    mysql> set sql_log_bin=0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> create user repl@'%' identified by 'sam123';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> grant replication slave on *.* to repl@'%';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set sql_log_bin=1;
    Query OK, 0 rows affected (0.00 sec)

**8.3307实例配置MGR通道**

mysql> change master to master_user='repl',master_password='sam123' for channel 'group_replication_recovery';
    Query OK, 0 rows affected, 2 warnings (0.03 sec)

**9.3307实例启动MGR**

mysql> start group_replication;
    ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

查看后台报错如下：

2019-05-24T02:10:59.062381Z 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 63f6b8a5-7dc6-11e9-962c-000c2999b048:1 > Group transactions: 29ff7ba4-7c68-11e9-8d6b-000c2999b048:1-4,
    46b4c1d0-7dc6-11e9-92e8-000c2999b048:1'
    2019-05-24T02:10:59.062438Z 0 [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'
    2019-05-24T02:10:59.062447Z 0 [Note] Plugin group_replication reported: 'To force this member into the group you can use the group_replication_allow_local_disjoint_gtids_join option'

`63f6b8a5-7dc6-11e9-962c-000c2999b048:1`是3307实例自身的GTID，由于3306实例没有这个GTID信息，所以MGR认为3307的GTID是多余的，不允许加入。  
解决办法有两个：  
1.在3307实例执行reset master清空GTID信息；  
2.按照提示设置\`\`\`set global group\_replication\_allow\_local\_disjoint\_gtids\_join=on’，本文采用这种方法。

可以看到sam.test已经同步过来了，3307实例启动MGR时后台日志如下：

2019-05-24T02:25:49.773022Z 32 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor 46b4c1d0-7dc6-11e9-92e8-000c2999b048 at master port: 3306.'
    2019-05-24T02:25:49.785211Z 34 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
    2019-05-24T02:25:49.796519Z 35 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './master-relay-bin-group_replication_recovery.000001' position: 4
    2019-05-24T02:25:49.797166Z 34 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'repl@master:3306',replication started in log 'FIRST' at position 4
    2019-05-24T02:25:49.859004Z 32 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'
    2019-05-24T02:25:49.859075Z 35 [Note] Slave SQL thread for channel 'group_replication_recovery' exiting, replication stopped in log 'mysql-bin.000001' at position 1020
    2019-05-24T02:25:49.861097Z 34 [Note] Slave I/O thread killed while reading event for channel 'group_replication_recovery'
    2019-05-24T02:25:49.861123Z 34 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'mysql-bin.000001', position 1476
    2019-05-24T02:25:49.879102Z 32 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='master', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
    2019-05-24T02:25:49.894582Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'

**10.登录3308实例，创建复制用户**

[root@master ~]# mysql -uroot -psam123 -S /usr/local/mysql/mysql3.sock
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3
    Server version: 5.7.23-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    
    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.
    
    mysql> set sql_log_bin=0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> create user repl@'%' identified by 'sam123';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> grant replication slave on *.* to repl@'%';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> set sql_log_bin=1;
    Query OK, 0 rows affected (0.00 sec)

**11.3308实例配置MGR通道**

mysql> change master to master_user='repl',master_password='sam123' for channel 'group_replication_recovery';
    Query OK, 0 rows affected, 2 warnings (0.04 sec)

**12.3308实例启动MGR**

可以看到，3306实例的sam.test已经同步过来了。