原 【DB宝42】MySQL高可用架构MHA+ProxySQL实现读写分离和负载均衡
Tags： 原创MySQL高可用监控主从复制MHA读写分离负载均衡ProxySQL
👉 本文共约2493个字，系统预计阅读时间或需10分钟。
目录
三、配置ProxySQL环境
3.1 申请ProxySQL主机并安装ProxySQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | docker rm -f MHA-LHR-ProxySQL-ip136 docker run -d --name MHA-LHR-ProxySQL-ip136 -h MHA-LHR-ProxySQL-ip136 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --network mhalhr --ip 192.168.68.136 \ -p 26032:6032 -p 26033:6033 -p 26080:6080 \ --privileged=true lhrbest/lhrcentos76:8.0 \ /usr/sbin/init docker network connect bridge MHA-LHR-ProxySQL-ip136 docker restart MHA-LHR-ProxySQL-ip136 docker cp proxysql2-2.0.15-1.1.el7.x86_64.rpm MHA-LHR-ProxySQL-ip136:/ docker exec -it MHA-LHR-ProxySQL-ip136 bash rpm -ivh proxysql2-2.0.15-1.1.el7.x86_64.rpm systemctl start proxysql systemctl status proxysql |
3.2 添加远程登录用户
1 2 3 4 5 6 7 8 9 10 | -- 添加远程登录用户 mysql -uadmin -padmin -h127.0.0.1 -P6032 select @@admin-admin_credentials; set admin-admin_credentials='admin:admin;root:lhr'; select @@admin-admin_credentials; load admin variables to runtime; save admin variables to disk; -- 远程登录 mysql -uroot -plhr -h192.168.66.35 -P26032 |
执行过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | -- ProxySQL本地登录 [root@MHA-LHR-ProxySQL-ip136 /]# mysql -uadmin -padmin -h127.0.0.1 -P6032 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 162 Server version: 5.5.30 (ProxySQL Admin Module) 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> select @@admin-admin_credentials; +---------------------------+ | @@admin-admin_credentials | +---------------------------+ | admin:admin;lhr:lhr | +---------------------------+ 1 row in set (0.05 sec) mysql> set admin-admin_credentials='admin:admin;root:lhr'; Query OK, 1 row affected (0.00 sec) mysql> select @@admin-admin_credentials; +---------------------------+ | @@admin-admin_credentials | +---------------------------+ | admin:admin;root:lhr | +---------------------------+ 1 row in set (0.00 sec) mysql> load admin variables to runtime; Query OK, 0 rows affected (0.00 sec) mysql> save admin variables to disk; Query OK, 35 rows affected (0.13 sec) mysql> -- 远程登录 C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P26032 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): ProxySQL Error: Access denied for user 'root'@'172.17.0.1' (using password: YES) C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P26032 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 163 Server version: 5.5.30 (ProxySQL Admin Module) 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 [(none)]> show databases; +-----+---------------+-------------------------------------+ | seq | name | file | +-----+---------------+-------------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | | 5 | stats_history | /var/lib/proxysql/proxysql_stats.db | +-----+---------------+-------------------------------------+ 5 rows in set (0.05 sec) |
3.3 开启ProxySQL的web监控功能
1 2 3 4 5 6 7 8 9 10 11 12 | -- 开启web监控功能 SET admin-web_enabled='true'; LOAD ADMIN VARIABLES TO RUNTIME; SAVE ADMIN VARIABLES TO DISK; select * from global_variables where variable_name LIKE 'admin-web_enabled'; select @@admin-web_enabled; lsof -i:6080 -- 浏览器访问 https://192.168.66.35:26080 用户名和密码：stats:stats |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
