【MySQL高可用】MySQL高可用之MHA

最新推荐文章于 2024-03-05 10:30:47 发布

MambaCloud

最新推荐文章于 2024-03-05 10:30:47 发布

阅读量403

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_49756466/article/details/127341974

版权

MySQL 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

【MySQL高可用】MySQL高可用之MHA

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

MHA简介

MHA（Master High Availability Manager and tools for MySQL）目前在MySQL高可用方面是一个相对成熟的解决方案。MHA是一套优秀的作为MySQL高可用环境下故障切换和主从提升的高可用软件。MHA仅适用于MySQL Replication环境，目的在于维持Master主库的高可用性。在MySQL故障切换过程中，MHA能做到0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能最大程度上保证数据库的一致性，以达到真正意义上的高可用。

MHA工具包的组成

MHA由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。

Manager工具包情况

masterha_check_ssh:检查MHA的SSH配置情况
masterha_check_repl:检查MySQL复制状况
masterha_manager:启动MHA
masterha_check_status:检测当前MHA运行状态
masterha_master_monitor:检测Master是否宕机
masterha_master_switch:控制故障转移（自动或手动）
masterha_conf_host:添加或删除配置的server信息

Node工具包

save_binary_logs:保存和复制Master的binlog日志。
apply_diff_relay_logs:识别差异的中继日志时间并将其应用到其他slave
purge_relay_logs:清除中继日志（不阻塞SQL线程）

MHA架构

IP	主机名	作用	Server ID	port	类型	备注
192.168.68.131	MHA-LHR-Master1-ip131	master node	573306131	3306	写入	对外提供写服务
192.168.68.132	MHA-LHR-Slave1-ip132	slave node1 （Candicate Master）	573306132		读	备选Master提供读服务
192.168.68.133	MHA-LHR-Slave2-ip133	slave node2	573306133		读	提供读服务
192.168.68.134	MHA-LHR-Monitor-ip134	Monitor host				监控其它机器，一旦Mater宕机，将会把备选Master提升为新的Master，而将Slave指向新的Master
192.168.68.135		VIP				在131和132之间进行浮动漂移

准备MHA环境

下载MHA镜像

小麦苗的Docker Hub的地址：https://hub.docker.com/u/lhrbest

-- 下载镜像
[root@mysql ~]# docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-master1-ip131
[root@mysql ~]# docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave1-ip132
[root@mysql ~]# docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave2-ip133 
[root@mysql ~]# docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-monitor-ip134
-- 重命名镜像
[root@mysql ~]# docker tag registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-master1-ip131 lhrbest/mha-lhr-master1-ip131
[root@mysql ~]# docker tag registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave1-ip132 lhrbest/mha-lhr-slave1-ip132
[root@mysql ~]# docker tag registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave2-ip133 lhrbest/mha-lhr-slave2-ip133
[root@mysql ~]# docker tag registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-monitor-ip134 lhrbest/mha-lhr-monitor-ip134

[root@mysql ~]# docker images | grep mha
lhrbest/mha-lhr-monitor-ip134                                     latest              7d29597dc997        2 years ago         1.53GB
registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-monitor-ip134   latest              7d29597dc997        2 years ago         1.53GB
lhrbest/mha-lhr-slave2-ip133                                      latest              d3717794e93a        2 years ago         4.56GB
registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave2-ip133    latest              d3717794e93a        2 years ago         4.56GB
lhrbest/mha-lhr-slave1-ip132                                      latest              f62ee813e487        2 years ago         4.56GB
registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave1-ip132    latest              f62ee813e487        2 years ago         4.56GB
lhrbest/mha-lhr-master1-ip131                                     latest              ae7be48d83dc        2 years ago         4.56GB
registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-master1-ip131   latest              ae7be48d83dc        2 years ago         4.56GB
[root@mysql ~]#

编辑yml文件，创建MHA相关容器

version: '3.8'  

services:
    MHA-LHR-Master1-ip131:
        container_name: "MHA-LHR-Master1-ip131"
        restart: "always"
        hostname: MHA-LHR-Master1-ip131
        privileged: true
        image: lhrbest/mha-lhr-master1-ip131
        ports:
            - "33061:3306"
            - "2201:22"
        networks:       
            mhalhr:         
                ipv4_address: 192.168.68.131

    MHA-LHR-Slave1-ip132:     
        container_name: "MHA-LHR-Slave1-ip132"
        restart: "always"     
        hostname: MHA-LHR-Slave1-ip132     
        privileged: true     
        image: lhrbest/mha-lhr-slave1-ip132     
        ports:       
            - "33062:3306"       
            - "2202:22"     
        networks:       
            mhalhr:         
                ipv4_address: 192.168.68.132     

    MHA-LHR-Slave2-ip133:     
        container_name: "MHA-LHR-Slave2-ip133"     
        restart: "always"     
        hostname: MHA-LHR-Slave2-ip133     
        privileged: true     
        image: lhrbest/mha-lhr-slave2-ip133     
        ports:       
            - "33063:3306"       
            - "2203:22"     
        networks:       
            mhalhr:         
                ipv4_address: 192.168.68.133     

    MHA-LHR-Monitor-ip134:     
        container_name: "MHA-LHR-Monitor-ip134"     
        restart: "always"     
        hostname: MHA-LHR-Monitor-ip134     
        privileged: true     
        image: lhrbest/mha-lhr-monitor-ip134     
        ports:       
            - "33064:3306"       
            - "2204:22"     
        networks:       
            mhalhr:         
                ipv4_address: 192.168.68.134
networks:
    mhalhr:     
        name: mhalhr     
        ipam:       
            config:          
                - subnet: "192.168.68.0/16"

[root@mysql ~]# curl --insecure -L https://github.com/docker/compose/releases/download/1.26.2/docker-compose-Linux-x86_64 -o /usr/local/bin/docker-compose
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
100 11.6M  100 11.6M    0     0  4818k      0  0:00:02  0:00:02 --:--:-- 13.7M
[root@mysql ~]# chmod +x /usr/local/bin/docker-compose
[root@mysql ~]# docker-compose -v
docker-compose version 1.26.2, build eefe0d31

创建MHA容器

[root@mysql mha]# docker-compose up -d
Creating network "mhalhr" with the default driver
Creating MHA-LHR-Slave1-ip132  ... done
Creating MHA-LHR-Monitor-ip134 ... done
Creating MHA-LHR-Master1-ip131 ... done
Creating MHA-LHR-Slave2-ip133  ... done
[root@mysql mha]# docker ps
CONTAINER ID        IMAGE                           COMMAND             CREATED             STATUS              PORTS                                                            NAMES
57dcf79de2d0        lhrbest/mha-lhr-slave2-ip133    "/usr/sbin/init"    21 seconds ago      Up 20 seconds       16500-16599/tcp, 0.0.0.0:2203->22/tcp, 0.0.0.0:33063->3306/tcp   MHA-LHR-Slave2-ip133
7f35910ed0b7        lhrbest/mha-lhr-slave1-ip132    "/usr/sbin/init"    21 seconds ago      Up 20 seconds       16500-16599/tcp, 0.0.0.0:2202->22/tcp, 0.0.0.0:33062->3306/tcp   MHA-LHR-Slave1-ip132
35da3dc24b7e        lhrbest/mha-lhr-master1-ip131   "/usr/sbin/init"    21 seconds ago      Up 20 seconds       16500-16599/tcp, 0.0.0.0:2201->22/tcp, 0.0.0.0:33061->3306/tcp   MHA-LHR-Master1-ip131
cd08ee68d1a3        lhrbest/mha-lhr-monitor-ip134   "/usr/sbin/init"    21 seconds ago      Up 20 seconds       0.0.0.0:2204->22/tcp, 0.0.0.0:33064->3306/tcp                    MHA-LHR-Monitor-ip134

初始化MHA环境

添加网卡

[root@mysql mha]# docker network connect bridge MHA-LHR-Master1-ip131
[root@mysql mha]# docker network connect bridge MHA-LHR-Slave1-ip132
[root@mysql mha]# docker network connect bridge MHA-LHR-Slave2-ip133
[root@mysql mha]# docker network connect bridge MHA-LHR-Monitor-ip134

修改Manager节点的hosts文件

[root@mysql mha]# docker exec -it MHA-LHR-Monitor-ip134 bash
[root@MHA-LHR-Monitor-ip134 /]# cat >> /etc/hosts << EOF 
192.168.68.131 MHA-LHR-Master1-ip131 
192.168.68.132 MHA-LHR-Slave1-ip132 
192.168.68.133 MHA-LHR-Slave2-ip133 
192.168.68.134 MHA-LHR-Monitor-ip134 
EOF

主库131添加VIP

[root@mysql mha]# docker exec -it MHA-LHR-Master1-ip131 bash
[root@MHA-LHR-Master1-ip131 /]# /sbin/ifconfig eth0:1 192.168.68.135/24
[root@MHA-LHR-Master1-ip131 /]# ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.68.131  netmask 255.255.0.0  broadcast 192.168.255.255
        ether 02:42:c0:a8:44:83  txqueuelen 0  (Ethernet)
        RX packets 66  bytes 5866 (5.7 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 51  bytes 5512 (5.3 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.68.135  netmask 255.255.255.0  broadcast 192.168.68.255
        ether 02:42:c0:a8:44:83  txqueuelen 0  (Ethernet)

eth1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.17.0.2  netmask 255.255.0.0  broadcast 172.17.255.255
        ether 02:42:ac:11:00:02  txqueuelen 0  (Ethernet)
        RX packets 8  bytes 656 (656.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

管理节点验证VIP网络

[root@mysql ~]# docker exec -it  MHA-LHR-Monitor-ip134 bash
[root@MHA-LHR-Monitor-ip134 /]# ping 192.168.68.135
PING 192.168.68.135 (192.168.68.135) 56(84) bytes of data.
64 bytes from 192.168.68.135: icmp_seq=1 ttl=64 time=0.109 ms
64 bytes from 192.168.68.135: icmp_seq=2 ttl=64 time=0.049 ms
^C
--- 192.168.68.135 ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1003ms
rtt min/avg/max/mdev = 0.049/0.079/0.109/0.030 ms
[root@MHA-LHR-Monitor-ip134 /]#

从节点启动复制进程

[root@mysql ~]# docker exec -it  MHA-LHR-Slave2-ip133 bash
[root@MHA-LHR-Slave2-ip133 /]# mysql -uroot -plhr 
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 4
Server version: 5.7.30-log 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> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.68.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: MHA-LHR-Master1-ip131-bin.000011
          Read_Master_Log_Pos: 234
               Relay_Log_File: MHA-LHR-Slave2-ip133-relay-bin.000023
                Relay_Log_Pos: 399
        Relay_Master_Log_File: MHA-LHR-Master1-ip131-bin.000011
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: information_schema,performance_schema,mysql,sys
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 234
              Relay_Log_Space: 1856
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 573306131
                  Master_UUID: c8ca4f1d-aec3-11ea-942b-0242c0a84483
             Master_Info_File: /usr/local/mysql-5.7.30-linux-glibc2.12-x86_64/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: c8ca4f1d-aec3-11ea-942b-0242c0a84483:1-11,
d24a77d1-aec3-11ea-9399-0242c0a84484:1-3,
d391ce7e-aec3-11ea-94cd-0242c0a84485:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

[root@mysql ~]# docker exec -it  MHA-LHR-Slave1-ip132  bash
[root@MHA-LHR-Slave1-ip132 /]# mysql -uroot -plhr 
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 4
Server version: 5.7.30-log 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> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.68.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: MHA-LHR-Master1-ip131-bin.000011
          Read_Master_Log_Pos: 234
               Relay_Log_File: MHA-LHR-Slave1-ip132-relay-bin.000036
                Relay_Log_Pos: 399
        Relay_Master_Log_File: MHA-LHR-Master1-ip131-bin.000011
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: information_schema,performance_schema,mysql,sys
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 234
              Relay_Log_Space: 1856
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 573306131
                  Master_UUID: c8ca4f1d-aec3-11ea-942b-0242c0a84483
             Master_Info_File: /usr/local/mysql-5.7.30-linux-glibc2.12-x86_64/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: c8ca4f1d-aec3-11ea-942b-0242c0a84483:1-11,
d24a77d1-aec3-11ea-9399-0242c0a84484:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified