[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
IP | CPU name | effect | Server ID | port | type | Remark |
---|---|---|---|---|---|---|
192.168.68.131 | MHA-LHR-Master1-ip131 | master node | 573306131 | 3306 | write | Provide external writing services |
192.168.68.132 | MHA-LHR-Slave1-ip132 | slave node1 (Candicate Master) | 573306132 | read | Alternative Master provides reading service | |
192.168.68.133 | MHA-LHR-Slave2-ip133 | slave node2 | 573306133 | read | Provide reading service | |
192.168.68.134 | MHA-LHR-Monitor-ip134 | Monitor host | Monitor 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.135 | VIP | Float 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.