mgr-安装部署（多主）

操作系统：centos7

mysql版本：mysql5.7.27

主机名	IP地址	角色
mgr1	10.10.20.92	primary
mgr2	10.10.20.104	second
mgr3	10.10.20.88	second

1、准备工作

关闭防火墙，修改SELinux为disabled，添加/etc/hosts(三台均执行)

service iptables stop

chkconfig iptables off

setenforce 0

echo -e "10.10.20.92  mgr1\n10.10.20.104  mgr2\n10.10.20.88  mgr3">>/etc/hosts

2、安装MySQL数据库（这里使用源码安装，不做介绍）

3、配置my.cnf

以主为例，两个副本稍作修改

mgr1 my.cnf配置

[client]
port=3308
socket=/tmp/mysql.sock
default-character-set=utf8

[mysql]
no-auto-rehash
default-character-set=utf8

[mysqld]
port=3306
character-set-server=utf8
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
datadir=/data/mysql
pid-file =/data/mysql/mysql.pid
bind-address = 0.0.0.0
#explicit_defaults_for_timestamp=true
#lower_case_table_names=1
#back_log=103
max_connections=3000
max_connect_errors=100000
table_open_cache=512
external-locking=FALSE
max_allowed_packet=32M
sort_buffer_size=2M
join_buffer_size=2M
thread_cache_size=51
query_cache_size=32M
#query_cache_limit=4M
transaction_isolation=REPEATABLE-READ
tmp_table_size=96M
max_heap_table_size=96M

###***slowqueryparameters
long_query_time=1
slow_query_log = 1
slow_query_log_file=/data/slowlog/slow.log

###***binlogparameters
log-bin=mysql-bin
binlog_cache_size=4M
max_binlog_cache_size=4096M
max_binlog_size=1024M
binlog_format=row
expire_logs_days=7

###***relay-logparameters
#relay-log=/data/3307/relay-bin
#relay-log-info-file=/data/3307/relay-log.info
#master-info-repository=table
#relay-log-info-repository=table
#relay-log-recovery=1
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = "e319cdf3-9b01-40c3-b390-d7852c81741f"
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = "10.10.20.92:33061"
loose-group_replication_group_seeds ="10.10.20.92:33061,10.10.20.104:33062,10.10.20.88:33063"
loose-group_replication_bootstrap_group = off
loose-group_replication_single_primary_mode = FALSE
loose-group_replication_enforce_update_everywhere_checks = TRUE

plugin-load=group_replication.so

#***MyISAMparameters
key_buffer_size=16M
read_buffer_size=1M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=1M
#skip-name-resolve

###***master-slavereplicationparameters
server-id=92
#slave-skip-errors=all

#***Innodbstorageengineparameters
innodb_buffer_pool_size=730M
innodb_data_file_path=ibdata1:10M:autoextend
#innodb_file_io_threads=8
innodb_thread_concurrency=16
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_log_file_size=512M
innodb_log_files_in_group=2
innodb_max_dirty_pages_pct=75
innodb_buffer_pool_dump_pct=50
innodb_lock_wait_timeout=50
innodb_file_per_table=on

[mysqldump]
quick
max_allowed_packet=32M

[myisamchk]
key_buffer=16M
sort_buffer_size=16M
read_buffer=8M
write_buffer=8M

[mysqld_safe]
open-files-limit=8192
log-error=/data/mysql/error.log
pid-file=/data/mysql/mysqld.pid

View Code

注意每个节点的server_id、loose-group_replication_local_address、loose-group_replication_group_seeds都配置成自己的相印的参数

组复制部分，配置文件介绍：
group_replication变量使用的loose-前缀是指示Server启用时尚未加载复制插件也将继续启动

##指示Server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列
transaction_write_set_extraction = XXHASH64

##表示将加入或者创建的复制组命名为01e5fb97-be64-41f7-bafd-3afc7a6ab555
##可自定义(通过cat /proc/sys/kernel/random/uuid)
loose-group_replication_group_name="01e5fb97-be64-41f7-bafd-3afc7a6ab555"

##设置为Server启动时不自动启动组复制
loose-group_replication_start_on_boot=off 

##绑定本地的192.168.29.128及33061端口接受其他组成员的连接，IP地址必须为其他组成员可正常访问
loose-group_replication_local_address="192.168.29.128:33061" 

##本行为告诉服务器当服务器加入组时，应当连接到10.10.20.92:33061,10.10.20.104:33062,10.10.20.88:33063
##这些种子服务器进行配置。本设置可以不是全部的组成员服务地址。
loose-group_replication_group_seeds ="10.10.20.92:33061,10.10.20.104:33062,10.10.20.88:33063"

##配置是否自动引导组
loose-group_replication_bootstrap_group = off 

##配置白名单，默认情况下只允许192.168.29.128连接到复制组，如果是其他IP则需要配置。
loose-group_replication_ip_whitelist=”10.30.0.0/16,10.31.0..0/16,10.27.0.0/16″

##
loose-group_replication_single_primary_mode=FALSE

##
loose-group_replication_enforce_update_everywhere_checks= TRUE

4、创建复制环境

在mgr1/mgr2/mgr3上建立复制账号：

set sql_log_bin=0;  
grant replication slave,replication client on *.* to repl@'localhost' identified by 'repl';  
grant replication slave,replication client on *.* to repl@'127.0.0.1' identified by 'repl';  
grant replication slave,replication client on *.* to repl@'10.10.20.%' identified by 'repl';  
SET SQL_LOG_BIN=1;
注：如果为三台独立节点，需要将localhost、127.0.0.1和远程主机域都授权用户

5、安装group replication插件、开启分布式复制

安装插件：

在mgr1/mgr2/mgr3上依次安装group replication插件

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';  

6、启动mgr集群

开始构建group replication集群，通常操作命令
在mgr1、mgr2、mgr3上依次执行
mysql>  CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';  
Query OK, 0 rows affected, 2 warnings (0.02 sec)  
 
mgr1上建立基本主库master库：
# 设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器以这台服务器为基准（只要在mgr1上执行就可以了），以后加入的就不需要设置。  
mysql> SET GLOBAL group_replication_bootstrap_group = ON;  
mysql> START GROUP_REPLICATION;  
mysql> SET GLOBAL group_replication_bootstrap_group = OFF;
mysql> select * from performance_schema.replication_group_members; 
 
mgr2上启动group_replication： 
mgr2上mysql命令行上执行启动：
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
mysql> start group_replication;
mysql> select * from performance_schema.replication_group_members;

mgr3上启动group_replication：
mgr3命令行上执行：  
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;  
mysql> start group_replication;  
   
-- 再去master库mgr1上，查看group_replication成员，会有mgr3的显示，而且已经是ONLINE了  
mysql> select * from performance_schema.replication_group_members;

最后查看集群状态，都为ONLINE就表示OK：
mysql> select * from performance_schema.replication_group_members;  

7、验证集群功能

在mgr1上建立测试库mgrtest，测试表t1，录入一条数据
mysql> create database mgrtest;  
Query OK, 1 row affected (0.00 sec)  
   
mysql> create table mgrtest.test(id int,cn varchar(32));  
Query OK, 0 rows affected (0.02 sec)  
   
mysql> insert into mgrtest.test(id,cn)values(1,'nihao');  
ERROR 3098 (HY000): The table does notcomply with the requirements by an external plugin.   
-- # 这里原因是group_replaction环境下面，表必须有主键不然不允许往里insert值。所以修改表t1，将id字段设置程主键即可。  
mysql> alter table test add primary key(id); 
mysql> insert into test(id,cn)values(1,'a');    
 
去mgr2/mgr3上可以看到数据已经同步过去
mysql> select * from mgr1.t1;  
+----+------+  
| id | cn  |  
+----+------+  
|  1| a    |  
+----+------+  
1 row in set (0.00 sec)  
 
然后在mgr2/mgr3上执行inert操作，也能进行插入，在另外两个库中也能看到信息同步。
若为单主模式，在mgr2/mgr3 insert则会拒绝，因为mgr2、mgr3为readonly
mysql> insert into test select 2,'b';  
ERROR 1290 (HY000): The MySQL server isrunning with the --super-read-only option so it cannot execute this statement 

8、无感知切换

手动关闭mgr1的MySQL库，在mgr2/mgr3日志中显示mgr1被移除
2019-08-21T10:07:38.545165+08:00 0 [Warning] Plugin group_replication reported: 'Members removed from the group: mgr1:3306'
2019-08-21T10:07:38.545263+08:00 0 [Note] Plugin group_replication reported: 'Group membership changed to mgr3:3306, mgr2:3306 on view 15663502168288818:4.'
在mgr2/mgr3插入数据，可正常插入数据。
mysql> insert into t1(id,cn)values(3,'移除');
Query OK, 1 row affected (0.00 sec)

重启启动mgr1,恢复mgr1进入集群

重新启动mgr1的MySQL，启动group_replication，可在mgr2/mgr3日志中显示mgr1加入进集群了
mysql> select * from performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+-----------+-------------+-------------+--------------+
| group_replication_applier |           |             |        NULL | OFFLINE      |
+---------------------------+-----------+-------------+-------------+--------------+
1 row in set (0.00 sec)

mysql> start group_replication;
Query OK, 0 rows affected (3.33 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 8de79773-c2ef-11e9-bf1c-000c2922d948 | mgr3        |        3306 | ONLINE       |
| group_replication_applier | bce22b67-c263-11e9-a33f-000c29090bae | mgr1        |        3306 | ONLINE       |
| group_replication_applier | fb127250-c330-11e9-81c1-000c29e38dc6 | mgr2        |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
mysql> insert into shish.t1(id,cn)values(4,'mgr1上插入');
Query OK, 1 row affected (0.00 sec)
mgr2/mgr3日志：
2019-08-22T10:18:35.745545+08:00 0 [Note] Plugin group_replication reported: 'Members joined the group: mgr1:3306'
2019-08-22T10:18:35.745599+08:00 0 [Note] Plugin group_replication reported: 'Group membership changed to mgr3:3306, mgr1:3306, mgr2:3306 on view 15663502168288818:5.'
2019-08-22T10:18:35.798121+08:00 84 [Note] Start binlog_dump to master_thread_id(84) slave_server(92), pos(, 4)
2019-08-22T10:18:35.844594+08:00 0 [Note] Plugin group_replication reported: 'The member with address mgr1:3306 was declared online within the replication group'

9、切到单主模式

# 所有节点执行
mysql> stop group_replication;
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
mysql> set global group_replication_single_primary_mode=ON;

# 主节点（选定主节点，这里在mgr2上执行）执行
SET GLOBAL group_replication_bootstrap_group=ON; 
START GROUP_REPLICATION; 
SET GLOBAL group_replication_bootstrap_group=OFF;
# 从节点（mgr1、mgr3）执行
START GROUP_REPLICATION; 

# 查看MGR组信息
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 8de79773-c2ef-11e9-bf1c-000c2922d948 | mgr3        |        3306 | ONLINE       |
| group_replication_applier | bce22b67-c263-11e9-a33f-000c29090bae | mgr1        |        3306 | ONLINE       |
| group_replication_applier | fb127250-c330-11e9-81c1-000c29e38dc6 | mgr2        |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

10、切回多主模式

MGR切换模式需要重新启动组复制，因些需要在所有节点上先关闭组复制，设置 group_replication_single_primary_mode=OFF 等参数，再启动组复制。

# 停止组复制(所有节点执行)：
mysql> stop group_replication;
mysql> set global group_replication_single_primary_mode=OFF;
mysql> set global group_replication_enforce_update_everywhere_checks=ON;

# 随便选择某个节点执行
mysql> SET GLOBAL group_replication_bootstrap_group=ON; 
mysql> START GROUP_REPLICATION; 
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

# 其他节点执行
mysql> START GROUP_REPLICATION; 

# 查看组信息，所有节点的 MEMBER_ROLE 都为 PRIMARY
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 8de79773-c2ef-11e9-bf1c-000c2922d948 | mgr3        |        3306 | ONLINE       |
| group_replication_applier | bce22b67-c263-11e9-a33f-000c29090bae | mgr1        |        3306 | ONLINE       |
| group_replication_applier | fb127250-c330-11e9-81c1-000c29e38dc6 | mgr2        |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

11、问题记录

问题一：由于安装时删除了mysql.session用户，启动group_replication报如下错误
[ERROR] Plugin group_replication reported: 'There was an error when trying to access the server with user: mysql.session. Make sure the user is present in the server and that mysql_upgrade was run after a server update.'
[Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member'
[ERROR] Plugin group_replication reported: 'Error calling group communication interfaces while trying to leave the group'
解决：从未删除的库中新增mysql.session一条记录
官网介绍：
配置并启动服务器s1后，安装组复制插件。如果您plugin_load_add='group_replication.so在选项文件中使用 ，则会安装组复制插件，您可以继续执行下一步。如果您决定手动安装插件，请连接到服务器并发出以下命令：
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql.session在加载组复制之前 ，用户必须存在。 mysql.session在MySQL 5.7.19版中添加了。如果使用早期版本初始化数据字典，则必须执行MySQL升级过程（请参见第2.11节“升级MySQL”）。
如果未运行升级，则组复制无法以错误消息启动 尝试使用用户访问服务器时出现错误：mysql.session@localhost。确保用户在服务器中，并且在服务器更新后运行了mysql_upgrade。

问题二：安装过程中碰到如下所示，在mysql-bin.000014上无法恢复，由于是新安装，在mgr1/mgr2/mgr3三台数据库上分别执行reset master，重新启动，问题解决
[Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. 
Previous state master_host='mgr1', 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=''.
[Note] Plugin group_replication reported: 'Retrying group recovery connection with another donor. Attempt 10/10'
[Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. 
Previous state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='mgr1', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
[Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor bce22b67-c263-11e9-a33f-000c29090bae at mgr1 port: 3306.'
[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.
[Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'repl@mgr1:3306',replication started in log 'FIRST' at position 4
[Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './mgr2-relay-bin-group_replication_recovery.000001' position: 4
[ERROR] Slave SQL for channel 'group_replication_recovery': Could not execute Write_rows event on table mysql.user; Duplicate entry 'localhost-mysql.session' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; 
                the event's master log mysql-bin.000014, end_log_pos 834, Error_code: 1062
[Warning] Slave: Duplicate entry 'localhost-mysql.session' for key 'PRIMARY' Error_code: 1062
[ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000014' position 190.
[Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'
[Note] Slave I/O thread killed while reading event for channel 'group_replication_recovery'
[Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'mysql-bin.000014', position 3205
[Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='mgr1', 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=''.
[ERROR] Plugin group_replication reported: 'Maximum number of retries when trying to connect to a donor reached. Aborting group replication recovery.'
[Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'
[Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='<NULL>', master_port= 0, 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=''.
[ERROR] Plugin group_replication reported: 'Fatal error during the Recovery process of Group Replication. The server will leave the group.'
[Note] Plugin group_replication reported: 'Going to wait for view modification'
[Note] Plugin group_replication reported: 'Group membership changed: This member has left the group.'
解决：
由于是新搭建，在三个库中均同时执行reset master;

 其它错误情况，我没碰到，直接转链接：

https://www.sohu.com/a/126994738_505857

转载于:https://www.cnblogs.com/shuihuaboke/p/11383904.html