Rocky之Mysql-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> 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();   #任意節點都可執行

Rocky之Mysql-MGR高可用實戰案例

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案