在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宝典）、添加小麦苗微 信， 学习最实用的数据库技术。
........................................................................................................................ |
 |
|