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

lhrbest發表於2020-04-23


在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寶典今日頭條號地址:

........................................................................................................................

● 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

● 文章內容來源於小麥苗的學習筆記,部分整理自網路,若有侵權或不當之處還請諒解

● 版權所有,歡迎分享本文,轉載請保留出處

........................................................................................................................

小麥苗的微店

小麥苗出版的資料庫類叢書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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章