centos linux环境用docker进行MySql的MGR集群部署及验证

MySql的MGR集群部署

主要参考文件

【MySQL】—基于Docker安装部署MySQL8.0高可用之MGR集群 - 简书

原理介绍主要参考文档

mysql实战之mgr集群_mysql mgr-CSDN博客

1、启动docker

systemctl start docker

2、拉取镜像

docker pull mysql:8.0.20

3、网络配置

主master	从slave1	从slave2
hostname master-host; bash;	hostname slave1-host; bash;	hostname slave2-host; bash;

三台主机下

vi /etc/hosts



192.168.247.128 master-host

192.168.247.129 slave1-host

192.168.247.130 slave2-host

4、配置文件修改

mkdir docker/mysql/conf/my.cnf

mkdir docker/mysql/data/

详见附录

上传修改的文件······

5、创建容器：

主站master

docker run -d --name mysql-mgr-master -h mysql-mgr-master \

   -p 13066:3306 -p 33011:33011 \

    --network=host \

   -v /docker/mysql/conf/my-master.cnf:/etc/mysql/my.cnf -v /docker/mysql/data:/var/lib/mysql/ \

   -e MYSQL_ROOT_PASSWORD=admin123456 \

   -e TZ=Asia/Shanghai \

    --privileged=true mysql:8.0.20 

从站1

docker run -d --name mysql-mgr-slave01 -h mysql-mgr-slave01 \

   --network=host \

   -v /docker/mysql/conf/my-slave1.cnf:/etc/mysql/my.cnf -v /docker/mysql/data:/var/lib/mysql/ \

   -e MYSQL_ROOT_PASSWORD=admin123456 \

   -e TZ=Asia/Shanghai \

    --privileged=true mysql:8.0.20 

从站2

docker run -d --name mysql-mgr-slave02 -h mysql-mgr-slave02 \

   --network=host \

   -v /docker/mysql/conf/my-slave2.cnf:/etc/mysql/my.cnf -v /docker/mysql/data:/var/lib/mysql/ \

   -e MYSQL_ROOT_PASSWORD=admin123456 \

   -e TZ=Asia/Shanghai \

    --privileged=true mysql:8.0.20 

重启容器

docker restart `docker ps -a |grep mysql-mgr |awk -F " " '{print $1}'`

docker start mysql-mgr-master

docker start mysql-mgr-slave01

docker start mysql-mgr-slave02

6、进入容器

docker exec -it mysql-mgr-master bash

docker exec -it mysql-mgr-slave01 bash

docker exec -it mysql-mgr-slave02 bash

7、登录mysql

mysql -uroot -p -P3306

8、安装插件

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

9、三台主机设置复制账号执行

SET SQL_LOG_BIN=0;

CREATE USER repl@'%' IDENTIFIED BY 'admin123456';

GRANT REPLICATION SLAVE ON *.* TO repl@'%';

FLUSH PRIVILEGES;

SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='repl',

 MASTER_PASSWORD='admin123456' FOR CHANNEL 'group_replication_recovery';

10、启动MGR单主模式

（1）主站执行

SET GLOBAL group_replication_bootstrap_group=ON;

STOP GROUP_REPLICATION;

START GROUP_REPLICATION;

SET GLOBAL group_replication_bootstrap_group=OFF;

-- 查看MGR组信息

 SELECT * FROM performance_schema.replication_group_members;

（2）从站执行

CHANGE MASTER TO MASTER_USER='repl', MASTER_ASSWORD='admin123456' FOR CHANNEL 'group_replication_recovery';

set global group_replication_recovery_get_public_key=on;

start group_replication;

11、验证

（1）验证节点加入成功

 SELECT * FROM performance_schema.replication_group_members;

（2）单主模式验证节点宕机后自动选主

主站：docker stop mysql-mgr-master

从站slave01：SELECT * FROM performance_schema.replication_group_members;

从站slave01切换为主站

（3）验证读写分离

权限：主站读写和从站只读

主站写入成功

从站写入失败

主站：docker start mysql-mgr-master

slave01变为主站

和多主模式切换验证

（4）验证单主和多主模式切换

（a）查询当前模式：

show variables like '%group_replication_single_primary_mode%';

(b)单主切换多主

select group_replication_switch_to_multi_primary_mode();

(c)组成员查询验证角色

SELECT * FROM performance_schema.replication_group_members;

可见member_role有两个primary角色

（5）验证多主切换单主

（a）指定第二台为主站：

select group_replication_switch_to_single_primary_mode('481895e5-8aa8-11ee-a252-0242ac480017');

(b)第二台的member_role为primary

SELECT * FROM performance_schema.replication_group_members;

（6）验证主从同步

创建数据和表：

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> select * from lhrdb.tb1;

12、部署常见问题解决：

（1）、关闭防火墙或开放防火墙端口

systemctl stop firewalld;

systemctl disable firewalld;

systemctl status firewalld;

（2）常见报错和启动失败问题（从站启动问题：（GTID事务冲突））

从站启动问题：（GTID事务冲突）

主站：

show master status\G

从站：

RESET MASTER;

SET GLOBAL GTID_PURGED = '0f15eca5-8aa3-11ee-bfaa-0242ac480015:1-5,

'> aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-5';

set global group_replication_recovery_get_public_key=on;

‘

附录：配置文件my.cnf

1. my-master.cnf

[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 = mysql-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

group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

group_replication_start_on_boot=OFF

group_replication_local_address= "192.168.247.128:33061"

group_replication_group_seeds= "192.168.247.128:33061,192.168.247.129:33062,192.168.247.130:33063"

group_replication_bootstrap_group=OFF

group_replication_ip_whitelist="192.168.247.128,192.168.247.129,192.168.247.130"

report_host=192.168.247.128

report_port=3306

1. my-slave1.cnf

[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

group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

group_replication_start_on_boot=OFF

group_replication_local_address= "192.168.247.129:33062"

group_replication_group_seeds= "192.168.247.128:33061,192.168.247.129:33062,192.168.247.130:33063"

group_replication_bootstrap_group=OFF

group_replication_ip_whitelist="192.168.247.128,192.168.247.129,192.168.247.130"

report_host=192.168.247.129

report_port=3306

1. my-slave2.cnf

[mysqld]

user=mysql

port=3306

character_set_server=utf8mb4

secure_file_priv=''

server-id = 802033062

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=mysql-mgr-slave-relay-bin-ip23

transaction_write_set_extraction=XXHASH64

group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

group_replication_start_on_boot=OFF

group_replication_local_address= "192.168.247.130:33063"

group_replication_group_seeds= "192.168.247.128:33061,192.168.247.129:33062,192.168.247.130:33063"

group_replication_bootstrap_group=OFF

group_replication_ip_whitelist="192.168.247.128,192.168.247.129,192.168.247.130"

report_host=192.168.247.130

report_port=3306