您当前的位置： 首页 >  docker

在Docker中安装使用MySQL高可用之MGR

发布时间：2020-08-18 12:17:51 ，浏览量：0

文章目录
  • 一、创建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配置
一、创建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 ），注明添加缘由 ● 于 2020年8月 在西安完成 ● 最新修改时间：2020年8月 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 —————————————————————————————————————————— ●小麦苗的微店： 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宝典）、添加小麦苗微信， 学习最实用的数据库技术。 小麦苗信息 ——————————————————————————————————————————

关注
打赏
1688896170
查看更多评论

暂无认证

  • 0浏览

    0关注

    107766博文

    0收益

  • 0浏览

    0点赞

    0打赏

    0留言

私信
关注
热门博文

[ 申请 ]友情链接：

快连vpn 快连 快连vpn 搜外友链 笔趣阁 爱思助手 ClashX教程 绘画宝宝 配音宝宝
立即登录/注册

微信扫码登录

0.0554s