MGR高可用实战案例
1、环境准备
node1 rocky8.6 10.0.0.8
node2 rocky8.6 10.0.0.18
node3 rocky8.6 10.0.0.28
2、所有节点更改主机名和名称解析
hostnamectl set-hostname node1.wang.org
cat >> /etc/hosts <<EOF
10.0.0.8 node1.wang.org
10.0.0.18 node2.wang.org
10.0.0.28 node3.wang.org
EOF
yum -y install mysql-server
3、所有节点修改配置文件
[root@node1 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=8
gtid_mode=ON
enforce_gtid_consistency=ON
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
loose-group_replication_group_name="ef0143b7-94df-40ee-b798-95849678a271"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="10.0.0.8:24901"
loose-group_replication_group_seeds="10.0.0.8:24901,10.0.0.18:24901,10.0.0.28:24901"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_recovery_use_ssl=ON
[root@node2 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=18
gtid_mode=ON
enforce_gtid_consistency=ON
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
loose-group_replication_group_name="ef0143b7-94df-40ee-b798-95849678a271"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="10.0.0.18:24901"
loose-group_replication_group_seeds="10.0.0.8:24901,10.0.0.18:24901,10.0.0.28:24901"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_recovery_use_ssl=ON
[root@node3 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=28
gtid_mode=ON
enforce_gtid_consistency=ON
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
loose-group_replication_group_name="ef0143b7-94df-40ee-b798-95849678a271"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="10.0.0.28:24901"
loose-group_replication_group_seeds="10.0.0.8:24901,10.0.0.18:24901,10.0.0.28:24901"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_recovery_use_ssl=ON
4、在所有主机创建复制用户 并安装插件
[root@node1 ~]# mysql
mysql> set sql_log_bin=0;
mysql> create user repluser@'%' identified by '123456'
mysql> grant replication slave on *.* to repluser@'%';
mysql> flush privileges;
mysql> set sql_log_bin=1;
mysql> install plugin group_replication soname 'group_replication.so';
mysql> select * from information_schema.plugins where plugin_name='group_replication'/G
*************************** 1. row ***************************
PLUGIN_NAME: group_replication
PLUGIN_VERSION: 1.1
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: GROUP REPLICATION
PLUGIN_TYPE_VERSION: 1.4
PLUGIN_LIBRARY: group_replication.so
PLUGIN_LIBRARY_VERSION: 1.10
PLUGIN_AUTHOR: Oracle Corporation
PLUGIN_DESCRIPTION: Group Replication (1.1.0)
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
# node2、node3步骤同node1
5、启用第一个节点Primary （引导启动）
[root@node1 ~]# mysql
mysql> set global group_replication_bootstrap_group=ON;
mysql> start group_replication;
mysql> set global group_replication_bootstrap_group=OFF;
mysql> mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 81df7810-2d21-11ed-a76f-000c2942b4d3 | node1.wang.org | 3306 | ONLINE | PRIMARY | 8.0.26 |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)
6、启用剩余的所有节点Secondary
[root@node2 ~]# mysql
mysql> change master to master_user='repluser',master_password='123456' for channel 'group_replication_recovery';
mysql> start group_replication;
[root@node3 ~]# mysql
mysql> change master to master_user='repluser',master_password='123456' for channel 'group_replication_recovery';
mysql> start group_replication;
6-1、在所有节点都可以查看到信息：
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 81df7810-2d21-11ed-a76f-000c2942b4d3 | node1.wang.org | 3306 | ONLINE | PRIMARY | 8.0.26 |
| group_replication_applier | a4f0d915-2d22-11ed-a541-000c29720654 | node2.wang.org | 3306 | ONLINE | SECONDARY | 8.0.26 |
| group_replication_applier | a8259348-2d22-11ed-8440-000c295d78d1 | node3.wang.org | 3306 | ONLINE | SECONDARY | 8.0.26 |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
6-2、测试
# 主节点新增数据库，其他节点查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
[root@node1 ~]# systemctl stop mysqld.service #停掉一个服务
[root@node2 ~]# mysql
mysql> select * from performance_schema.replication_group_members; #可以看到主切换到了node2上
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | a4f0d915-2d22-11ed-a541-000c29720654 | node2.wang.org | 3306 | ONLINE | PRIMARY | 8.0.26 |
| group_replication_applier | a8259348-2d22-11ed-8440-000c295d78d1 | node3.wang.org | 3306 | ONLINE | SECONDARY | 8.0.26 |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
[root@node1 ~]# systemctl start mysqld.service #再恢复服务
[root@node1 ~]# mysql
mysql> change master to master_user='repluser',master_password='123456' for channel 'group_replication_recovery';
mysql> start group_replication;
mysql> mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 81df7810-2d21-11ed-a76f-000c2942b4d3 | node1.wang.org | 3306 | ONLINE | SECONDARY | 8.0.26 |
| group_replication_applier | a4f0d915-2d22-11ed-a541-000c29720654 | node2.wang.org | 3306 | ONLINE | PRIMARY | 8.0.26 |
| group_replication_applier | a8259348-2d22-11ed-8440-000c295d78d1 | node3.wang.org | 3306 | ONLINE | SECONDARY | 8.0.26 |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
7、单主切换多主模式
mysql> select group_replication_switch_to_multi_primary_mode(); #任意节点都可执行
+--------------------------------------------------+
| group_replication_switch_to_multi_primary_mode() |
+--------------------------------------------------+
| Mode switched to multi-primary successfully. |
+--------------------------------------------------+
1 row in set (1.02 sec)
mysql> select * from performance_schema.replication_group_members; #查看切换状态
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 81df7810-2d21-11ed-a76f-000c2942b4d3 | node1.wang.org | 3306 | ONLINE | PRIMARY | 8.0.26 |
| group_replication_applier | a4f0d915-2d22-11ed-a541-000c29720654 | node2.wang.org | 3306 | ONLINE | PRIMARY | 8.0.26 |
| group_replication_applier | a8259348-2d22-11ed-8440-000c295d78d1 | node3.wang.org | 3306 | ONLINE | PRIMARY | 8.0.26 |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF |
| super_read_only | OFF |
| transaction_read_only | OFF |
+-----------------------+-------+
4 rows in set (0.07 sec)
8、多主模式切换单主模式
mysql> select group_replication_switch_to_single_primary_mode(); #任意节点都可执行
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/288038.html