【MySQL高可用】MySQL高可用之MGR部署

已于 2022-10-16 16:29:02 修改
阅读量885 收藏 1
点赞数
分类专栏： MySQL 文章标签： mysql 服务器 docker
于 2022-10-15 12:18:52 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_49756466/article/details/127333596
版权

【MySQL高可用】MySQL高可用之MGR部署

参考：https://www.xmmup.com/dbbao45mysqlgaokeyongzhimgrconsuljiagoubushu.html

架构介绍

采用MGR集群、和consul服务注册发现实现应用端通过动态dns访问MGR集群，实现数据库高可用，自动化切换的方案。

本次环境一共包含7台机器。其中3台做MySQL的MGR数据节点（也需要安装Consul，作为Consul client）。单主模式，还有3台做Consul server集群。实现Consul的高可用，剩下一台做DNS服务器，用来解析MGR节点域名。

IP数据库版本主机名容器名容器映射端口Server ID实例端口MGR通讯端口备注
192.168.68.608.0.22mgr60mysql8022mgr3306033060802233060330633061MGR主节点
192.168.68.618.0.22mgr61mysql8022mgr3306133061802233061330633061MGR节点
192.168.68.628.0.22mgr62mysql8022mgr3306233062802233062330633061MGR节点

搭建MGR

申请3台MGR机器

[root@mysql conf]# docker pull lhrbest/lhrcentos76:8.0


-- 创建主从网络环境
[root@mysql ~]# docker network create --subnet=192.168.68.0/16 mhaclone
8b8e5ae9dd1efbb247323978a3dbcf025415b861b76bd828859ac934aefa77d1
[root@mysql ~]# docker network inspect mhaclone
[
    {
        "Name": "mhaclone",
        "Id": "8b8e5ae9dd1efbb247323978a3dbcf025415b861b76bd828859ac934aefa77d1",
        "Created": "2022-10-14T20:55:25.87905019+08:00",
        "Scope": "local",
        "Driver": "bridge",
        "EnableIPv6": false,
        "IPAM": {
            "Driver": "default",
            "Options": {},
            "Config": [
                {
                    "Subnet": "192.168.68.0/16"
                }
            ]
        },
        "Internal": false,
        "Attachable": false,
        "Ingress": false,
        "ConfigFrom": {
            "Network": ""
        },
        "ConfigOnly": false,
        "Containers": {},
        "Options": {},
        "Labels": {}
    }
]

-- 生成3台CentOS的环境
[root@mysql conf]# docker rm -f mysql8022mgr33060 mysql8022mgr33061 mysql8022mgr33062

[root@mysql conf]# docker run -d --name mysql8022mgr33060  -h mgr60   --network mhaclone --ip 192.168.68.60 --privileged=true  -p 33060:3306    -v /sys/fs/cgroup:/sys/fs/cgroup  lhrbest/lhrcentos76:8.0 init
575e7d8692eeb9bd702c190d4faf281696eb913611b96cc3eb14f2fd81022b74
[root@mysql conf]# docker run -d --name mysql8022mgr33061  -h mgr61   --network mhaclone --ip 192.168.68.61 --privileged=true  -p 33061:3306    -v /sys/fs/cgroup:/sys/fs/cgroup  lhrbest/lhrcentos76:8.0 init
73d80f98b3ddd54a5b9c6a4a7c91ae1bf54980e1d7f7aafca571827aff328ae5
[root@mysql conf]# docker run -d --name mysql8022mgr33062  -h mgr62  --network mhaclone --ip 192.168.68.62 --privileged=true  -p 33062:3306    -v /sys/fs/cgroup:/sys/fs/cgroup  lhrbest/lhrcentos76:8.0 init
3705d9583d6d8f8574a839ddb4e6a46b2055efc86d6e7f0b5b26a074483f6ff6

mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar下载地址：

https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar

-- 上传mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar到docker 
[root@mysql ~]# pwd
/root
[root@mysql ~]# ls
anaconda-ks.cfg  centos:7.6.1810  mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar  -p  --privileged=true
[root@mysql ~]# docker cp mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar mysql8022mgr33060:/
[root@mysql ~]# docker cp mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar mysql8022mgr33061:/
[root@mysql ~]# docker cp mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar mysql8022mgr33062:/

[root@mysql ~]# docker network connect bridge mysql8022mgr33060
[root@mysql ~]# docker network connect bridge mysql8022mgr33061
[root@mysql ~]# docker network connect bridge mysql8022mgr33062
[root@mysql ~]# docker restart  mysql8022mgr33060 mysql8022mgr33061 mysql8022mgr33062

3台主机安装MySQL环境

mysql8022mgr33060

[root@mysql ~]# docker exec -it mysql8022mgr33060 bash
[root@mgr60 /]# tar -xvf mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar
[root@mgr60 /]# yum localinstall -y mysql-community-*.rpm
-- 取消密码认证
[root@mgr60 /]# mv /usr/lib64/mysql/plugin/validate_password.so /usr/lib64/mysql/plugin/validate_password.so_bk
[root@mgr60 /]# mv /usr/lib64/mysql/plugin/component_validate_password.so /usr/lib64/mysql/plugin/component_validate_password.so_bk
[root@mgr60 /]# systemctl start mysqld
[root@mgr60 /]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2022-10-15 11:43:50 CST; 975ms ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1140 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 1211 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/docker-575e7d8692eeb9bd702c190d4faf281696eb913611b96cc3eb14f2fd81022b74.scope/system.slice/mysqld.service
           └─1211 /usr/sbin/mysqld

Oct 15 11:43:46 mgr60 systemd[1]: Starting MySQL Server...
Oct 15 11:43:50 mgr60 systemd[1]: Started MySQL Server.
[root@mgr60 /]# grep 'temporary password' /var/log/mysqld.log
2022-10-15T03:43:47.341706Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: HPe-pnyYh3us
[root@mgr60 /]# mysql -uroot -pHPe-pnyYh3us
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user root@'localhost' identified with mysql_native_password by 'wcb';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to root@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> create user root@'%' identified with mysql_native_password by 'wcb';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on *.* to root@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,grant_priv,super_priv,authentication_string,password_last_changed from mysql.user;
+------------------+-----------+------------+------------+------------------------------------------------------------------------+-----------------------+
| user             | host      | grant_priv | super_priv | authentication_string                                                  | password_last_changed |
+------------------+-----------+------------+------------+------------------------------------------------------------------------+-----------------------+
| root             | %         | Y          | Y          | *1095707276B0CF42657EB271586320F15FD4D6AB                              | 2022-10-15 11:45:00   |
| mysql.infoschema | localhost | N          | N          | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2022-10-15 11:43:47   |
| mysql.session    | localhost | N          | Y          | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2022-10-15 11:43:47   |
| mysql.sys        | localhost | N          | N          | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2022-10-15 11:43:47   |
| root             | localhost | Y          | Y          | *1095707276B0CF42657EB271586320F15FD4D6AB                              | 2022-10-15 11:44:49   |
+------------------+-----------+------------+------------+------------------------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)

mysql8022mgr33061

[root@mysql ~]# docker exec -it mysql8022mgr33061 bash
[root@mgr61 /]# tar -xvf mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar
[root@mgr61 /]# yum localinstall -y mysql-community-*.rpm
[root@mgr61 /]# mv /usr/lib64/mysql/plugin/validate_password.so /usr/lib64/mysql/plugin/validate_password.so_bk
[root@mgr61 /]#  mv /usr/lib64/mysql/plugin/component_validate_password.so /usr/lib64/mysql/plugin/component_validate_password.so_bk
[root@mgr61 /]# systemctl start mysqld
[root@mgr61 /]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2022-10-15 11:47:14 CST; 608ms ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1262 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 1333 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/docker-73d80f98b3ddd54a5b9c6a4a7c91ae1bf54980e1d7f7aafca571827aff328ae5.scope/system.slice/mysqld.service
           └─1333 /usr/sbin/mysqld

Oct 15 11:47:10 mgr61 systemd[1]: Starting MySQL Server...
Oct 15 11:47:14 mgr61 systemd[1]: Started MySQL Server.
[root@mgr61 /]# grep 'temporary password' /var/log/mysqld.log
2022-10-15T03:47:11.109466Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: <A!;j)I4Tptt
[root@mgr61 /]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user root@'localhost' identified with mysql_native_password by 'wcb';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on *.* to root@'localhost' with grant option;
Query OK, 0 rows affected (0.01 sec)

mysql> create user root@'%' identified with mysql_native_password by 'wcb';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on *.* to root@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,grant_priv,super_priv,authentication_string,password_last_changed from mysql.user;
+------------------+-----------+------------+------------+------------------------------------------------------------------------+-----------------------+
| user             | host      | grant_priv | super_priv | authentication_string                                                  | password_last_changed |
+------------------+-----------+------------+------------+------------------------------------------------------------------------+-----------------------+
| root             | %         | Y          | Y          | *1095707276B0CF42657EB271586320F15FD4D6AB                              | 2022-10-15 11:48:35   |
| mysql.infoschema | localhost | N          | N          | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2022-10-15 11:47:11   |
| mysql.session    | localhost | N          | Y          | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2022-10-15 11:47:11   |
| mysql.sys        | localhost | N          | N          | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2022-10-15 11:47:11   |
| root             | localhost | Y          | Y          | *1095707276B0CF42657EB271586320F15FD4D6AB                              | 2022-10-15 11:48:27   |
+------------------+-----------+------------+------------+------------------------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)

mysql8022mgr33062

[root@mysql ~]# docker exec -it mysql8022mgr33062 bash
[root@mgr62 /]# tar -xvf mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar
mysql-community-client-8.0.22-1.el7.x86_64.rpm
mysql-community-client-plugins-8.0.22-1.el7.x86_64.rpm
mysql-community-common-8.0.22-1.el7.x86_64.rpm
mysql-community-devel-8.0.22-1.el7.x86_64.rpm
mysql-community-embedded-compat-8.0.22-1.el7.x86_64.rpm
mysql-community-libs-8.0.22-1.el7.x86_64.rpm
mysql-community-libs-compat-8.0.22-1.el7.x86_64.rpm
mysql-community-server-8.0.22-1.el7.x86_64.rpm
mysql-community-test-8.0.22-1.el7.x86_64.rpm
[root@mgr62 /]# yum localinstall -y mysql-community-*.rpm
Loaded plugins: fastestmirror, ovl
Examining mysql-community-client-8.0.22-1.el7.x86_64.rpm: mysql-community-client-8.0.22-1.el7.x86_64
Marking mysql-community-client-8.0.22-1.el7.x86_64.rpm as an update to mysql-community-client-5.7.32-1.el7.x86_64
Examining mysql-community-client-plugins-8.0.22-1.el7.x86_64.rpm: mysql-community-client-plugins-8.0.22-1.el7.x86_64
Marking mysql-community-client-plugins-8.0.22-1.el7.x86_64.rpm to be installed
Examining mysql-community-common-8.0.22-1.el7.x86_64.rpm: mysql-community-common-8.0.22-1.el7.x86_64
Marking mysql-community-common-8.0.22-1.el7.x86_64.rpm as an update to mysql-community-common-5.7.32-1.el7.x86_64
Examining mysql-community-devel-8.0.22-1.el7.x86_64.rpm: mysql-community-devel-8.0.22-1.el7.x86_64
Marking mysql-community-devel-8.0.22-1.el7.x86_64.rpm to be installed
Examining mysql-community-embedded-compat-8.0.22-1.el7.x86_64.rpm: mysql-community-embedded-compat-8.0.22-1.el7.x86_64
Marking mysql-community-embedded-compat-8.0.22-1.el7.x86_64.rpm to be installed
Examining mysql-community-libs-8.0.22-1.el7.x86_64.rpm: mysql-community-libs-8.0.22-1.el7.x86_64
Marking mysql-community-libs-8.0.22-1.el7.x86_64.rpm as an update to mysql-community-libs-5.7.32-1.el7.x86_64
Examining mysql-community-libs-compat-8.0.22-1.el7.x86_64.rpm: mysql-community-libs-compat-8.0.22-1.el7.x86_64
Marking mysql-community-libs-compat-8.0.22-1.el7.x86_64.rpm as an update to mysql-community-libs-compat-5.7.32-1.el7.x86_64
Examining mysql-community-server-8.0.22-1.el7.x86_64.rpm: mysql-community-server-8.0.22-1.el7.x86_64
Marking mysql-community-server-8.0.22-1.el7.x86_64.rpm to be installed
Examining mysql-community-test-8.0.22-1.el7.x86_64.rpm: mysql-community-test-8.0.22-1.el7.x86_64
Marking mysql-community-test-8.0.22-1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-client.x86_64 0:5.7.32-1.el7 will be updated
---> Package mysql-community-client.x86_64 0:8.0.22-1.el7 will be an update
---> Package mysql-community-client-plugins.x86_64 0:8.0.22-1.el7 will be installed
---> Package mysql-community-common.x86_64 0:5.7.32-1.el7 will be updated
---> Package mysql-community-common.x86_64 0:8.0.22-1.el7 will be an update
---> Package mysql-community-devel.x86_64 0:8.0.22-1.el7 will be installed
---> Package mysql-community-embedded-compat.x86_64 0:8.0.22-1.el7 will be installed
---> Package mysql-community-libs.x86_64 0:5.7.32-1.el7 will be updated
---> Package mysql-community-libs.x86_64 0:8.0.22-1.el7 will be an update
---> Package mysql-community-libs-compat.x86_64 0:5.7.32-1.el7 will be updated
---> Package mysql-community-libs-compat.x86_64 0:8.0.22-1.el7 will be an update
---> Package mysql-community-server.x86_64 0:8.0.22-1.el7 will be installed
---> Package mysql-community-test.x86_64 0:8.0.22-1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===========================================================================================================================================
 Package                               Arch         Version               Repository                                                  Size
===========================================================================================================================================
Installing:
 mysql-community-client-plugins        x86_64       8.0.22-1.el7          /mysql-community-client-plugins-8.0.22-1.el7.x86_64        1.0 M
 mysql-community-devel                 x86_64       8.0.22-1.el7          /mysql-community-devel-8.0.22-1.el7.x86_64                  58 M
 mysql-community-embedded-compat       x86_64       8.0.22-1.el7          /mysql-community-embedded-compat-8.0.22-1.el7.x86_64        90 M
 mysql-community-server                x86_64       8.0.22-1.el7          /mysql-community-server-8.0.22-1.el7.x86_64                2.3 G
 mysql-community-test                  x86_64       8.0.22-1.el7          /mysql-community-test-8.0.22-1.el7.x86_64                  678 M
Updating:
 mysql-community-client                x86_64       8.0.22-1.el7          /mysql-community-client-8.0.22-1.el7.x86_64                230 M
 mysql-community-common                x86_64       8.0.22-1.el7          /mysql-community-common-8.0.22-1.el7.x86_64                8.9 M
 mysql-community-libs                  x86_64       8.0.22-1.el7          /mysql-community-libs-8.0.22-1.el7.x86_64                   22 M
 mysql-community-libs-compat           x86_64       8.0.22-1.el7          /mysql-community-libs-compat-8.0.22-1.el7.x86_64           6.1 M

Transaction Summary
===========================================================================================================================================
Install  5 Packages
Upgrade  4 Packages

Total size: 3.4 G
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Updating   : mysql-community-common-8.0.22-1.el7.x86_64                                                                             1/13 
  Installing : mysql-community-client-plugins-8.0.22-1.el7.x86_64                                                                     2/13 
  Updating   : mysql-community-libs-8.0.22-1.el7.x86_64                                                                               3/13 
  Updating   : mysql-community-client-8.0.22-1.el7.x86_64                                                                             4/13 
  Installing : mysql-community-server-8.0.22-1.el7.x86_64                                                                             5/13 
  Installing : mysql-community-test-8.0.22-1.el7.x86_64                                                                               6/13 
  Installing : mysql-community-devel-8.0.22-1.el7.x86_64                                                                              7/13 
  Updating   : mysql-community-libs-compat-8.0.22-1.el7.x86_64                                                                        8/13 
  Installing : mysql-community-embedded-compat-8.0.22-1.el7.x86_64                                                                    9/13 
  Cleanup    : mysql-community-libs-compat-5.7.32-1.el7.x86_64                                                                       10/13 
  Cleanup    : mysql-community-client-5.7.32-1.el7.x86_64                                                                            11/13 
  Cleanup    : mysql-community-libs-5.7.32-1.el7.x86_64                                                                              12/13 
  Cleanup    : mysql-community-common-5.7.32-1.el7.x86_64                                                                            13/13 
  Verifying  : mysql-community-libs-8.0.22-1.el7.x86_64                                                                               1/13 
  Verifying  : mysql-community-client-plugins-8.0.22-1.el7.x86_64                                                                     2/13 
  Verifying  : mysql-community-common-8.0.22-1.el7.x86_64                                                                             3/13 
  Verifying  : mysql-community-server-8.0.22-1.el7.x86_64                                                                             4/13 
  Verifying  : mysql-community-embedded-compat-8.0.22-1.el7.x86_64                                                                    5/13 
  Verifying  : mysql-community-devel-8.0.22-1.el7.x86_64                                                                              6/13 
  Verifying  : mysql-community-client-8.0.22-1.el7.x86_64                                                                             7/13 
  Verifying  : mysql-community-test-8.0.22-1.el7.x86_64                                                                               8/13 
  Verifying  : mysql-community-libs-compat-8.0.22-1.el7.x86_64                                                                        9/13 
  Verifying  : mysql-community-client-5.7.32-1.el7.x86_64                                                                            10/13 
  Verifying  : mysql-community-libs-compat-5.7.32-1.el7.x86_64                                                                       11/13 
  Verifying  : mysql-community-libs-5.7.32-1.el7.x86_64                                                                              12/13 
  Verifying  : mysql-community-common-5.7.32-1.el7.x86_64                                                                            13/13 

Installed:
  mysql-community-client-plugins.x86_64 0:8.0.22-1.el7                     mysql-community-devel.x86_64 0:8.0.22-1.el7                    
  mysql-community-embedded-compat.x86_64 0:8.0.22-1.el7                    mysql-community-server.x86_64 0:8.0.22-1.el7                   
  mysql-community-test.x86_64 0:8.0.22-1.el7                              

Updated:
  mysql-community-client.x86_64 0:8.0.22-1.el7                      mysql-community-common.x86_64 0:8.0.22-1.el7                          
  mysql-community-libs.x86_64 0:8.0.22-1.el7                        mysql-community-libs-compat.x86_64 0:8.0.22-1.el7                     

Complete!
[root@mgr62 /]# mv /usr/lib64/mysql/plugin/validate_password.so /usr/lib64/mysql/plugin/validate_password.so_bk
[root@mgr62 /]# mv /usr/lib64/mysql/plugin/component_validate_password.so /usr/lib64/mysql/plugin/component_validate_password.so_bk
[root@mgr62 /]# systemctl start mysqld
[root@mgr62 /]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2022-10-15 11:49:55 CST; 1s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1345 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 1418 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/docker-3705d9583d6d8f8574a839ddb4e6a46b2055efc86d6e7f0b5b26a074483f6ff6.scope/system.slice/mysqld.service
           └─1418 /usr/sbin/mysqld

Oct 15 11:49:48 mgr62 systemd[1]: Starting MySQL Server...
Oct 15 11:49:55 mgr62 systemd[1]: Started MySQL Server.
[root@mgr62 /]# grep 'temporary password' /var/log/mysqld.log
2022-10-15T03:49:49.759486Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: r7pD1vOPKf!j
[root@mgr62 /]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user root@'localhost' identified with mysql_native_password by 'wcb';
Query OK, 0 rows affected (0.02 sec)

mysql>  grant all on *.* to root@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql>  create user root@'%' identified with mysql_native_password by 'wcb';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on *.* to root@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> select user,host,grant_priv,super_priv,authentication_string,password_last_changed from mysql.user;
+------------------+-----------+------------+------------+------------------------------------------------------------------------+-----------------------+
| user             | host      | grant_priv | super_priv | authentication_string                                                  | password_last_changed |
+------------------+-----------+------------+------------+------------------------------------------------------------------------+-----------------------+
| root             | %         | Y          | Y          | *1095707276B0CF42657EB271586320F15FD4D6AB                              | 2022-10-15 11:50:25   |
| mysql.infoschema | localhost | N          | N          | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2022-10-15 11:49:50   |
| mysql.session    | localhost | N          | Y          | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2022-10-15 11:49:50   |
| mysql.sys        | localhost | N          | N          | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2022-10-15 11:49:50   |
| root             | localhost | Y          | Y          | *1095707276B0CF42657EB271586320F15FD4D6AB                              | 2022-10-15 11:50:17   |
+------------------+-----------+------------+------------+------------------------------------------------------------------------+-----------------------+
5 rows in set (0.01 sec)

修改MySQL参数

[root@mgr60 /]# cat > /etc/my.cnf <<"EOF" 
[mysqld] 
user=mysql 
port=3306 
character_set_server=utf8mb4 
secure_file_priv= 
server-id = 802233060 
default-time-zone = '+8:00' 
log_timestamps = SYSTEM 
log-bin = 
binlog_format=row 
binlog_checksum=NONE 
skip-name-resolve 
log_slave_updates = 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=lhrmgr60-relay-bin   
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= "192.168.68.60:33061" 
loose-group_replication_group_seeds= "192.168.68.60:33061,192.168.68.61:33061,192.168.68.62:33061" 
loose-group_replication_bootstrap_group=OFF 
loose-group_replication_ip_whitelist="192.168.68.60,192.168.68.61,192.168.68.62"   
report_host=192.168.68.60 
report_port=3306   
EOF
[root@mgr60 /]# systemctl restart mysqld
[root@mgr60 /]# mysql -uroot -pwcb -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                        |
+------------+-------------+--------------------------------------+
| mgr60      |   802233060 | 92e7d67b-4c3b-11ed-a1a5-0242ac110004 |
+------------+-------------+--------------------------------------+

[root@mgr61 /]# cat >  /etc/my.cnf <<"EOF" 
[mysqld] 
user=mysql 
port=3306 
character_set_server=utf8mb4 
secure_file_priv= 
server-id = 802233061 
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=lhrmgr61-relay-bin   
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= "192.168.68.61:33061" 
loose-group_replication_group_seeds= "192.168.68.60:33061,192.168.68.61:33061,192.168.68.62:33061" 
loose-group_replication_bootstrap_group=OFF 
loose-group_replication_ip_whitelist="192.168.68.60,192.168.68.61,192.168.68.62"   
report_host=192.168.68.61 
report_port=3306  
EOF
[root@mgr61 /]# systemctl restart mysqld
[root@mgr61 /]# mysql -uroot -pwcb -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                        |
+------------+-------------+--------------------------------------+
| mgr61      |   802233061 | 0c738122-4c3c-11ed-a575-0242ac110005 |
+------------+-------------+--------------------------------------+

[root@mgr62 /]# cat > /etc/my.cnf <<"EOF" 
[mysqld] 
user=mysql 
port=3306 
character_set_server=utf8mb4 
secure_file_priv= 
server-id = 802233062 
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=lhrmgr62-relay-bin   
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= "192.168.68.62:33061" 
loose-group_replication_group_seeds= "192.168.68.60:33061,192.168.68.61:33061,192.168.68.62:33061" 
loose-group_replication_bootstrap_group=OFF 
loose-group_replication_ip_whitelist="192.168.68.60,192.168.68.61,192.168.68.62"   
report_host=192.168.68.62 report_port=3306   
EOF
[root@mgr62 /]# systemctl restart mysqld
[root@mgr62 /]# mysql -uroot -pwcb -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                        |
+------------+-------------+--------------------------------------+
| mgr62      |   802233062 | 6adef8e0-4c3c-11ed-af3b-0242ac110006 |
+------------+-------------+--------------------------------------+

安装MGR插件

[root@mgr60 /]# mysql -uroot -pwcb
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.06 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.01 sec)
mysql> show plugins;

[root@mgr61 /]# mysql -uroot -pwcb
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.05 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.02 sec)

mysql> show plugins;

[root@mgr62 /]# mysql -uroot -pwcb
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.07 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.02 sec)

mysql> show plugins;

设置复制账号

[root@mgr60 /]# mysql -uroot -pwcb
mysql> CREATE USER repl@'%' IDENTIFIED BY 'wcb';
Query OK, 0 rows affected (0.06 sec)

mysql> GRANT REPLICATION SLAVE,BACKUP_ADMIN ON *.* TO repl@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='wcb' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

[root@mgr61 /]# mysql -uroot -pwcb
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='wcb' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

[root@mgr62 /]# mysql -uroot -pwcb
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='wcb' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.04 sec)

启动MGR(主节点)

[root@mgr60 /]# mysql -uroot -pwcb
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.20 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 92e7d67b-4c3b-11ed-a1a5-0242ac110004 | 192.168.68.60 |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

其他节点加入MGR(其他节点)

[root@mgr61 /]# mysql -uroot -pwcb
mysql> reset master;
Query OK, 0 rows affected (0.04 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (3.51 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 0c738122-4c3c-11ed-a575-0242ac110005 | 192.168.68.61 |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
| group_replication_applier | 92e7d67b-4c3b-11ed-a1a5-0242ac110004 | 192.168.68.60 |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
2 rows in set (0.01 sec)

[root@mgr62 /]# mysql -uroot -pwcb
mysql> reset master;
Query OK, 0 rows affected (0.06 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.87 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 0c738122-4c3c-11ed-a575-0242ac110005 | 192.168.68.61                  |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
| group_replication_applier | 6adef8e0-4c3c-11ed-af3b-0242ac110006 | 192.168.68.62 report_port=3306 |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
| group_replication_applier | 92e7d67b-4c3b-11ed-a1a5-0242ac110004 | 192.168.68.60                  |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
3 rows in set (0.03 sec)

测试同步

[root@mgr60 /]# mysql -uroot -pwcb
mysql> create database db1;
Query OK, 1 row affected (0.09 sec)

mysql> CREATE TABLE db1.`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;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> insert into db1.tb1(hostname,server_id) select @@hostname,@@server_id;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from db1.tb1;
+----+----------+-----------+
| id | hostname | server_id |
+----+----------+-----------+
|  1 | mgr60    | 802233060 |
+----+----------+-----------+
1 row in set (0.01 sec)

[root@mgr61 /]# mysql -uroot -pwcb
mysql> select * from db1.tb1;
+----+----------+-----------+
| id | hostname | server_id |
+----+----------+-----------+
|  1 | mgr60    | 802233060 |
+----+----------+-----------+
1 row in set (0.02 sec)

[root@mgr62 /]# mysql -uroot -pwcb
mysql> select * from db1.tb1;
+----+----------+-----------+
| id | hostname | server_id |
+----+----------+-----------+
|  1 | mgr60    | 802233060 |
+----+----------+-----------+
1 row in set (0.02 sec)

这里可以看到MGR已经搭建完成，主库的数据可以同步到从库。

其他MGR相关操作

删除节点

mysql> stop GROUP_REPLICATION;
Query OK, 0 rows affected (4.60 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 8818cc27-4d2a-11ed-ae32-0242ac110004 | 192.168.68.62 report_port=3306 |        3306 | OFFLINE      |             |                |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

手工切换主节点

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 2d2ef770-4d2a-11ed-af9d-0242ac110002 | 192.168.68.60                  |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
| group_replication_applier | 6367f2b8-4d2a-11ed-bed1-0242ac110003 | 192.168.68.61                  |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
| group_replication_applier | 8818cc27-4d2a-11ed-ae32-0242ac110004 | 192.168.68.62 report_port=3306 |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
3 rows in set (0.03 sec)

mysql> select group_replication_set_as_primary('6367f2b8-4d2a-11ed-bed1-0242ac110003');
+--------------------------------------------------------------------------+
| group_replication_set_as_primary('6367f2b8-4d2a-11ed-bed1-0242ac110003') |
+--------------------------------------------------------------------------+
| Primary server switched to: 6367f2b8-4d2a-11ed-bed1-0242ac110003         |
+--------------------------------------------------------------------------+
1 row in set (1.06 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 2d2ef770-4d2a-11ed-af9d-0242ac110002 | 192.168.68.60                  |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
| group_replication_applier | 6367f2b8-4d2a-11ed-bed1-0242ac110003 | 192.168.68.61                  |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
| group_replication_applier | 8818cc27-4d2a-11ed-ae32-0242ac110004 | 192.168.68.62 report_port=3306 |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

模拟主库停止后自动切换主节点

mysql>  SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 2d2ef770-4d2a-11ed-af9d-0242ac110002 | 192.168.68.60                  |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
| group_replication_applier | 6367f2b8-4d2a-11ed-bed1-0242ac110003 | 192.168.68.61                  |        3306 | UNREACHABLE  | PRIMARY     | 8.0.22         |
| group_replication_applier | 8818cc27-4d2a-11ed-ae32-0242ac110004 | 192.168.68.62 report_port=3306 |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+

停止主库

[root@mysql ~]# docker stop mysql8022mgr33061
mysql8022mgr33061

等待一会后发现已经进行切换

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 2d2ef770-4d2a-11ed-af9d-0242ac110002 | 192.168.68.60                  |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
| group_replication_applier | 8818cc27-4d2a-11ed-ae32-0242ac110004 | 192.168.68.62 report_port=3306 |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
+---------------------------+--------------------------------------+--------------------------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)
MambaCloud
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
金融行业MySQL高可用实践-明溪源
12-17
明溪源介绍 金融行业MySQL高可用实践 1)金融行业MySQL高可用方案选型 a)MySQL Replication – MHA (MySQL5.7不支持) b)MGR + 轻量级中间件MySQL Router统一访问 2)爱可生金融级MySQL高可用方案 3)某大行核心系统MySQL应用实践
MySQL之MGR原理+保姆级配置部署
weixin_44508518的博客
09-12 2978
如上图所示，由3个节点组成一个复制组，Consensus层为一致 性协议层，在事务提交过程中，发生组间通讯，由2个节点决议(certify)通过这个事务，事务 才能够最终得以提交并响应。**高灵活性：**提供单主模式和多主模式，单主模式在主库宕机后能够自动选主，所有写入都在主 节点进行，多主模式支持多节点写入。**高扩展性：**节点的增加与移除会自动更新组成员信息，新节点加入后，自动从其他节点同步增 量数据，直到与其他节点数据一致；**高一致性：**基于分布式paxos协议实现组复制，保证数据一致性；
MySql高可用集群之MGR（MySQL Group Replication）
最新发布
weixin_44112884的博客
04-11 630
--- MGR （MySQL Group Replication）是MySQL自带的一个插件，可以灵活部署。MySQL MGR集群是多个MySQL Server节点共同组成的分布式集群，每个Server都有完整的副本，它是基于ROW格式的二进制日志文件和GTID特性。架构主要是APIs层、组件层、复制协议模块层和GCS API+Paxos引擎层构成。
Mysql数据库管理--高可用集群MGR实战1
oradbm的博客
05-11 1233
MySQL Group Replication(MGR)是MySQL官方于2016年12月推出的一个全新的高可用与高扩展的解决方案，MGR是基于原生复制及paxos协议的组复制技术，并以插件的方式提供，可以采取多主模式和单主模式，单主模式下，会自动选主，所有更新操作都在主上进行，多主模式下，所有server都可以同时处理更新操作。下面我们就来搭建下MGR集群（单主模式）。环境清单gtid_mode=on #开启全局事务。
MYSQL--架构--MGR--部署--01--单主模式
zhou920786312的博客
06-01 1123
| 角色 | IP | 主机名 |server_id |mysql版本 | 备注 | | :------- | :------- | :------- |:------- |:------- | | master | 192.168.187.177 | mysql01 | 1 | 8.0.22 | 主服务器,提供读写操作 | | slave | 192.168.187.178 | mysql02 | 2 | 8.0.22 | 从服务器,提供读操作 | | slave
MySQL MGR 联机配置
只是甲的博客
09-02 893
文章目录一.配置组复制模式1.1 单主模式1.2 多主模式1.3 联机配置组复制模式1.3.1 单主模式下修改主服务器1.3.2 单主修改为多主1.3.3 多主修改为单主1.4 配置并发写实例数1.5 设置组的通信协议版本二.保证数据一致性2.1 组复制数据一致性简介2.1.1 只读保护2.1.2 主库故障转移2.2 一致性级别介绍2.2.1 EVENTUAL（缺省值）2.2.2 BEFORE_ON_PRIMARY_FAILOVER2.2.3 BEFORE(读时同步)2.2.4 AFTER（写时同步）2.2
MYSQL--架构--MGR--部署--03--配置
zhou920786312的博客
06-01 903
loose 前缀的意义在于第一次启动时还没加载组复制的plugin,可以让mysql server忽略该参数,继续启动。尝试从供体池连接到供体时，加入该组的服务器尝试的次数是10启动时加载插件，这里配置的是组复制的插件。是否使用克隆插件。Mysql服务ID将master的连接信息写入表：mysql.salve_master_info将中继日志的信息写入表:mysql.slave_realy_log_info从服务器 从 主服务器 收到的更新 记入到 从服务器 自己的二进制日志文件中。以行的格式记录将MySQ
详解mysql数据库官方高可用方案——MGR理论篇
m0_69804655的博客
03-08 1438
一个服务器被怀疑意味这该服务器无响应(mute)，当服务器A在一段时间内为收到服务器B的信息，一个超时异常发生并且服务器B会被标记为 suspicion状态，这意味着，组内其他的成员服务器会协调将其踢出复制组，如果一个服务器无法和其余的服务器通信，则他会怀疑其他服务器都故障了.由于其服务器和组内其他服务器达成一致，它自身的怀疑是没有结果的，这时他无法执行任何本地事务。重新配置过程中需要大多数节点同意，即组内故障服务器低于大多数，否则视图无法更新且会阻塞事务的执行以防止脑裂的发生，这时就需要人为的干预了。
mysql实战之mgr集群
qq_62789440的博客
01-10 2512
MGR 是一个新的高可用与高扩展的方案，集群中的任何节点数据都是一样的，可以实现任何节点都可以写入，实现了真正意义上的多主。
MySQL MGR
summer_fish的专栏
11-08 3226
ProxySQL 介绍 ProxySQL 是基于 MySQL 的一款开源的中间件的产品，是一个灵活的 MySQL 代理层，可以实现读写分离，支持 Query 路由功能，支持动态指定某个 SQL 进行缓存，支持动态加载（无需重启 ProxySQL 服务），故障切换和一些 SQL 的过滤功能。 MGR 单主 + VIP MGR MGR （MySQL Group Replication）是MySQL自带的一个插件，可以灵活部署。MySQL ...
mysql mgr集群部署
ApexPredator的博客
02-19 968
mysql mgr集群部署
MYSQL高可用-MGR+MysqlRouter安装文档-20200416.docx
04-23
该文档详细的想你介绍如何一步一步搭建mysql innodb cluster +mysqlrouter 实现mysql的高可用，小白也可以按照文档搭建高可用架构
docker-mysql高可用负载均衡集群
01-09
安装docker 安装PXC镜像 docker pull percona/percona-xtradb-cluster 本地安装PXC镜像 docker load < /home/soft/pxc.tar.gz 查看docker安装的镜像 docker images ...docker tag percona/percona-xtradb-cluster ...
2-5、MySQL高可用架构演进-吴炳锡@知数堂.pdf
12-16
MySQL高可用架构演进-吴炳锡。 1. 什么是高可用 2. 第一代传统复制: MHA及使用架构 3. 第二代基于GTID复制:GTID+Binlog server At Booking & Facebook 4. 第三代增强半同步复制:GTID+增强半同步及多IDC架构及...
Oracle和MySQL的高可用方案对比分析
12-16
关于Oracle和MySQL的高可用方案，其实一直想要总结了，就会分为几个系列来简单说说。通过这样的对比，会对两种数据库架构设计上的细节差异有一个基本的认识。Oracle有一套很成熟的解决方案。用我在OOW上的ppt来看，...
MySQL+MGR 单主模式和多主模式的集群环境 - 部署手册 (Centos7.5)
MyDriverC
01-29 469
MySQL Group Replication（简称MGR）是MySQL官方于2016年12月推出的一个全新的高可用与高扩展的解决方案。MGR是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案，以插件形式提供，实现了分布式下数据的最终一致性, 它是MySQL5.7版本出现的新特性，它提供了高可用、高扩展、高可靠的MySQL集群服务。MySQL组复制分单主模式和多主模式，mysql 的复制技术仅解决了数据同步的问题，如果 master 宕机，意味着数据库管理员需要介入，应用系统可能需要
MySQL8.0 MGR介绍
sinat_36757755的博客
05-02 4206
常见的数据库高可用是通过创建计算数据节点冗余来确保数据库的节点宕机不会影响系统的运行，但这会导致数据库的架构复杂，运维成本提升，此外，还需要解决分布式可能所带来的脑裂，数据一致性问题。 MySQL MGR插件基于分布式Paxos算法，协调多个数据库节点进行数据复制，事务一致性检验，故障自动检测选主，节点添加删除，MGR有两种模式，单主模式以及多主模式。
MySQL5.7 MGR 集群搭建
热门推荐
高飞的博客
06-16 12万+
mysql mgr 集群
MySQL MGR安装部署
sinat_36757755的博客
05-22 1447
部署环境: 模式 单主模式 主节点(8.0.22) 192.168.1.240 备节点(8.0.22) 192.168.1.241 备节点(8.0.22) 192.168.1.242 MGR配置以及限制: 数据库版本MySQL 5.7.17及以上。 InnoDB存储引擎:数据必须存储在 InnoDB事务存储引擎中。 disabled_storage_engines="MyISAM,BLACKHOLE,FEDERA
mysql 实施MGR高可用
08-30
MySQL提供了MGR（MySQL Group Replication）来实现高可用性。 以下是一些实施MGR高可用的步骤： 1. 配置MySQL实例：在每个参与MGR的MySQL实例上，需要启用二进制日志（binlog），并设置一个唯一的服务器ID。 2. ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值