在Docker中安裝MySQL 8.0.19之MGR（單主模式&多主模式）+新增節點

在Docker中安裝MySQL之MGR（單主模式&多主模式）-- MySQL 8.0.19 + Docker

----------- Docker中安裝MGR
docker pull mysql:8.0.19
docker network create --subnet=172.72.0.0/24 mysql-network
mkdir -p /usr/local/mysql/mgr15/conf.d
mkdir -p /usr/local/mysql/mgr15/data
mkdir -p /usr/local/mysql/mgr16/conf.d
mkdir -p /usr/local/mysql/mgr16/data
mkdir -p /usr/local/mysql/mgr17/conf.d
mkdir -p /usr/local/mysql/mgr17/data
docker run -d --name mysql8018mgr33065 \
   -h mgr15 -p 33065:3306 --net=mysql-network --ip 172.72.0.15 \
   -v /usr/local/mysql/mgr15/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/mgr15/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=lhr \
   -e TZ=Asia/Shanghai \
   mysql:8.0.19
docker run -d --name mysql8018mgr33066 \
   -h mgr16 -p 33066:3306 --net=mysql-network --ip 172.72.0.16 \
   -v /usr/local/mysql/mgr16/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/mgr16/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=lhr \
   -e TZ=Asia/Shanghai \
   mysql:8.0.19
docker run -d --name mysql8018mgr33067 \
   -h mgr17 -p 33067:3306 --net=mysql-network --ip 172.72.0.17 \
   -v /usr/local/mysql/mgr17/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/mgr17/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=lhr \
   -e TZ=Asia/Shanghai \
   mysql:8.0.19
vi /usr/local/mysql/mgr15/conf.d/my.cnf
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id = 801833065
log-bin = 
binlog_format=row
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
log-slave-updates=1
skip-name-resolve
auto-increment-increment=2
auto-increment-offset=1
gtid-mode=ON
enforce-gtid-consistency=on
skip_name_resolve
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
max_allowed_packet = 500M
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=mgr15-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
vi /usr/local/mysql/mgr16/conf.d/my.cnf
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id = 801833066
log-bin = 
binlog_format=row
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
log-slave-updates=1
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
max_allowed_packet = 500M
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=mgr16-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
vi /usr/local/mysql/mgr17/conf.d/my.cnf
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id = 801833067
log-bin = 
binlog_format=row
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
log-slave-updates=1
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
max_allowed_packet = 500M
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=mgr16-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
docker restart mysql8018mgr33065
docker restart mysql8018mgr33066
docker restart mysql8018mgr33067
docker ps
docker exec -it mysql8018mgr33065 bash
docker exec -it mysql8018mgr33065 mysql -uroot -plhr
mysql -uroot -plhr -h121.36.78.6 -P33065 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h121.36.78.6 -P33066 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h121.36.78.6 -P33067 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h121.36.78.6 -P33065 
mysql -uroot -plhr -h121.36.78.6 -P33066 
mysql -uroot -plhr -h121.36.78.6 -P33067 
docker logs -f --tail 10 mysql8018mgr33065
docker logs -f --tail 10 mysql8018mgr33066
docker logs -f --tail 10 mysql8018mgr33067
--注意，此處不能提前建立root@%使用者，否則後續啟動MGR報錯
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'lhr';
grant all on *.* to root@'localhost' WITH GRANT OPTION;
flush privileges;
select user,host,grant_priv,super_priv,authentication_string,password_last_changed from mysql.user;
#安裝MGR外掛（所有節點執行）
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
show plugins;
#設定複製賬號（所有節點執行）
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';
##########啟動MGR單主模式
# 啟動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;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 532f233c-847c-11ea-8cd1-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | PRIMARY     | 8.0.19         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
# 其他節點加入MGR，在從庫（172.72.0.16，172.72.0.17）上執行
mysql> 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 | 532f233c-847c-11ea-8cd1-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | PRIMARY     | 8.0.19         |
| group_replication_applier | 5b1bfe02-847c-11ea-b385-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | SECONDARY   | 8.0.19         |
| group_replication_applier | 5b3cf88f-847c-11ea-944d-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | SECONDARY   | 8.0.19         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
可以看到，3個節點狀態為online，並且主節點為172.72.0.15，只有主節點可以寫入，其他節點只讀，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;
--函式切換：從MySQL 8.0.13開始，可以使用函式進行線上修改MGR模式。
select group_replication_switch_to_multi_primary_mode(); #單主切多主
select group_replication_switch_to_single_primary_mode('5b1bfe02-847c-11ea-b385-0242ac480010') ;  #多主切單主
MySQL [lhrdb]> show variables like '%group_replication_single_primary_mode%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | OFF   |
+---------------------------------------+-------+
MySQL [lhrdb]> SELECT @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
##########切換到多主模式
MGR切換模式需要重新啟動組複製，因些需要在所有節點上先關閉組複製，設定 group_replication_single_primary_mode=OFF 等引數，再啟動組複製。
# 停止組複製(所有節點執行)：
stop group_replication;
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;
# 隨便選擇某個節點執行
SET GLOBAL group_replication_bootstrap_group=ON; 
START GROUP_REPLICATION; 
SET GLOBAL group_replication_bootstrap_group=OFF;
# 其他節點執行
START GROUP_REPLICATION; 
# 檢視組資訊，所有節點的 MEMBER_ROLE 都為 PRIMARY，SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 532f233c-847c-11ea-8cd1-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | PRIMARY     | 8.0.19         |
| group_replication_applier | 5b1bfe02-847c-11ea-b385-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | PRIMARY     | 8.0.19         |
| group_replication_applier | 5b3cf88f-847c-11ea-944d-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | PRIMARY     | 8.0.19         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.03 sec)
可以看到所有節點狀態都是online，角色都是PRIMARY，MGR多主模式搭建成功。
##########切回單主模式
# 所有節點執行
stop group_replication;
set global group_replication_enforce_update_everywhere_checks=OFF;
set global group_replication_single_primary_mode=ON;
# 主節點（172.72.0.16）執行
SET GLOBAL group_replication_bootstrap_group=ON; 
START GROUP_REPLICATION; 
SET GLOBAL group_replication_bootstrap_group=OFF;
# 從節點（172.72.0.15、172.72.0.17）執行
START GROUP_REPLICATION; 
# 檢視MGR組資訊，SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 532f233c-847c-11ea-8cd1-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | SECONDARY   | 8.0.19         |
| group_replication_applier | 5b1bfe02-847c-11ea-b385-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | PRIMARY     | 8.0.19         |
| group_replication_applier | 5b3cf88f-847c-11ea-944d-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | SECONDARY   | 8.0.19         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.03 sec)
---測試自增id
CREATE TABLE lhrdb.`tb1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into lhrdb.tb1(name) select @@server_id;
insert into lhrdb.tb1(name) select @@server_id;
insert into lhrdb.tb1(name) select @@server_id;
select * from lhrdb.tb1;
-------------mgr新增節點
mkdir -p /usr/local/mysql/mgr18/conf.d
mkdir -p /usr/local/mysql/mgr18/data
docker run -d --name mysql8018mgr33068 \
   -h mgr18 -p 33068:3306 --net=mysql-network --ip 172.72.0.18 \
   -v /usr/local/mysql/mgr18/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/mgr18/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=lhr \
   -e TZ=Asia/Shanghai \
   mysql:8.0.19
vi /usr/local/mysql/mgr18/conf.d/my.cnf
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id = 801833068
log-bin = 
binlog_format=row
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
log-slave-updates=1
skip-name-resolve
auto-increment-increment=2
auto-increment-offset=1
gtid-mode=ON
enforce-gtid-consistency=on
skip_name_resolve
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
max_allowed_packet = 500M
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=mgr18-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
docker restart mysql8018mgr33068
docker ps
mysql -uroot -plhr -h121.36.78.6 -P33065 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h121.36.78.6 -P33066 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h121.36.78.6 -P33067 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h121.36.78.6 -P33068 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h121.36.78.6 -P33065 
mysql -uroot -plhr -h121.36.78.6 -P33066 
mysql -uroot -plhr -h121.36.78.6 -P33067 
mysql -uroot -plhr -h121.36.78.6 -P33068 
docker logs -f --tail 10 mysql8018mgr33065
docker logs -f --tail 10 mysql8018mgr33066
docker logs -f --tail 10 mysql8018mgr33067
docker logs -f --tail 10 mysql8018mgr33068
#安裝MGR外掛（新增節點執行）
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
show plugins;
#設定複製賬號（新增節點執行）
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';
#在其他三個節點執行：
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;
#後續需要加入到引數檔案中
#新節點加入：
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lhr' FOR CHANNEL 'group_replication_recovery';
#檢視所有節點：
mysql> SELECT * FROM performance_schema.replication_group_members;
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 | 1e791b5e-851c-11ea-80c4-0242ac480012 | 172.72.0.18 |        3306 | ONLINE       | PRIMARY     | 8.0.19         |
| group_replication_applier | 532f233c-847c-11ea-8cd1-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | PRIMARY     | 8.0.19         |
| group_replication_applier | 5b1bfe02-847c-11ea-b385-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | PRIMARY     | 8.0.19         |
| group_replication_applier | 5b3cf88f-847c-11ea-944d-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | PRIMARY     | 8.0.19         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
4 rows in set (0.31 sec)

---

---

About Me

........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在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 ），註明新增緣由 ● 於 2020-04-01 06:00 ~ 2020-04-30 24:00 在西安完成 ● 最新修改時間：2020-04-01 06:00 ~ 2020-04-30 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-2687867/，如需轉載，請註明出處，否則將追究法律責任。