[MySQL High Availability] MHA for MySQL High Availability

[MySQL High Availability] MHA for MySQL High Availability

Reference: https://www.xmmup.com/dbbao19zaidockerzhongshiyongmysqlgaokeyongzhimha.html

Introduction to MHA

MHA (Master High Availability Manager and tools for MySQL) is currently a relatively mature solution for MySQL high availability. MHA is an excellent set of high-availability software for failover and master-slave promotion in MySQL high-availability environment. MHA is only applicable to the MySQL Replication environment, and the purpose is to maintain the high availability of the Master master database. In the process of MySQL failover, MHA can automatically complete the failover operation of the database within 0~30 seconds, and in the process of failover, MHA can ensure the consistency of the database to the greatest extent, so as to achieve the true meaning high availability.

Components of the MHA Toolkit

​ MHA consists of two parts: MHA Manager (management node) and MHA Node (data node).

Manager toolkit situation

  • masterha_check_ssh: Check the SSH configuration of MHA
  • masterha_check_repl: Check MySQL replication status
  • masterha_manager: start MHA
  • masterha_check_status: Check the current MHA running status
  • masterha_master_monitor: detects whether the Master is down
  • masterha_master_switch: control failover (automatic or manual)
  • masterha_conf_host: Add or delete configured server information

Node Toolkit

  • save_binary_logs: Save and copy the Master's binlog log.
  • apply_diff_relay_logs : Identify diff relay log times and apply them to other slave s
  • purge_relay_logs: Purge relay logs (do not block SQL thread)

MHA Architecture

IPCPU nameeffectServer IDporttypeRemark
192.168.68.131MHA-LHR-Master1-ip131master node5733061313306writeProvide external writing services
192.168.68.132MHA-LHR-Slave1-ip132slave node1 (Candicate Master)573306132readAlternative Master provides reading service
192.168.68.133MHA-LHR-Slave2-ip133slave node2573306133readProvide reading service
192.168.68.134MHA-LHR-Monitor-ip134Monitor hostMonitor other machines, once Mater goes down, it will upgrade the candidate Master to the new Master, and point the Slave to the new Master
192.168.68.135VIPFloat drift between 131 and 132

Prepare the MHA environment

Download MHA image

  • Address of Docker Hub of Wheat Miao: https://hub.docker.com/u/lhrbest
-- Download mirror
[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
-- rename image
[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 ~]#

Edit yml file to create MHA related container

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

Create MHA container

[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

Initialize the MHA environment

Add network card

[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

Modify the hosts file of the Manager node

[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

Main library 131 Add 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

Manage nodes to verify VIP network

[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 /]#

Start the replication process from the node

[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

The MHA environment is built.

Tags: Database MySQL

Posted by vidago on Sat, 15 Oct 2022 22:47:26 +0530

Hot Tags

©2023 Programs Team