如何利用MHA+ProxySQL实现读写分离和负载均衡

转x33g5p2x  于2022-04-07 转载在 其他  
字(21.1k)|赞(0)|评价(0)|浏览(550)

本文分享自华为云社区《MySQL高可用架构MHA+ProxySQL实现读写分离和负载均衡》，作者：小麦苗DB宝。

一、MHA+ProxySQL架构

我们都知道，MHA（Master High Availability Manager and tools for MySQL）目前在MySQL高可用方面是一个相对成熟的解决方案，是一套作为MySQL高可用性环境下故障切换和主从提升的高可用软件。它的架构是要求一个MySQL复制集群必须最少有3台数据库服务器，一主二从，即一台充当Master，一台充当备用Master，另一台充当从库。但是，如果不连接任何外部的数据库中间件，那么就会导致所有的业务压力流向主库，从而造成主库压力过大，而2个从库除了本身的IO和SQL线程外，无任何业务压力，会严重造成资源的浪费。因此，我们可以把MHA和ProxySQL结合使用来实现读写分离和负载均衡。所有的业务通过中间件ProxySQL后，会被分配到不同的MySQL机器上。从而，前端的写操作会流向主库，而读操作会被负载均衡的转发到2个从库上。

MHA+ProxySQL架构如下图所示：

https://cdn.jsdelivr.net/gh/lhrbest/pic/img/20210416111756.png

二、快速搭建MHA环境

2.1 下载MHA镜像

-- 下载镜像

  1. docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-master1-ip131
  2. docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave1-ip132
  3. docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave2-ip133
  4. docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-monitor-ip134

-- 重命名镜像

  1. docker tag 	registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-master1-ip131  lhrbest/mha-lhr-master1-ip131
  2. docker tag	registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave1-ip132   lhrbest/mha-lhr-slave1-ip132 
  3. docker tag	registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave2-ip133   lhrbest/mha-lhr-slave2-ip133 
  4. docker tag	registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-monitor-ip134  lhrbest/mha-lhr-monitor-ip134

一共4个镜像，3个MHA Node，一个MHA Manager，压缩包大概3G，下载完成后：

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

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

编辑yml文件，使用docker-compose来创建MHA相关容器，注意docker-compose.yml文件的格式，对空格、缩进、对齐都有严格要求：

  1. # 创建存放yml文件的路径
  2. mkdir -p /root/mha
  4. # 编辑文件/root/mha/docker-compose.yml
  5. cat > /root/mha/docker-compose.yml <<"EOF"
  6. version: '3.8'
  8. services:
  9.   MHA-LHR-Master1-ip131:
  10.     container_name: "MHA-LHR-Master1-ip131"
  11.     restart: "always"
  12.     hostname: MHA-LHR-Master1-ip131
  13.     privileged: true
  14.     image: lhrbest/mha-lhr-master1-ip131
  15.     ports:
  16.       - "33131:3306"
  17.       - "2201:22"
  18.     networks:
  19.       mhalhr:
  20.         ipv4_address: 192.168.68.131
  22.   MHA-LHR-Slave1-ip132:
  23.     container_name: "MHA-LHR-Slave1-ip132"
  24.     restart: "always"
  25.     hostname: MHA-LHR-Slave1-ip132
  26.     privileged: true
  27.     image: lhrbest/mha-lhr-slave1-ip132
  28.     ports:
  29.       - "33132:3306"
  30.       - "2202:22"
  31.     networks:
  32.       mhalhr:
  33.         ipv4_address: 192.168.68.132
  35.   MHA-LHR-Slave2-ip133:
  36.     container_name: "MHA-LHR-Slave2-ip133"
  37.     restart: "always"
  38.     hostname: MHA-LHR-Slave2-ip133
  39.     privileged: true
  40.     image: lhrbest/mha-lhr-slave2-ip133
  41.     ports:
  42.       - "33133:3306"
  43.       - "2203:22"
  44.     networks:
  45.       mhalhr:
  46.         ipv4_address: 192.168.68.133
  48.   MHA-LHR-Monitor-ip134:
  49.     container_name: "MHA-LHR-Monitor-ip134"
  50.     restart: "always"
  51.     hostname: MHA-LHR-Monitor-ip134
  52.     privileged: true
  53.     image: lhrbest/mha-lhr-monitor-ip134
  54.     ports:
  55.       - "33134:3306"
  56.       - "2204:22"
  57.     networks:
  58.       mhalhr:
  59.         ipv4_address: 192.168.68.134
  61. networks:
  62.   mhalhr:
  63.     name: mhalhr
  64.     ipam:
  65.       config:
  66.          - subnet: "192.168.68.0/16"
  68. EOF

2.3 安装docker-compose软件（若已安装，可忽略）

  1. [root@lhrdocker ~]# curl --insecure -L https://github.com/docker/compose/releases/download/1.28.4/docker-compose-Linux-x86_64 -o /usr/local/bin/docker-compose
  2.   % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
  3.                                  Dload  Upload   Total   Spent    Left  Speed
  4. 100   638  100   638    0     0    530      0  0:00:01  0:00:01 --:--:--   531
  5. 100 11.6M  100 11.6M    0     0  1994k      0  0:00:06  0:00:06 --:--:-- 2943k
  6. [root@lhrdocker ~]# chmod +x /usr/local/bin/docker-compose
  7. [root@lhrdocker ~]# docker-compose -v
  8. docker-compose version 1.28.4, build cabd5cfb

2.4 创建MHA容器

  1. # 启动mha环境的容器，一定要进入文件夹/root/mha/后再操作
  2. -- docker rm -f MHA-LHR-Master1-ip131 MHA-LHR-Slave1-ip132 MHA-LHR-Slave2-ip133 MHA-LHR-Monitor-ip134
  3. [root@lhrdocker ~]# cd /root/mha/
  4. [root@lhrdocker mha]#
  5. [root@lhrdocker mha]# docker-compose up -d
  6. Creating network "mhalhr" with the default driver
  7. Creating MHA-LHR-Monitor-ip134 ... done
  8. Creating MHA-LHR-Slave2-ip133  ... done
  9. Creating MHA-LHR-Master1-ip131 ... done
  10. Creating MHA-LHR-Slave1-ip132  ... done
  11. [root@docker35 ~]# docker ps | grep "mha\|COMMAND" 
  12. CONTAINER ID        IMAGE                           COMMAND                  CREATED             STATUS              PORTS                                                                                           NAMES
  13. 2978361198b7        lhrbest/mha-lhr-master1-ip131   "/usr/sbin/init"         2 minutes ago       Up 2 minutes        16500-16599/tcp, 0.0.0.0:2201->22/tcp, 0.0.0.0:33131->3306/tcp                                  MHA-LHR-Master1-ip131
  14. a64e2e86589c        lhrbest/mha-lhr-slave1-ip132    "/usr/sbin/init"         2 minutes ago       Up 2 minutes        16500-16599/tcp, 0.0.0.0:2202->22/tcp, 0.0.0.0:33132->3306/tcp                                  MHA-LHR-Slave1-ip132
  15. d7d6ce34800b        lhrbest/mha-lhr-monitor-ip134   "/usr/sbin/init"         2 minutes ago       Up 2 minutes        0.0.0.0:2204->22/tcp, 0.0.0.0:33134->3306/tcp                                                   MHA-LHR-Monitor-ip134
  16. dacd22edb2f8        lhrbest/mha-lhr-slave2-ip133    "/usr/sbin/init"         2 minutes ago       Up 2 minutes        16500-16599/tcp, 0.0.0.0:2203->22/tcp, 0.0.0.0:33133->3306/tcp                                  MHA-LHR-Slave2-ip133

2.5 主库131添加VIP

  1. # 进入主库131
  2. docker exec -it MHA-LHR-Master1-ip131 bash
  4. # 添加VIP135
  5. /sbin/ifconfig eth0:1 192.168.68.135/24
  6. ifconfig
  8. # 如果删除的话
  9. ip addr del 192.168.68.135/24 dev eth1

添加完成后：

  1. [root@MHA-LHR-Master1-ip131 /]# ifconfig
  2. eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
  3.         inet 192.168.68.131  netmask 255.255.0.0  broadcast 192.168.255.255
  4.         ether 02:42:c0:a8:44:83  txqueuelen 0  (Ethernet)
  5.         RX packets 220  bytes 15883 (15.5 KiB)
  6.         RX errors 0  dropped 0  overruns 0  frame 0
  7.         TX packets 189  bytes 17524 (17.1 KiB)
  8.         TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
  10. eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
  11.         inet 192.168.68.135  netmask 255.255.255.0  broadcast 192.168.68.255
  12.         ether 02:42:c0:a8:44:83  txqueuelen 0  
  13. lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
  14.         inet 127.0.0.1  netmask 255.0.0.0
  15.         loop  txqueuelen 1000  (Local Loopback)
  16.         RX packets 5  bytes 400 (400.0 B)
  17.         RX errors 0  dropped 0  overruns 0  frame 0
  18.         TX packets 5  bytes 400 (400.0 B)
  19.         TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
  20.  
  21. # 管理节点已经可以ping通VIP了
  22. [root@MHA-LHR-Monitor-ip134 /]# ping 192.168.68.135
  23. PING 192.168.68.135 (192.168.68.135) 56(84) bytes of data.
  24. 64 bytes from 192.168.68.135: icmp_seq=1 ttl=64 time=0.172 ms
  25. 64 bytes from 192.168.68.135: icmp_seq=2 ttl=64 time=0.076 ms
  26. ^C
  27. --- 192.168.68.135 ping statistics ---
  28. 2 packets transmitted, 2 received, 0% packet loss, time 1000ms
  29. rtt min/avg/max/mdev = 0.076/0.124/0.172/0.048 ms

到这一步就可以验证主从复制是否正确，若正确，则可以直接测试MHA了。

  1. mysql -uroot -plhr -h192.168.68.131 -P3306
  2. show slave hosts;
  3. mysql> show slave hosts;
  4. +-----------+----------------+------+-----------+--------------------------------------+
  5. | Server_id | Host           | Port | Master_id | Slave_UUID                           |
  6. +-----------+----------------+------+-----------+--------------------------------------+
  7. | 573306133 | 192.168.68.133 | 3306 | 573306131 | d391ce7e-aec3-11ea-94cd-0242c0a84485 |
  8. | 573306132 | 192.168.68.132 | 3306 | 573306131 | d24a77d1-aec3-11ea-9399-0242c0a84484 |
  9. +-----------+----------------+------+-----------+--------------------------------------+
  10. 2 rows in set (0.00 sec)

https://cdn.jsdelivr.net/gh/lhrbest/pic/img/20210416111806.png

三、配置ProxySQL环境

3.1 申请ProxySQL主机并安装ProxySQL

  1. docker rm -f MHA-LHR-ProxySQL-ip136
  2. docker run -d --name MHA-LHR-ProxySQL-ip136 -h MHA-LHR-ProxySQL-ip136 \
  3.   -v /sys/fs/cgroup:/sys/fs/cgroup \
  4.   --network mhalhr --ip 192.168.68.136 \
  5.   -p 26032:6032 -p 26033:6033 -p 26080:6080 \
  6.   --privileged=true lhrbest/lhrcentos76:8.0 \
  7.   /usr/sbin/init
  9. docker network connect bridge MHA-LHR-ProxySQL-ip136
  10. docker restart MHA-LHR-ProxySQL-ip136
  12. docker cp proxysql2-2.0.15-1.1.el7.x86_64.rpm MHA-LHR-ProxySQL-ip136:/
  13. docker exec -it MHA-LHR-ProxySQL-ip136 bash
  14. rpm -ivh proxysql2-2.0.15-1.1.el7.x86_64.rpm
  15. systemctl start proxysql
  16. systemctl status proxysql

3.2 添加远程登录用户

  1. -- 添加远程登录用户
  2. mysql -uadmin -padmin -h127.0.0.1 -P6032
  3. select @@admin-admin_credentials;
  4. set admin-admin_credentials='admin:admin;root:lhr';
  5. select @@admin-admin_credentials;
  6. load admin variables to runtime;
  7. save admin variables to disk;
  9. -- 远程登录
  10. mysql -uroot -plhr -h192.168.66.35 -P26032

执行过程：

  1. -- ProxySQL本地登录
  2. [root@MHA-LHR-ProxySQL-ip136 /]# mysql -uadmin -padmin -h127.0.0.1 -P6032
  3. mysql: [Warning] Using a password on the command line interface can be insecure.
  4. Welcome to the MySQL monitor.  Commands end with ; or \g.
  5. Your MySQL connection id is 162
  6. Server version: 5.5.30 (ProxySQL Admin Module)
  8. Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
  10. Oracle is a registered trademark of Oracle Corporation and/or its
  11. affiliates. Other names may be trademarks of their respective
  12. owners.
  14. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  16. mysql> select @@admin-admin_credentials;
  17. +---------------------------+
  18. | @@admin-admin_credentials |
  19. +---------------------------+
  20. | admin:admin;lhr:lhr       |
  21. +---------------------------+
  22. 1 row in set (0.05 sec)
  24. mysql> set admin-admin_credentials='admin:admin;root:lhr';
  25. Query OK, 1 row affected (0.00 sec)
  27. mysql> select @@admin-admin_credentials;
  28. +---------------------------+
  29. | @@admin-admin_credentials |
  30. +---------------------------+
  31. | admin:admin;root:lhr      |
  32. +---------------------------+
  33. 1 row in set (0.00 sec)
  35. mysql> load admin variables to runtime;
  36. Query OK, 0 rows affected (0.00 sec)
  38. mysql> save admin variables to disk;
  39. Query OK, 35 rows affected (0.13 sec)
  41. mysql> 
  43. -- 远程登录
  44. C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P26032
  45. mysql: [Warning] Using a password on the command line interface can be insecure.
  46. ERROR 1045 (28000): ProxySQL Error: Access denied for user 'root'@'172.17.0.1' (using password: YES)
  48. C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P26032
  49. mysql: [Warning] Using a password on the command line interface can be insecure.
  50. Welcome to the MySQL monitor.  Commands end with ; or \g.
  51. Your MySQL connection id is 163
  52. Server version: 5.5.30 (ProxySQL Admin Module)
  54. Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
  56. Oracle is a registered trademark of Oracle Corporation and/or its
  57. affiliates. Other names may be trademarks of their respective
  58. owners.
  60. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  62. MySQL [(none)]> show databases;
  63. +-----+---------------+-------------------------------------+
  64. | seq | name          | file                                |
  65. +-----+---------------+-------------------------------------+
  66. | 0   | main          |                                     |
  67. | 2   | disk          | /var/lib/proxysql/proxysql.db       |
  68. | 3   | stats         |                                     |
  69. | 4   | monitor       |                                     |
  70. | 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
  71. +-----+---------------+-------------------------------------+
  72. 5 rows in set (0.05 sec)

3.3 开启ProxySQL的web监控功能

  1. -- 开启web监控功能
  2. SET admin-web_enabled='true';
  3. LOAD ADMIN VARIABLES TO RUNTIME;
  4. SAVE ADMIN VARIABLES TO DISK;
  5. select * from global_variables where variable_name LIKE 'admin-web_enabled';
  6. select @@admin-web_enabled;
  8. lsof -i:6080
  10. -- 浏览器访问
  11. https://192.168.66.35:26080
  12. 用户名和密码：stats:stats

https://cdn.jsdelivr.net/gh/lhrbest/pic/img/20210416111815.png

3.4 配置被监控的数据库

3.4.1 向ProxySQL插入被监控数据库

  1. -- 1、向ProxySQL插入被监控数据库
  2. select * from mysql_servers;
  3. insert into main.mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.68.131',3306);
  4. insert into main.mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.68.132',3306);
  5. insert into main.mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.68.133',3306);
  6. load mysql servers to runtime;
  7. save mysql servers to disk;
  8. select * from mysql_servers;
  9. MySQL [(none)]> select * from mysql_servers;
  10. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  11. | hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
  12. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  13. | 10           | 192.168.68.131 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  14. | 10           | 192.168.68.132 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  15. | 10           | 192.168.68.133 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  16. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  17. 3 rows in set (0.07 sec)

3.4.2 在所有被监控MySQL服务器上创建监控帐户

  1. -- 2、在所有被监控MySQL服务器上创建帐户，注意：新版本中，这里的密码必须为monitor，可参考配置文件/etc/proxysql.cnf
  2. mysql -uroot -plhr -h192.168.66.35 -P33131
  3. create user 'monitor'@'%' IDENTIFIED BY 'monitor';
  4. GRANT all privileges ON *.* TO 'monitor'@'%' with grant option;
  5. select user,host from mysql.user;
  7. mysql> select user,host from mysql.user;
  8. +---------------+--------------+
  9. | user          | host         |
  10. +---------------+--------------+
  11. | mha           | %            |
  12. | monitor       | %            |
  13. | repl          | %            |
  14. | root          | %            |
  15. | mysql.session | localhost    |
  16. | mysql.sys     | localhost    |
  17. | root          | localhost    |
  18. +---------------+--------------+
  19. 7 rows in set (0.00 sec)

3.4.3 在所有被监控MySQL服务器上创建对外访问账户

  1. -- 3、 在所有被监控MySQL服务器上创建对外访问账户：
  2. create user 'wr'@'%' IDENTIFIED BY 'lhr';
  3. GRANT all privileges ON *.* TO 'wr'@'%' with grant option; 
  5. -- 配置到ProxySQL中
  6. insert into mysql_users(username,password,default_hostgroup) values('wr','lhr',10);
  7. update mysql_users set transaction_persistent=1 where username='wr';
  8. load mysql users to runtime;
  9. save mysql users to disk;
  10. select * from mysql_users;
  12. MySQL [(none)]> select * from mysql_users;
  13. +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
  14. | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
  15. +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
  16. | wr       | lhr      | 1      | 0       | 10                | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |         |
  17. +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
  18. 1 row in set (0.05 sec)

3.4.4 配置监控

  1. -- 4、在ProxySQL端执行下列SQL语句：
  2. set mysql-monitor_username='monitor';
  3. set mysql-monitor_password='monitor';
  4. load mysql servers to runtime;
  5. save mysql servers to disk;
  6. select * from global_variables where variable_name in('mysql-monitor_username','mysql-monitor_password');
  7. +------------------------+----------------+
  8. | variable_name          | variable_value |
  9. +------------------------+----------------+
  10. | mysql-monitor_password | monitor        |
  11. | mysql-monitor_username | monitor        |
  12. +------------------------+----------------+
  13. 2 rows in set (0.05 sec)
  14. -- 检查连接到MySQL的日志
  15. select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6;
  16. select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;
  17. MySQL [(none)]> select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6;
  18. +----------------+------+------------------+----------------------+------------+
  19. | hostname       | port | time_start_us    | ping_success_time_us | ping_error |
  20. +----------------+------+------------------+----------------------+------------+
  21. | 192.168.68.132 | 3306 | 1614050308827202 | 252                  | NULL       |
  22. | 192.168.68.133 | 3306 | 1614050308716530 | 370                  | NULL       |
  23. | 192.168.68.131 | 3306 | 1614050308605853 | 542                  | NULL       |
  24. | 192.168.68.131 | 3306 | 1614050298778908 | 334                  | NULL       |
  25. | 192.168.68.133 | 3306 | 1614050298690947 | 297                  | NULL       |
  26. | 192.168.68.132 | 3306 | 1614050298605725 | 344                  | NULL       |
  27. +----------------+------+------------------+----------------------+------------+
  28. 6 rows in set (0.06 sec)
  30. MySQL [(none)]> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;
  31. +----------------+------+------------------+-------------------------+---------------+
  32. | hostname       | port | time_start_us    | connect_success_time_us | connect_error |
  33. +----------------+------+------------------+-------------------------+---------------+
  34. | 192.168.68.131 | 3306 | 1614050285481316 | 1173                    | NULL          |
  35. | 192.168.68.133 | 3306 | 1614050284894846 | 1008                    | NULL          |
  36. | 192.168.68.132 | 3306 | 1614050284309124 | 970                     | NULL          |
  37. | 192.168.68.131 | 3306 | 1614050225194575 | 1108                    | NULL          |
  38. | 192.168.68.133 | 3306 | 1614050224751771 | 987                     | NULL          |
  39. | 192.168.68.132 | 3306 | 1614050224309026 | 1294                    | NULL          |
  40. +----------------+------+------------------+-------------------------+---------------+
  41. 6 rows in set (0.05 sec)

四、故障切换

在Manager节点检查SSH、复制及MHA的状态。

  1. docker exec -it MHA-LHR-Monitor-ip134 bash
  2. masterha_check_ssh --conf=/etc/mha/mha.cnf
  3. masterha_check_repl --conf=/etc/mha/mha.cnf
  4. masterha_check_status --conf=/etc/mha/mha.cnf
  6. -- 启动MHA监控进程
  7. nohup masterha_manager --conf=/etc/mha/mha.cnf  --ignore_last_failover < /dev/null > /usr/local/mha/manager_start.log 2>&1 &
  9. --关闭MHA监控进程
  10. masterha_stop --conf=/etc/mha/mha.cnf
  11. [root@MHA-LHR-Monitor-ip134 /]# masterha_check_status --conf=/etc/mha/mha.cnf
  12. mha (pid:3738) is running(0:PING_OK), master:192.168.68.131

接下来，宕掉主库，继续观察ProxySQL的情况：

  1. -- 宕掉主库
  2.  docker stop MHA-LHR-Master1-ip131

MHA自动执行了故障转移，主库切换为132，并发送告警邮件：

https://cdn.jsdelivr.net/gh/lhrbest/pic/img/20210416111828.png

此时，来查看ProxySQL的情况：

  1. MySQL [(none)]> select * from mysql_servers;
  2. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  3. | hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
  4. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  5. | 10           | 192.168.68.132 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  6. | 20           | 192.168.68.131 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  7. | 20           | 192.168.68.133 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  8. | 20           | 192.168.68.132 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  9. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  10. 4 rows in set (0.05 sec)
  12. MySQL [(none)]> select * from runtime_mysql_servers;
  13. +--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  14. | hostgroup_id | hostname       | port | gtid_port | status  | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
  15. +--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  16. | 10           | 192.168.68.132 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  17. | 20           | 192.168.68.131 | 3306 | 0         | SHUNNED | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  18. | 20           | 192.168.68.133 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  19. | 20           | 192.168.68.132 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  20. +--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  21. 4 rows in set (1.26 sec)

可以发现131已经变成SHUNNED状态，ProxySQL会避开这个主机。

此时再做压测等操作，所有负载会被分配到132和133上，此处不再测试。

接下来启动131，并以从库的身份加入原主从环境：

  1. -- 启动131
  2. docker start MHA-LHR-Master1-ip131
  4. -- 在134的日志文件中找到恢复的语句
  5. grep "All other slaves should start replication from here" /usr/local/mha/manager_running.log
  7. -- 在131上执行恢复
  8. mysql -uroot -plhr -h192.168.68.131 -P3306
  9. CHANGE MASTER TO MASTER_HOST='192.168.68.132', 
  10. MASTER_PORT=3306, 
  11. MASTER_AUTO_POSITION=1,
  12. MASTER_USER='repl',
  13. MASTER_PASSWORD='lhr';
  15. start slave;
  16. show slave status \G
  18. -- 设置只读
  19. set global read_only=1;

查询ProxySQL：

  1. MySQL [(none)]> select * from mysql_servers;
  2. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  3. | hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
  4. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  5. | 10           | 192.168.68.132 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  6. | 20           | 192.168.68.131 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  7. | 20           | 192.168.68.133 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  8. | 20           | 192.168.68.132 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  9. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  10. 4 rows in set (0.06 sec)

可以看到131为只读。若想让132只写，则可以删除相关记录：

  1. MySQL [(none)]> delete from mysql_servers where hostgroup_id=20 and  hostname='192.168.68.132';
  2. Query OK, 1 row affected (0.06 sec)
  4. MySQL [(none)]> load mysql servers to runtime;
  5. Query OK, 0 rows affected (0.68 sec)
  7. MySQL [(none)]> save mysql servers to disk;
  8. Query OK, 0 rows affected (0.10 sec)
  10. MySQL [(none)]> select * from mysql_servers;
  11. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  12. | hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
  13. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  14. | 10           | 192.168.68.132 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  15. | 20           | 192.168.68.131 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  16. | 20           | 192.168.68.133 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  17. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  18. 3 rows in set (0.05 sec)
  20. MySQL [(none)]> select * from runtime_mysql_servers;
  21. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  22. | hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
  23. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  24. | 10           | 192.168.68.132 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  25. | 20           | 192.168.68.133 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  26. | 20           | 192.168.68.131 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
  27. +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  28. 3 rows in set (0.94 sec)

可以看到132为主库，131和133为从库。Orchestrator界面：

https://cdn.jsdelivr.net/gh/lhrbest/pic/img/20210416111835.png

点击关注，第一时间了解华为云新鲜技术~

版权说明 : 本文为转载文章, 版权归原作者所有 版权申明

原文链接 : https://huaweicloud.blog.csdn.net/article/details/123988862

内容来源于网络，如有侵权，请联系作者删除！

mysqlproxysql读写分离技术交流高可用架构MHA

相关文章

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新文章

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com