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

lhrbest發表於2020-08-18
DockerMySql

目錄
一、建立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環境

# 拉取映象
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引數

cat > /usr/local/mysql/lhrmgr15/conf.d/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id = 802033065
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log-bin = 
binlog_format=row
binlog_checksum=NONE
log-slave-updates=1
skip-name-resolve
auto-increment-increment=2
auto-increment-offset=1
gtid-mode=ON
enforce-gtid-consistency=on
default_authentication_plugin=mysql_native_password
max_allowed_packet = 500M
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=lhrmgr15-relay-bin-ip15
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "172.72.0.15:33061"
loose-group_replication_group_seeds= "172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17"
report_host=172.72.0.15
report_port=3306
EOF
cat >  /usr/local/mysql/lhrmgr16/conf.d/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id = 802033066
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log-bin = 
binlog_format=row
binlog_checksum=NONE
log-slave-updates=1
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
default_authentication_plugin=mysql_native_password
max_allowed_packet = 500M
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=lhrmgr16-relay-bin-ip16
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "172.72.0.16:33062"
loose-group_replication_group_seeds= "172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17"
report_host=172.72.0.16
report_port=3306
EOF
cat > /usr/local/mysql/lhrmgr17/conf.d/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id = 802033067
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log-bin = 
binlog_format=row
binlog_checksum=NONE
log-slave-updates=1
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
default_authentication_plugin=mysql_native_password
max_allowed_packet = 500M
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=lhrmgr16-relay-bin-ip16
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "172.72.0.17:33063"
loose-group_replication_group_seeds= "172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17"
report_host=172.72.0.17
report_port=3306
EOF

三、重啟MySQL環境

# 重啟MySQL
docker restart mysql8020mgr33065 mysql8020mgr33066 mysql8020mgr33067
docker ps
# 進入MySQL
docker exec -it mysql8020mgr33065 bash
docker exec -it mysql8020mgr33065 mysql -uroot -plhr
#遠端連線MySQL
mysql -uroot -plhr -h192.168.1.35 -P33065 
mysql -uroot -plhr -h192.168.1.35 -P33066 
mysql -uroot -plhr -h192.168.1.35 -P33067 
# 檢視MySQL日誌
docker logs -f --tail 10 mysql8020mgr33065
docker logs -f --tail 10 mysql8020mgr33066
docker logs -f --tail 10 mysql8020mgr33067
# 檢視MySQL的主機名、server_id和server_uuid
mysql -uroot -plhr -h192.168.1.35 -P33065 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.1.35 -P33066 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.1.35 -P33067 -e "select @@hostname,@@server_id,@@server_uuid"

結果：

[root@docker35 ~]# mysql -uroot -plhr -h192.168.1.35 -P33065 -e "select @@hostname,@@server_id,@@server_uuid"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| lhrmgr15   |   802033065 | 611717fe-d785-11ea-9342-0242ac48000f |
+------------+-------------+--------------------------------------+
[root@docker35 ~]# mysql -uroot -plhr -h192.168.1.35 -P33066 -e "select @@hostname,@@server_id,@@server_uuid"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| lhrmgr16   |   802033066 | 67090f47-d785-11ea-b76c-0242ac480010 |
+------------+-------------+--------------------------------------+
[root@docker35 ~]# mysql -uroot -plhr -h192.168.1.35 -P33067 -e "select @@hostname,@@server_id,@@server_uuid"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| lhrmgr17   |   802033067 | 678cf064-d785-11ea-b8ce-0242ac480011 |
+------------+-------------+--------------------------------------+
[root@docker35 ~]#

四、安裝MGR外掛（所有節點執行）

MySQL [(none)]> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.23 sec)
MySQL [(none)]> show plugins;
+---------------------------------+----------+--------------------+----------------------+---------+
| Name                            | Status   | Type               | Library              | License |
+---------------------------------+----------+--------------------+----------------------+---------+
| group_replication               | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+---------------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.00 sec)

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

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';

執行過程：

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)上執行

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;

執行過程：

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）上執行

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

執行結果：

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         |
| group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
| group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.01 sec)

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

七、多主和單主模式切換

7.1、查詢當前模式

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模式。

-- 單主切多主
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、單主切多主模式

MySQL [(none)]> SELECT @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (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         |
| group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
| group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows 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)
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         |
| group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
| group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
MySQL [(none)]> SELECT @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+

7.2.2、多主切單主模式

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 * 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       | SECONDARY   | 8.0.20         |
| group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
| group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 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、停止組複製(所有節點執行)：

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

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

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

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         |
| group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
| group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

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

7.3.2、多主切單主模式

1、所有節點執行

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）執行

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）執行

START GROUP_REPLICATION;

4、檢視MGR組資訊

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       | SECONDARY   | 8.0.20         |
| group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
| group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

八、測試同步

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

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節點

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 <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id = 802033068
log-bin = 
binlog_format=row
binlog_checksum=NONE
log-slave-updates=1
skip-name-resolve
auto-increment-increment=2
auto-increment-offset=1
gtid-mode=ON
enforce-gtid-consistency=on
default_authentication_plugin=mysql_native_password
max_allowed_packet = 500M
log_slave_updates=on
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=lhrmgr18-relay-bin-ip18
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "172.72.0.18:33064"
loose-group_replication_group_seeds= "172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063,172.72.0.18:33064"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17,172.72.0.18"
report_host=172.72.0.18
report_port=3306
EOF
docker restart mysql8020mgr33068
docker ps
mysql -uroot -plhr -h192.168.1.35 -P33065 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.1.35 -P33066 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.1.35 -P33067 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.1.35 -P33068 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.1.35 -P33065 
mysql -uroot -plhr -h192.168.1.35 -P33066 
mysql -uroot -plhr -h192.168.1.35 -P33067 
mysql -uroot -plhr -h192.168.1.35 -P33068 
docker logs -f --tail 10 mysql8020mgr33065
docker logs -f --tail 10 mysql8020mgr33066
docker logs -f --tail 10 mysql8020mgr33067
docker logs -f --tail 10 mysql8020mgr33068

9.2、新節點安裝MGR外掛

-- 安裝MGR外掛（新增節點執行）
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
show plugins;

9.3、新節點設定複製賬號

-- 設定複製賬號（新增節點執行）
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';

9.4、在原3節點執行修改引數

set global group_replication_group_seeds='172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063,172.72.0.18:33064';
stop group_replication;
set global group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17,172.72.0.18";
start group_replication;

9.5、新節點加入

-- 4個節點需要保證以下2個引數的值一致
MySQL [(none)]> 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、檢視所有節點

MySQL [lhrdb]> 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         |
| group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
| group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
| group_replication_applier | e4d6bf4b-d78d-11ea-b1b6-0242ac480012 | 172.72.0.18 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
4 rows in set (0.31 sec)

十、重置MGR配置

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

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

About Me
——————————————————————————————————————————
● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除
● 《資料庫筆試面試寶典》作者
● OCP、OCM、高可用（RAC+DG+OGG）網路班開講啦，有需要的小夥伴可以私聊我
● 本文在itpub、部落格園、CSDN和個人微信公眾號（ DB寶）上有同步更新
● 本文itpub地址： http://blog.itpub.net/26736162
● 本文部落格園地址： http://www.cnblogs.com/lhrbest
● 本文CSDN地址： https://blog.csdn.net/lihuarongaini
● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/
● DBA寶典今日頭條號地址： 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
●==小麥苗出版的資料庫類叢書==： http://blog.itpub.net/26736162/viewspace-2142121/
●==小麥苗OCP、OCM、高可用網路班==： http://blog.itpub.net/26736162/viewspace-2148098/
●==小麥苗騰訊課堂主頁==： https://lhr.ke.qq.com/
——————————————————————————————————————————
使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（ DB寶）
及QQ群（DBA寶典）、新增小麥苗微信， 學習最實用的資料庫技術。
小麥苗資訊
——————————————————————————————————————————

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2712530/，如需轉載，請註明出處，否則將追究法律責任。

相關文章