MySQL高可用代理ProxySQL

去皮卡多  论坛元老 | 2025-5-26 23:40:22 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 2002|帖子 2002|积分 6006

安装

系统为ubuntu20.0
  1. wget https://github.com/sysown/proxysql/releases/download/v2.4.2/proxysql_2.4.2-ubuntu20_amd64.deb
  2. dpkg -i proxysql_2.4.2-ubuntu20_amd64.deb
复制代码
原本想用编译安装的，编译了30多分钟，启动的时候提示无法打开设置文件，无语了，只能用deb包安装

默认安装位置：
proxysql.cnf：/etc/proxysql.cnf
datadir：   /var/lib/proxysql
errorlog：   /var/lib/proxysql/proxysql.log
启动
  1. systemctl start proxysql
复制代码
proxysql原理：

proxysql.cnf 中的设置只在初次启动 proxysql 时生效。假如 proxysql 的内置数据库已经创建，后续只会从内置的数据库中读取设置信息，这时再修改 proxysql.cnf 就不起作用了。
  1. root@ubuntu06:/usr/local/proxysql# mysql -uadmin -padmin -P6032 -h127.0.0.1
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 1
  5. Server version: 5.5.30 (ProxySQL Admin Module)
  7. Copyright (c) 2000, 2024, Oracle and/or its affiliates.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. mysql>
  16. mysql>
  17. mysql> show databases;
  18. +-----+---------------+-------------------------------------+
  19. | seq | name          | file                                |
  20. +-----+---------------+-------------------------------------+
  21. | 0   | main          |                                     |
  22. | 2   | disk          | /var/lib/proxysql/proxysql.db       |
  23. | 3   | stats         |                                     |
  24. | 4   | monitor       |                                     |
  25. | 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
  26. +-----+---------------+-------------------------------------+
  27. 5 rows in set (0.00 sec)
复制代码
main：内存数据库。这个库里的表分两类，runtime 开头的表存的是当前实际生效的设置。别的表存的是设置值，可通过 load 命令加载到 runtime 中，通过 save 命令持久化到磁盘中。
disk：sqllite 数据库。proxysql 启动时从这个数据库加载设置项。
 
MySQL主从复制

mysql 主：192.168.152.112:8000
mysql 从：192.168.152.113:8000
proxysql1 192.168.152.112:6032
proxysql2 192.168.152.112:6032
 
proxysql设置

 1，proxysql自身需要连接到被监控的MySQL实例，因此需要再被监控的MySQL实例上创建相关用户
MySQL 主节点192.168.152.112:8000创建monitor用户：
  1. -- 1，MySQL 主节点创建monitor用户：
  2. create user 'proxysql_monitor'@'%' identified with mysql_native_password
  3. by 'proxysql_monitor_password';
  4. -- ALTER USER 'proxysql_monitor'@'%' IDENTIFIED BY 'proxysql_monitor_password';
  5. -- FLUSH privileges;
  6. grant replication client on *.* to 'proxysql_monitor'@'%';
  8. -- group replication
  9. grant select on performance_schema.replication_group_member_stats to 'proxysql_monitor'@'%';
  10. grant select on performance_schema.replication_group_members to 'proxysql_monitor'@'%';
复制代码
 MySQL 从节点192.168.152.113:8000创建monitor用户（假如主从复制包罗了mysql系统数据库自身，则不用再次创建）：
  1. -- 1，MySQL 主节点创建monitor用户：
  2. create user 'proxysql_monitor'@'%' identified with mysql_native_password
  3. by 'proxysql_monitor_password';
  4. -- ALTER USER 'proxysql_monitor'@'%' IDENTIFIED BY 'proxysql_monitor_password';
  5. -- FLUSH privileges;
  6. grant replication client on *.* to 'proxysql_monitor'@'%';
  8. -- group replication
  9. grant select on performance_schema.replication_group_member_stats to 'proxysql_monitor'@'%';
  10. grant select on performance_schema.replication_group_members to 'proxysql_monitor'@'%';
复制代码
 
2，proxysql 上（192.168.152.112:6032）设置步骤1中的数据库账号，用以监控目标MySQL的主从复制节点状态
  1. mysql -uadmin -padmin -P6032 -h127.0.0.1
  3. set mysql-monitor_username = 'proxysql_monitor';
  4. set mysql-monitor_password = 'proxysql_monitor_password';
  5. load mysql variables to runtime;
  6. save mysql variables to DISK;
复制代码
 
3，proxysql 上（192.168.152.112:6032）设置监控的目标数据库实例（192.168.152.112:8000，192.168.152.113:8000）
  1. -- 主节点
  2. insert into mysql_servers ( hostgroup_id, hostname, port, max_replication_lag)VALUES ( 100, '192.168.152.112', 8000, 3);
  3. -- 从节点
  4. insert into mysql_servers ( hostgroup_id, hostname, port, max_replication_lag)VALUES ( 101, '192.168.152.113', 8000, 3);
  6. load mysql servers to runtime; save mysql servers to disk;
  9. mysql> 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. | 100          | 192.168.152.112 | 8000 | 0         | ONLINE | 1      | 0           | 1000            | 3                   | 0       | 0              |         |
  14. | 101          | 192.168.152.113 | 8000 | 0         | ONLINE | 1      | 0           | 1000            | 3                   | 0       | 0              |         |
  15. +--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  16. 2 rows in set (0.00 sec)
  20. -- 监控账号proxysql_monitor，连接到监控节点的日志查看，一开始有报错正常是因为笔者的环境MySQL主从没有同步mysql系统库，在从节点创建相关的监控用户后就正常了
  21. mysql> select * from mysql_server_connect_log;
  22. +-----------------+------+------------------+-------------------------+-----------------------------------------------------------------------------------+
  23. | hostname        | port | time_start_us    | connect_success_time_us | connect_error                                                                     |
  24. +-----------------+------+------------------+-------------------------+-----------------------------------------------------------------------------------+
  25. | 192.168.152.112 | 8000 | 1748242184707589 | 4645                    | NULL                                                                              |
  26. | 192.168.152.113 | 8000 | 1748242185385232 | 0                       | Access denied for user 'proxysql_monitor'@'192.168.152.112' (using password: YES) |
  27. | 192.168.152.113 | 8000 | 1748242244708867 | 0                       | Access denied for user 'proxysql_monitor'@'192.168.152.112' (using password: YES) |
  28. | 192.168.152.112 | 8000 | 1748242245453236 | 1453                    | NULL                                                                              |
  29. | 192.168.152.112 | 8000 | 1748242304709696 | 1504                    | NULL                                                                              |
  30. | 192.168.152.113 | 8000 | 1748242305526984 | 0                       | Access denied for user 'proxysql_monitor'@'192.168.152.112' (using password: YES) |
  31. | 192.168.152.113 | 8000 | 1748242364710488 | 0                       | Access denied for user 'proxysql_monitor'@'192.168.152.112' (using password: YES) |
  32. | 192.168.152.112 | 8000 | 1748242365656714 | 797                     | NULL                                                                              |
  33. | 192.168.152.112 | 8000 | 1748242424711003 | 1054                    | NULL                                                                              |
  34. | 192.168.152.113 | 8000 | 1748242425655353 | 0                       | Access denied for user 'proxysql_monitor'@'192.168.152.112' (using password: YES) |
  35. | 192.168.152.112 | 8000 | 1748242484711945 | 972                     | NULL                                                                              |
  36. | 192.168.152.113 | 8000 | 1748242485622272 | 2250                    | NULL                                                                              |
  37. | 192.168.152.112 | 8000 | 1748242544713013 | 1076                    | NULL                                                                              |
  38. | 192.168.152.113 | 8000 | 1748242545666379 | 1753                    | NULL                                                                              |
  39. | 192.168.152.112 | 8000 | 1748242604714363 | 1224                    | NULL                                                                              |
  40. | 192.168.152.113 | 8000 | 1748242605430811 | 1649                    | NULL                                                                              |
  41. | 192.168.152.112 | 8000 | 1748242664715436 | 3282                    | NULL                                                                              |
  42. | 192.168.152.113 | 8000 | 1748242665599634 | 1467                    | NULL                                                                              |
  43. +-----------------+------+------------------+-------------------------+-----------------------------------------------------------------------------------+
  44. 18 rows in set (0.00 sec)
复制代码
 
  4， proxysql上创建读/写组的group Id
  1. -- 指定写组的id为100，读组的id为101，也即第三步骤插入mysql_servers时，对应的服务器的hostgroup_id字段的值
  2. insert into mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, COMMENT) VALUES(100, 101, 'read_only', 'mysql m-s cluster');
  3. load mysql servers to runtime;
  4. save mysql servers to disk;
复制代码
 
 5，设置读写分离的用户
  1. -- 5.1，主节点上创建一个读写的用户
  2. create user 'rw_user'@'%' identified with mysql_native_password
  3. by 'rw_user_password';
  4. grant all on *.* to 'rw_user'@'%';
  6. -- 5.2，从节点上创建一个只读的用户
  7. create user 'ro_user'@'%' identified with mysql_native_password
  8. by 'ro_user_password';
  9. grant select on *.* to 'ro_user'@'%';
  11. flush PRIVILEGES;
  14. -- 5.3，配置读写分离用户，
  16. --主节点的rw_user用户指定到default_hostgroup = 100的读写组
  17. insert into mysql_users
  18. (username, password, default_hostgroup, transaction_persistent, backend, frontend, comment)
  19. values ('rw_user', 'rw_user_password', 100, 1, 1, 1, 'read/write user');
  21. --从节点的ro_user用户指定到default_hostgroup = 101的只读组
  22. insert into mysql_users
  23. (username, password, default_hostgroup, transaction_persistent, backend, frontend, comment)
  24. values ('ro_user', 'ro_user_password', 101, 1, 1, 1, 'read only user');
  26. load mysql users  to runtime;
  27. save mysql users  TO disk;
  30. mysql> select * from mysql_users;
  31. +----------+------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+-----------------+
  32. | username | password         | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment         |
  33. +----------+------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+-----------------+
  34. | rw_user  | rw_user_password | 1      | 0       | 100               | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |            | read/write user |
  35. | ro_user  | ro_user_password | 1      | 0       | 101               | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |            | read only user |
  36. +----------+------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+-----------------+
  37. 2 rows in set (0.00 sec)
复制代码
 
 6，读写分离测试
  1. -- rw_user账号会连接到主节点
  2. mysql -urw_user -prw_user_password -P6033 -h127.0.0.1 -e "select @@server_id"
  3. -- ro_user账号会连接到从节点
  4. mysql -uro_user -pro_user_password -P6033 -h127.0.0.1 -e "select @@server_id"
  6. root@ubuntu06:/usr/local/proxysql#
  7. root@ubuntu06:/usr/local/proxysql#
  8. root@ubuntu06:/usr/local/proxysql# mysql -urw_user -prw_user_password -P6033 -h127.0.0.1 -e "select @@server_id"
  9. mysql: [Warning] Using a password on the command line interface can be insecure.
  10. +-------------+
  11. | @@server_id |
  12. +-------------+
  13. |       18000 |
  14. +-------------+
  16. root@ubuntu06:/usr/local/proxysql# mysql -uro_user -pro_user_password -P6033 -h127.0.0.1 -e "select @@server_id"
  17. mysql: [Warning] Using a password on the command line interface can be insecure.
  18. +-------------+
  19. | @@server_id |
  20. +-------------+
  21. |       18001 |
  22. +-------------+
  23. root@ubuntu06:/usr/local/proxysql#
复制代码
 
    
7，模拟MySQL主从故障转移
  1. 1，主节点宕机
  2. 强制关闭主节点，模拟故障转移，原主节点下线，192.168.152.112，MySQL主从复制层面完成故障转移之后，在mysqlproxy层面，192.168.152.113升级为主节点（hostgroup_id自动更新为100），
  3. mysql>
  4. mysql> select * from runtime_mysql_servers;
  5. +--------------+-----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  6. | hostgroup_id | hostname        | port | gtid_port | status  | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
  7. +--------------+-----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  8. | 100          | 192.168.152.113 | 8000 | 0         | ONLINE  | 1      | 0           | 1000            | 3                   | 0       | 0              |         |
  9. | 101          | 192.168.152.112 | 8000 | 0         | SHUNNED | 1      | 0           | 1000            | 3                   | 0       | 0              |         |
  10. | 101          | 192.168.152.113 | 8000 | 0         | ONLINE  | 1      | 0           | 1000            | 3                   | 0       | 0              |         |
  11. +--------------+-----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  12. 3 rows in set (0.00 sec)
  14. mysql>
  17. 2，主节点宕机后恢复，作为从节点运行，上述的status=SHUNNED会自动更新为ONLINE，此时会更具read_only状态来判断主从节点，而不会自动将原始主节点添加到读写组hostgroup_id中
  18. mysql> select * from runtime_mysql_servers;
  19. +--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  20. | hostgroup_id | hostname        | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
  21. +--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  22. | 100          | 192.168.152.113 | 8000 | 0         | ONLINE | 1      | 0           | 1000            | 3                   | 0       | 0              |         |
  23. | 101          | 192.168.152.112 | 8000 | 0         | ONLINE | 1      | 0           | 1000            | 3                   | 0       | 0              |         |
  24. | 101          | 192.168.152.113 | 8000 | 0         | ONLINE | 1      | 0           | 1000            | 3                   | 0       | 0              |         |
  25. +--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  26. 3 rows in set (0.00 sec)
复制代码
 
8，proxysql自身的集群
proxysql自身假如是单点状态，同样会存在风险，因此proxysql自身也需要做一个cluster，proxysql自身作cluster比力简单。

1，在proxysql1也即192.168.152.112 节点上实行：
  1. 1，在192.168.152.112 节点上执行：
  3. set admin-admin_credentials = 'admin:admin;clusteradmin:clusteradmin';
  4. set admin-cluster_username='clusteradmin';
  5. set admin-cluster_password='clusteradmin';
  6. load admin variables to runtime;
  7. save admin variables to DISK;
  10. -- 插入proxysql多个节点的信息
  11. insert into proxysql_servers(hostname, port, weight, comment) values('192.168.152.112', 6032, 1, 'proxysql node 1');
  13. insert into proxysql_servers(hostname, port, weight, comment) values('192.168.152.113', 6032, 1, 'proxysql node 2');
  15. load proxysql servers to runtime;
  16. save proxysql servers to DISK;
  19. mysql> select * from proxysql_servers;
  20. +-----------------+------+--------+-----------------+
  21. | hostname        | port | weight | comment         |
  22. +-----------------+------+--------+-----------------+
  23. | 192.168.152.112 | 6032 | 1      | proxysql node 1 |
  24. | 192.168.152.113 | 6032 | 1      | proxysql node 2 |
  25. +-----------------+------+--------+-----------------+
  26. 2 rows in set (0.01 sec)
复制代码
1，在proxysql2也即192.168.152.113 节点上实行：
  1. 2，在192.168.152.113 节点上执行（与112上一样的初始化语句才能同步）：
  3. set admin-admin_credentials = 'admin:admin;clusteradmin:clusteradmin';
  4. set admin-cluster_username='clusteradmin';
  5. set admin-cluster_password='clusteradmin';
  6. load admin variables to runtime;
  7. save admin variables to DISK;
  10. -- 插入proxysql多个节点的信息
  11. insert into proxysql_servers(hostname, port, weight, comment) values('192.168.152.112', 6032, 1, 'proxysql node 1');
  12.    
  13. insert into proxysql_servers(hostname, port, weight, comment) values('192.168.152.113', 6032, 1, 'proxysql node 2');
  16. load proxysql servers to runtime;
  17. save proxysql servers to DISK;
  20. mysql> select * from proxysql_servers;
  21. +-----------------+------+--------+-----------------+
  22. | hostname        | port | weight | comment         |
  23. +-----------------+------+--------+-----------------+
  24. | 192.168.152.112 | 6032 | 1      | proxysql node 1 |
  25. | 192.168.152.113 | 6032 | 1      | proxysql node 2 |
  26. +-----------------+------+--------+-----------------+
  27. 2 rows in set (0.01 sec)
  30. --自动同步proxysql1（192.168.152.112:6032）上的信息
  31. mysql> select * from mysql_users;
  32. +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+----------------
  33. | username | password                                  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections
  34. +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+----------------
  35. | ro_user  | *D738C9F311FE0C81C55A34E241BE55B6243D63C3 | 1      | 0       | 101               |                | 0             | 1                      | 0            | 0       | 1        | 10000
  36. | rw_user  | *31E35F0357C0338CA567D6DC2D1E1EE8B1D7D6A6 | 1      | 0       | 100               |                | 0             | 1                      | 0            | 0       | 1        | 10000
  37. | rw_user  | *31E35F0357C0338CA567D6DC2D1E1EE8B1D7D6A6 | 1      | 0       | 100               |                | 0             | 1                      | 0            | 1       | 0        | 10000
  38. | ro_user  | *D738C9F311FE0C81C55A34E241BE55B6243D63C3 | 1      | 0       | 101               |                | 0             | 1                      | 0            | 1       | 0        | 10000
  39. +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+----------------
  40. 4 rows in set (0.00 sec)
复制代码
 
这里有一个细节：mysql.user表在proxysql1（192.168.152.112:6032）上明文，在proxysql2（192.168.152.113:6032）上是密文
 

 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

去皮卡多

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

渠道 国产数据库 集成商 AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表