Mysql MGR高可用部署

        mysql传统的主从复制模式主要有：异步和半同步复制，但是他们都有些缺陷，如数据一致性、运维成本高等问题，往往我们在设计高可用架构时，不能作为最优方案选择。mysql在5.7.17版本后，推出了组复制（mysql group replication,简称MGR）。

        MGR 技术在保证数据强一致性基础上，可自动进行故障检测、自动切换，具备防脑裂机制，可进行多节点写入等特点，是mysql高可用技术发展的重点方向。

安装环境：

      1、3台服务器操作系统为centos 7.5.1804,mysql版本为5.7.30。操作系统防火墙、内核参数、selinux、用户和组添加等省略，直接进行配置。

      2、配置各主机名和ip地址对应关系，所有节点需配置；

[root@rac3 data]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
#::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.170.17 rac1
192.168.170.18 rac2
192.168.170.19 rac3

      3、配置my.ini文件，3个节点除了server_id、loose-group_replication_local_address参数不一样外，其他的都可以保持一致。

节点1配置：

[mysqld]
datadir=/data1/mgr/data
basedir=/usr/local/mysql
port=3306
socket=/data1/mgr/mysql.sock
server_id=1
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
log_error=/data1/mgr/logs/error.log
log_bin=/data1/mgr/logs/mysql-bin
transaction_write_set_extraction=XXHASH64
###
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data1/mgr/logs/slow.log
###
loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.170.17:33060"
loose-group_replication_group_seeds="192.168.170.17:33060,192.168.170.18:33060,192.168.170.19:33060"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE

节点2配置：

[mysqld]
datadir=/data1/mgr/data
basedir=/usr/local/mysql
port=3306
socket=/data1/mgr/mysql.sock
server_id=2
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
log_error=/data1/mgr/logs/error.log
log_bin=/data1/mgr/logs/mysql-bin
transaction_write_set_extraction=XXHASH64
###
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data1/mgr/logs/slow.log
###
loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.170.18:33060"
loose-group_replication_group_seeds="192.168.170.17:33060,192.168.170.18:33060,192.168.170.19:33060"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE

节点3配置：

[mysqld]
datadir=/data1/mgr/data
basedir=/usr/local/mysql
port=3306
socket=/data1/mgr/mysql.sock
server_id=3
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
log_error=/data1/mgr/logs/error.log
log_bin=/data1/mgr/logs/mysql-bin
transaction_write_set_extraction=XXHASH64
###
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data1/mgr/logs/slow.log
###
loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.170.19:33060"
loose-group_replication_group_seeds="192.168.170.17:33060,192.168.170.18:33060,192.168.170.19:33060"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE

组复制参数说明：

transaction_write_set_extraction = XXHASH64
##每个事务写集合，并使用XXHASH64哈希算法将其编码为散列
loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
##复制组命名为aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee
loose-group_replication_start_on_boot=off 
##设置为Server启动时不自动启动组复制
loose-group_replication_local_address="192.168.170.17:33060" 
##绑定本机ip地址和端口，并接受其他组成员的连接
loose-group_replication_group_seeds="192.168.170.17:33060,192.168.170.18:33060,192.168.170.19:33060"
##mgr组的成员，当服务器加入组时，连接到192.168.170.17:33060,192.168.170.18:33060,192.168.170.19:33060
loose-group_replication_bootstrap_group = off 
##是否自动引导组

4、初始化myslq及启动，3个节点都需要初始化并启动myslq服务。

[root@rac1 ~]# /usr/local/mysql/bin/mysqld --initialize-insecure  --user=mysql  --datadir=/data1/mgr/data

[root@rac1 ~]# nohup /usr/local/mysql/bin/mysqld_safe &

5、加载group_replication插件、配置同步账户信息，以下操作3个节点都需要执行。

[root@rac1 ~]# /usr/local/mysql/bin/mysql -uroot -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[root@rac1 ~]# /usr/local/mysql/bin/mysql -uroot -p -S /data1/mgr/mysql.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> 
mysql> install plugin group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.11 sec)

mysql> 
mysql> 
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> create user repl@'%' identified by '123456';
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)

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

mysql> 

6、启动mgr服务，需要在3个节点都执行。

mysql> set global group_replication_bootstrap_group=on;
Query OK, 0 rows affected (0.00 sec)
mysql> start group_replication;
Query OK, 0 rows affected (2.73 sec)

mysql> set global group_replication_bootstrap_group=off;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 12ed1804-ff04-11eb-810c-0050568f8774 | rac1        |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 12ed1804-ff04-11eb-810c-0050568f8774 | rac1        |        3306 | ONLINE       |
| group_replication_applier | 789c2972-ff04-11eb-b4e1-0050568fa18f | rac2        |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 12ed1804-ff04-11eb-810c-0050568f8774 | rac1        |        3306 | ONLINE       |
| group_replication_applier | 789c2972-ff04-11eb-b4e1-0050568fa18f | rac2        |        3306 | ONLINE       |
| group_replication_applier | 9278af53-ff04-11eb-892d-0050568fd5f5 | rac3        |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

        分别在3个节点都启动group_replication服务，并查询replication_group_members表发现组员状态都运行在online，mgr3个节点搭建成功。

注意事项：

1、在创建同步用户时，先把log_bin关闭，创建完后在开启日志记录，否则在开启group_replication服务时会提示异常。

2、loose-group_replication_local_address端口不可被其他服务占用。