目錄
一、建立3臺MySQL環境
二、修改MySQL引數
三、重啟MySQL環境
四、安裝MGR外掛（所有節點執行）
五、設定複製賬號（所有節點執行）
六、啟動MGR單主模式
  6.1、啟動MGR，在主庫(172.72.0.15)上執行
  6.2、其他節點加入MGR，在從庫（172.72.0.16，172.72.0.17）上執行
七、多主和單主模式切換
  7.1、查詢當前模式
  7.2、函式實現多主和單主切換
    7.2.1、單主切多主模式
    7.2.2、多主切單主模式
  7.3、手動切換
    7.3.1、單主切多主模式
    7.3.2、多主切單主模式
八、測試同步
九、MGR新增節點
  9.1、建立新MySQL節點
  9.2、新節點安裝MGR外掛
  9.3、新節點設定複製賬號
  9.4、在原3節點執行修改引數
  9.5、新節點加入
  9.6、檢視所有節點
十、重置MGR配置

原文：https://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454779517&idx=1&sn=33e53ae7ba4cd197ab0aabe2b47fdab8&chksm=fe8b9b05c9fc121342a467e1e3cf664ebcd9868632f1a0e831371fcf5e177e822085e8d37603&scene=126&sessionid=1597723838&key=6121756d4ad9fd3c089b55b2e4b86ca6503025ccbd8ac6db3e0cc31e3b37761facfba24d00c5dc7a0e3829c720cc41355dee091a0188ae55b7afd3a8fca43caa323fa5b7b6ec6d03559cbbebfe31ecac4fffddc267d3a769d269538dc6b450ec863995517827ca3de7a37353f9c2dbca63e25abb0f8fd898b971c095c0dab50e&ascene=1&uin=MTk5MDM4ODY5&devicetype=Windows+10+x64&version=62090538&lang=zh_CN&exportkey=A6qE%2FrTGfaERjk%2BNTt6TgME%3D&pass_ticket=czBK0U%2BcRKRxaBAJ63Hrx5IKsFM7L4RS%2Bt1miQY1BBI%3D

# 一、建立3臺MySQL環境

```sh
# 拉取映象
docker pull mysql:8.0.20
# 建立專用網路
docker network create --subnet=172.72.0.0/24 mysql-network

# 建立目錄儲存資料
mkdir -p /usr/local/mysql/lhrmgr15/conf.d
mkdir -p /usr/local/mysql/lhrmgr15/data
mkdir -p /usr/local/mysql/lhrmgr16/conf.d
mkdir -p /usr/local/mysql/lhrmgr16/data
mkdir -p /usr/local/mysql/lhrmgr17/conf.d
mkdir -p /usr/local/mysql/lhrmgr17/data

# 建立3個節點的MySQL
docker run -d --name mysql8020mgr33065 \
   -h lhrmgr15 -p 33065:3306 --net=mysql-network --ip 172.72.0.15 \
   -v /usr/local/mysql/lhrmgr15/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/lhrmgr15/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=lhr \
   -e TZ=Asia/Shanghai \
   mysql:8.0.20

docker run -d --name mysql8020mgr33066 \
   -h lhrmgr16 -p 33066:3306 --net=mysql-network --ip 172.72.0.16 \
   -v /usr/local/mysql/lhrmgr16/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/lhrmgr16/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=lhr \
   -e TZ=Asia/Shanghai \
   mysql:8.0.20

docker run -d --name mysql8020mgr33067 \
   -h lhrmgr17 -p 33067:3306 --net=mysql-network --ip 172.72.0.17 \
   -v /usr/local/mysql/lhrmgr17/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/lhrmgr17/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=lhr \
   -e TZ=Asia/Shanghai \
   mysql:8.0.20
```

# 二、修改MySQL引數

```sh
cat > /usr/local/mysql/lhrmgr15/conf.d/my.cnf <  /usr/local/mysql/lhrmgr16/conf.d/my.cnf < /usr/local/mysql/lhrmgr17/conf.d/my.cnf < INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.23 sec)

# 五、設定複製賬號（所有節點執行）

```sql
SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY 'lhr';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lhr' FOR CHANNEL 'group_replication_recovery';
```

執行過程：

```sql
MySQL [(none)]> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> CREATE USER repl@'%' IDENTIFIED BY 'lhr';
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lhr' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 1 warning (0.04 sec)
```

# 六、啟動MGR單主模式

## 6.1、啟動MGR，在主庫(172.72.0.15)上執行

```sql
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

-- 檢視MGR組資訊 
SELECT * FROM performance_schema.replication_group_members;

```

執行過程：

```sql
MySQL [(none)]> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.49 sec)

MySQL [(none)]> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.01 sec)
```

## 6.2、其他節點加入MGR，在從庫（172.72.0.16，172.72.0.17）上執行

```sql
START GROUP_REPLICATION;
-- 檢視MGR組資訊
SELECT * FROM performance_schema.replication_group_members;
```

執行結果：

可以看到，3個節點狀態為online，並且主節點為172.72.0.15，只有主節點可以寫入，其他節點只讀，MGR單主模式搭建成功。

#  七、多主和單主模式切換

##  7.1、查詢當前模式

```sql
MySQL [(none)]>  show variables like '%group_replication_single_primary_mode%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON    |
+---------------------------------------+-------+
1 row in set (0.01 sec)

MySQL [(none)]> SELECT @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.00 sec)
```

引數group_replication_single_primary_mode為ON，表示單主模式。

##  7.2、函式實現多主和單主切換

函式切換：從MySQL 8.0.13開始，可以使用函式進行線上修改MGR模式。

```sql
-- 單主切多主
select group_replication_switch_to_multi_primary_mode(); 
-- 多主切單主，入參需要傳入主庫的server_uuid
select group_replication_switch_to_single_primary_mode('@@server_uuid') ;

-- 檢視組資訊
SELECT * FROM performance_schema.replication_group_members;
```

### 7.2.1、單主切多主模式

```sql
MySQL [(none)]> SELECT @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.00 sec)

MySQL [(none)]> 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.01 sec)

### 7.2.2、多主切單主模式

```sql
MySQL [(none)]> SELECT @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
1 row in set (0.00 sec)

MySQL [(none)]> select group_replication_switch_to_single_primary_mode('67090f47-d785-11ea-b76c-0242ac480010') ;
+-----------------------------------------------------------------------------------------+
| group_replication_switch_to_single_primary_mode('67090f47-d785-11ea-b76c-0242ac480010') |
+-----------------------------------------------------------------------------------------+
| Mode switched to single-primary successfully.                                           |
+-----------------------------------------------------------------------------------------+
1 row in set (1.02 sec)

MySQL [(none)]> SELECT @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.00 sec)
```

## 7.3、手動切換

MGR切換模式需要重新啟動組複製，因些需要在所有節點上先關閉組複製，設定 group_replication_single_primary_mode=OFF 等引數，再啟動組複製。

### 7.3.1、單主切多主模式

1、停止組複製(所有節點執行)：

```sql
stop group_replication;
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;
```

2、隨便選擇某個節點執行

```sql
SET GLOBAL group_replication_bootstrap_group=ON; 
START GROUP_REPLICATION; 
SET GLOBAL group_replication_bootstrap_group=OFF;
```

3、其他節點執行

```
START GROUP_REPLICATION; 
```

4、檢視組資訊，所有節點的 MEMBER_ROLE 都為 PRIMARY

可以看到所有節點狀態都是online，角色都是PRIMARY，MGR多主模式切換成功。

### 7.3.2、多主切單主模式

1、所有節點執行

```sql
stop group_replication;
set global group_replication_enforce_update_everywhere_checks=OFF;
set global group_replication_single_primary_mode=ON;
```

2、主節點（172.72.0.16）執行

```sql
SET GLOBAL group_replication_bootstrap_group=ON; 
START GROUP_REPLICATION; 
SET GLOBAL group_replication_bootstrap_group=OFF;
```

3、從節點（172.72.0.15、172.72.0.17）執行

```sql
START GROUP_REPLICATION; 
```

4、檢視MGR組資訊

# 八、測試同步

在主節點上執行以下命令，然後在其它節點查詢：

```sql
create database lhrdb;
CREATE TABLE lhrdb.`tb1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `hostname` varchar(100) DEFAULT NULL,
 `server_id` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into lhrdb.tb1(hostname,server_id) select @@hostname,@@server_id;
select * from lhrdb.tb1;

-- 3個節點查詢出來的值一樣
MySQL [(none)]> select * from lhrdb.tb1;
+----+----------+-----------+
| id | hostname | server_id |
+----+----------+-----------+
|  1 | lhrmgr16 | 802033066 |
+----+----------+-----------+
1 row in set (0.02 sec)
```

# 九、MGR新增節點

##  9.1、建立新MySQL節點

```sh
mkdir -p /usr/local/mysql/lhrmgr18/conf.d
mkdir -p /usr/local/mysql/lhrmgr18/data

docker run -d --name mysql8020mgr33068 \
  -h lhrmgr18 -p 33068:3306 --net=mysql-network --ip 172.72.0.18 \
  -v /usr/local/mysql/lhrmgr18/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/lhrmgr18/data:/var/lib/mysql/ \
  -e MYSQL_ROOT_PASSWORD=lhr \
  -e TZ=Asia/Shanghai \
  mysql:8.0.20

cat > /usr/local/mysql/lhrmgr18/conf.d/my.cnf < select @@group_replication_enforce_update_everywhere_checks,@@group_replication_single_primary_mode;
+------------------------------------------------------+-----------------------------------------+
| @@group_replication_enforce_update_everywhere_checks | @@group_replication_single_primary_mode |
+------------------------------------------------------+-----------------------------------------+
|                                                    0 |                                       1 |
+------------------------------------------------------+-----------------------------------------+

-- 如果不一致，那麼需要修改
set global group_replication_single_primary_mode=ON;
set global group_replication_enforce_update_everywhere_checks=OFF;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lhr' FOR CHANNEL 'group_replication_recovery';

-- 新節點加入
start group_replication;
```

##  9.6、檢視所有節點

#  十、重置MGR配置

如果需要重置，那麼需要執行如下命令：

```sql
STOP GROUP_REPLICATION;
reset master;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lhr' FOR CHANNEL 'group_replication_recovery';
start GROUP_REPLICATION;
```

——————————————————————————————————————————
>【乾貨來了|小麥苗IT資料分享】
★小麥苗DB職場乾貨：[https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w](https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w)
★小麥苗資料庫健康檢查：[https://share.weiyun.com/5lb2U2M](https://share.weiyun.com/5lb2U2M)
★小麥苗微店：[https://weidian.com/s/793741433?wfr=c&ifr=shopdetail](https://weidian.com/s/793741433?wfr=c&ifr=shopdetail)
★各種作業系統下的資料庫安裝檔案（Linux、Windows、AIX等）：[https://pan.baidu.com/s/19yJdUQhGz2hTgozb9ATdAw](https://pan.baidu.com/s/19yJdUQhGz2hTgozb9ATdAw)  提取碼：4xpv
★小麥苗分享的資料：[https://share.weiyun.com/57HUxNi](https://share.weiyun.com/57HUxNi)
★小麥苗課堂資料：[https://share.weiyun.com/5fAdN5m](https://share.weiyun.com/5fAdN5m)
★小麥苗課堂試聽資料：[https://share.weiyun.com/5HnQEuL](https://share.weiyun.com/5HnQEuL)
★小麥苗出版的相關書籍：[https://share.weiyun.com/5sQBQpY](https://share.weiyun.com/5sQBQpY)
★小麥苗部落格文章：[https://share.weiyun.com/5ufi4Dx](https://share.weiyun.com/5ufi4Dx)
★資料庫系列（Oracle、MySQL、NoSQL）：[https://share.weiyun.com/5n1u8gv](https://share.weiyun.com/5n1u8gv)
★公開課錄影檔案：[https://share.weiyun.com/5yd7ukG](https://share.weiyun.com/5yd7ukG)
★其它常用軟體分享：[https://share.weiyun.com/53BlaHX](https://share.weiyun.com/53BlaHX)
★其它IT資料（OS、網路、儲存等）：[https://share.weiyun.com/5Mn6ESi](https://share.weiyun.com/5Mn6ESi)
★Python資料：[https://share.weiyun.com/5iuQ2Fn](https://share.weiyun.com/5iuQ2Fn)
★已安裝配置好的虛擬機器：[https://share.weiyun.com/5E8pxvT](https://share.weiyun.com/5E8pxvT)
——————————————————————————————————————————

>**About Me**
——————————————————————————————————————————
● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除
● 《資料庫筆試面試寶典》作者
● OCP、OCM、高可用（RAC+DG+OGG）網路班開講啦，有需要的小夥伴可以私聊我
● 本文在itpub、部落格園、CSDN和個人微信公眾號（ [DB寶](https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w)）上有同步更新
● 本文itpub地址： [http://blog.itpub.net/26736162](http://blog.itpub.net/26736162)
● 本文部落格園地址： [http://www.cnblogs.com/lhrbest](http://www.cnblogs.com/lhrbest)
● 本文CSDN地址： [https://blog.csdn.net/lihuarongaini](https://blog.csdn.net/lihuarongaini)
● 本文pdf版、個人簡介及小麥苗雲盤地址： [http://blog.itpub.net/26736162/viewspace-1624453/](http://blog.itpub.net/26736162/viewspace-1624453/)
● 資料庫筆試面試題庫及解答： [http://blog.itpub.net/26736162/viewspace-2134706/](http://blog.itpub.net/26736162/viewspace-2134706/)
● DBA寶典今日頭條號地址： [http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826](http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826)
——————————————————————————————————————————
● QQ群號： 230161599 、618766405
● 微信群：可加我微信，我拉大家進群，非誠勿擾
● 聯絡我請加QQ好友 （ 646634621 ），註明新增緣由
● 於 2019-10-01 06:00 ~ 2019-10-31 24:00 在西安完成
● 最新修改時間：2019-10-01 06:00 ~ 2019-10-31 24:00
● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
——————————————————————————————————————————
●==小麥苗的微店==： [https://weidian.com/s/793741433?wfr=c&ifr=shopdetail](https://weidian.com/s/793741433?wfr=c&ifr=shopdetail)
●==小麥苗出版的資料庫類叢書==： [http://blog.itpub.net/26736162/viewspace-2142121/](http://blog.itpub.net/26736162/viewspace-2142121/)
●==小麥苗OCP、OCM、高可用網路班==： [http://blog.itpub.net/26736162/viewspace-2148098/](http://blog.itpub.net/26736162/viewspace-2148098/)
●==小麥苗騰訊課堂主頁==： [https://lhr.ke.qq.com/](https://lhr.ke.qq.com/)
——————————————————————————————————————————
使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（ [DB寶](https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w)）
及QQ群（DBA寶典）、新增小麥苗微信， 學習最實用的資料庫技術。
![小麥苗資訊](https://img-blog.csdnimg.cn/20191021104734877.png)
——————————————————————————————————————————

【DB寶18】在Docker中安裝使用MySQL高可用之MGR

相關文章