當前位置：首頁 > IT技術 > 數(shù)據(jù)庫 > 正文

Rocky之Mysql-MGR高可用實戰(zhàn)案例
2022-09-06 22:46:33

1、環(huán)境準備

node1  rocky8.6  10.0.0.8
node2  rocky8.6  10.0.0.18
node3  rocky8.6  10.0.0.28

2、所有節(jié)點更改主機名和名稱解析

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、所有節(jié)點修改配置文件

[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、在所有主機創(chuàng)建復制用戶 并安裝插件
[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、啟用第一個節(jié)點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> 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、啟用剩余的所有節(jié)點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、在所有節(jié)點都可以查看到信息：

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、測試

# 主節(jié)點新增數(shù)據(jù)庫，其他節(jié)點查看
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();  #任意節(jié)點都可執(zhí)行
+--------------------------------------------------+
| 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;   #查看切換狀態(tài)
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| 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();   #任意節(jié)點都可執(zhí)行

本文摘自 ：https://blog.51cto.com/d

開通會員，享受整站包年服務立即開通 >
推薦內(nèi)容