Java教程
proxysql运维实践
本文主要是介绍proxysql运维实践，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
前置基础参考：https://www.cnblogs.com/gered/p/15868767.html#autoid-4-0-0
【1】环境
当前架构
、
【2】场景
（1）1主2从，主库挂掉
10秒就超时（这个阈值是连接超时）反馈出来了，但如果我们的DML、select 等其他操作 超过10秒是没有关系的；
主库挂了，非 mysql_query_rules 定位到从组的请求，都会连不上，我们查看一下相关日志
select * from runtime_mysql_servers; select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_ping_log where ping_error is not null limit 10; select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_connect_log where connect_error is not null order by time_start_us desc limit 10; select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_read_only_log where error is not null limit 10;
结果：
admin@mysqldb 17:02:48 [(none)]>select * from runtime_mysql_servers; +--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 192.168.148.39 | 3306 | 0 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 192.168.148.30 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 192.168.148.27 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec) admin@mysqldb 17:02:52 [(none)]>select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_ping_log where ping_error is not null limit 10; +---------------------+----------------+------+------------------+----------------------+---------------------------------------------------------+ | datetime | hostname | port | time_start_us | ping_success_time_us | ping_error | +---------------------+----------------+------+------------------+----------------------+---------------------------------------------------------+ | 2022-02-16 08:56:43 | 192.168.148.39 | 3306 | 1645001803735858 | 0 | Can't connect to MySQL server on '192.168.148.39' (115) | | 2022-02-16 08:56:45 | 192.168.148.39 | 3306 | 1645001805750545 | 0 | Can't connect to MySQL server on '192.168.148.39' (115) | | 2022-02-16 08:56:47 | 192.168.148.39 | 3306 | 1645001807770656 | 0 | Can't connect to MySQL server on '192.168.148.39' (115) | | 2022-02-16 08:56:49 | 192.168.148.39 | 3306 | 1645001809742158 | 0 | Can't connect to MySQL server on '192.168.148.39' (115) | | 2022-02-16 08:56:51 | 192.168.148.39 | 3306 | 1645001811771683 | 0 | Can't connect to MySQL server on '192.168.148.39' (115) | | 2022-02-16 08:56:53 | 192.168.148.39 | 3306 | 1645001813752847 | 0 | Can't connect to MySQL server on '192.168.148.39' (115) | | 2022-02-16 08:56:55 | 192.168.148.39 | 3306 | 1645001815746628 | 0 | Can't connect to MySQL server on '192.168.148.39' (115) | | 2022-02-16 08:56:57 | 192.168.148.39 | 3306 | 1645001817742752 | 0 | Can't connect to MySQL server on '192.168.148.39' (115) | | 2022-02-16 08:56:59 | 192.168.148.39 | 3306 | 1645001819728439 | 0 | Can't connect to MySQL server on '192.168.148.39' (115) | | 2022-02-16 08:57:01 | 192.168.148.39 | 3306 | 1645001821729269 | 0 | Can't connect to MySQL server on '192.168.148.39' (115) | +---------------------+----------------+------+------------------+----------------------+---------------------------------------------------------+ 10 rows in set (0.00 sec) admin@mysqldb 17:02:52 [(none)]>select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_connect_log where connect_error is not null order by time_start_us desc limit 10; +---------------------+----------------+------+------------------+-------------------------+---------------------------------------------------------+ | datetime | hostname | port | time_start_us | connect_success_time_us | connect_error | +---------------------+----------------+------+------------------+-------------------------+---------------------------------------------------------+ | 2022-02-16 08:56:47 | 192.168.148.39 | 3306 | 1645001807770108 | 0 | Can't connect to MySQL server on '192.168.148.39' (115) | | 2022-02-16 08:56:45 | 192.168.148.39 | 3306 | 1645001805716648 | 0 | Can't connect to MySQL server on '192.168.148.39' (115) | | 2022-02-16 08:56:43 | 192.168.148.39 | 3306 | 1645001803713712 | 0 | Can't connect to MySQL server on '192.168.148.39' (115) | | 2022-02-16 08:56:41 | 192.168.148.39 | 3306 | 1645001801713565 | 0 | Can't connect to MySQL server on '192.168.148.39' (115) | +---------------------+----------------+------+------------------+-------------------------+---------------------------------------------------------+ 4 rows in set (0.00 sec) admin@mysqldb 17:02:52 [(none)]>select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_read_only_log where error is not null limit 10; +---------------------+----------------+------+------------------+-----------------+-----------+---------------------------------------------------------------------------------------------+ | datetime | hostname | port | time_start_us | success_time_us | read_only | error | +---------------------+----------------+------+------------------+-----------------+-----------+---------------------------------------------------------------------------------------------+ | 2022-02-16 08:56:42 | 192.168.148.39 | 3306 | 1645001802730203 | 0 | NULL | Lost connection to MySQL server during query | | 2022-02-16 08:56:44 | 192.168.148.39 | 3306 | 1645001804750337 | 0 | NULL | timeout on creating new connection: Can't connect to MySQL server on '192.168.148.39' (115) | | 2022-02-16 08:56:46 | 192.168.148.39 | 3306 | 1645001806742312 | 0 | NULL | timeout on creating new connection: Can't connect to MySQL server on '192.168.148.39' (115) | +---------------------+----------------+------+------------------+-----------------+-----------+---------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
然后我们看一下最新的
select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_ping_log order by time_start_us desc limit 10; select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_connect_log order by time_start_us desc limit 10; select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_read_only_log order by time_start_us desc limit 10;
从下图我们可以看出， ping 还是一直不通，而 connect 、read_only 中甚至直接剔除对 主库 192.168.148.39 的连接尝试了
结合上面的所有，我们发现 ping_log 报错了 300次（2s一次的频率），而 connect_log（2秒一次的频率）只报了4次；
如下图，我们发现我们选中的参数，次数是3，所以就 connect 重试3次后，所以 connect_log 中只有4次记录是正常的；
（2）1主2从，主库挂掉后重新启动恢复
恢复主库后：可无缝连上，现有连接无需重连，无需断开原长连接
、
恢复后，我们看看 mysql server，hostgrou_id
我们可以看到，status 还是 shunned 状态
我们执行一个路由到主库的相关操作，它状态就变回来了；
（2）1主2从=》主库挂掉，从变主库=》1主1从
关闭主库，从库1： 192.168.148.27 执行如下代码，变成 proxysql中的主库
stop slave; set global read_only=0;
如下图：
为什么主库变成下图这样，即在10 写组，又在20 读组？因为如下参数
mysql-monitor_writer_is_also_reader = true（当原read_only=1 的server read_only=0时，迁移一份到 writer 组的同时，也保留它的 reader 角色）
这个是为了避免故障转移后只有1个实例的时候，可以同时兼顾读写，从而业务连续；
我们再查询看看：是可以都读负载均衡分发的；写也是写到 我们的新主库 192.168.148.27 中去了；
注意：proxysql 连接在任意时候都没断开过，所以主从切换等情况不影响它的长连接，也不需要重连等等；
这篇关于proxysql运维实践的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
Springboot应用的多环境打包入门11-23
-
Springboot应用的生产发布入门教程11-23
-
Python编程入门指南11-23
-
Java创业入门：从零开始的编程之旅11-23
-
Java创业入门：新手必读的Java编程与创业指南11-23
-
Java对接阿里云智能语音服务入门详解11-23
-
Java对接阿里云智能语音服务入门教程11-23
-
JAVA对接阿里云智能语音服务入门教程11-23
-
Java副业入门：初学者的简单教程11-23
-
JAVA副业入门：初学者的实战指南11-23
-
JAVA项目部署入门：新手必读指南11-23
-
Java项目部署入门：新手必看指南11-23
-
Java项目部署入门：新手必读指南11-23
-
Java项目开发入门：新手必读指南11-23
-
JAVA项目开发入门：从零开始的实用教程11-23
栏目导航
