ProxySQL 使用情况报错问题汇总及解决办法

1.ProxySQL Error: connection is locked to hostgroup 2 but trying to reach hostgroup 1

解决方案：登上proxysql的管理端执行以下命令

set mysql-set_query_lock_on_hostgroup=0;

#修改后，需要加载到RUNTIME，并保存到disk
load mysql variables to runtime;
save mysql variables to disk;


# 也可以在安装后尚未启动时修改配置文件，增加上这个参数：
mysql_variables=
{
    ......
    set_query_lock_on_hostgroup=0
}

2.java.sql.SQLException: Unknown system variable query_cache_size

解决方案：proxysql 2.4.1版本目前内置的mysql版本也才是5.5.30的，所以如果你的数据库是8.0及以上的，一定要记得修改proxysql内置mysql的版本号，登上proxysql的管理端执行以下命令

update global_variables set variable_value="8.0.29" where variable_name='mysql-server_version';

#修改后，需要加载到RUNTIME，并保存到disk
load mysql variables to runtime;
save mysql variables to disk;

# 也可以在安装后尚未启动时修改配置文件，更新这个参数：
mysql_variables=
{
    ......
    server_version="8.0.29"
}

3.配置文件中添加如下参数，启动失败，报语法错误

mysql_variables=
{
    ......
    default_charset='utf8mb4'
    default_collation_connection='utf8mb4_general_ci'
}

5月 25 17:28:45 k8s-develop-manager systemd[1]: Starting High Performance Advanced Proxy for MySQL...
5月 25 17:28:45 k8s-develop-manager proxysql[14792]: Parse error at /etc/proxysql.cnf:87 - syntax error
5月 25 17:28:45 k8s-develop-manager systemd[1]: proxysql.service: control process exited, code=exited status=1
5月 25 17:28:45 k8s-develop-manager systemd[1]: Failed to start High Performance Advanced Proxy for MySQL.
5月 25 17:28:45 k8s-develop-manager systemd[1]: Unit proxysql.service entered failed state.
5月 25 17:28:45 k8s-develop-manager systemd[1]: proxysql.service failed.

解决办法：

这俩变量的值默认是
mysql> select * from global_variables;
+----------------------------------------------------------------------+--------------------------------------------+
| variable_name                                                        | variable_value                             |
+----------------------------------------------------------------------+--------------------------------------------+
| mysql-default_charset                                                | utf8                                       |
| mysql-default_collation_connection                                   | utf8_general_ci                            |

启动之前配置文件中先不添加的，等程序启动后，手动修改

set mysql-default_charset='utf8mb4';
set mysql-default_collation_connection='utf8mb4_general_ci';

load mysql variables to runtime;
save mysql variables to disk;

mysql> select * from global_variables;
+----------------------------------------------------------------------+--------------------------------------------+
| variable_name                                                        | variable_value                             |
+----------------------------------------------------------------------+--------------------------------------------+
| mysql-default_charset                                                | utf8mb4                                    |
| mysql-default_collation_connection                                   | utf8mb4_general_ci                         |

4.查看proxySQL ping日志，发现监控账户连接数据库失败，报错如下：

mysql> select * from mysql_server_ping_log;       
+---------------+------+------------------+----------------------+---------------------------------------------------------------------------------------------------------------------------+
| hostname      | port | time_start_us    | ping_success_time_us | ping_error                                                                                                                |
+---------------+------+------------------+----------------------+---------------------------------------------------------------------------------------------------------------------------+
| 192.168.0.218 | 3306 | 1653471251681522 | 0                    | Aborted connection 2616 to db: 'unconnected' user: 'proxysql_monitor' host: '192.168.0.218' (init_connect command failed) |
| 192.168.0.36  | 3306 | 1653471251826419 | 0                    | Aborted connection 429 to db: 'unconnected' user: 'proxysql_monitor' host: '192.168.0.218' (init_connect command failed)  |

解决方案：

根据报错信息：init_connect command failed进行排查，发现是后端MySQL数据库配置文件中有个参数：init_connect=’SET NAMES utf8mb4’，使用的单引号符号不对才导致连接报错的
修改该参数配置为：init_connect='SET NAMES utf8mb4'，重启MySQL，proxySQL ping日志就不会再报错了

自动回避复制延迟较大的节点

查看proxysql运行日志，会有如下信息

2022-05-27 15:53:41 MySQL_Monitor.cpp:2375:monitor_replication_lag_thread(): [ERROR] Replication lag on server 192.168.20.200:3306 is NULL, using the value 60 (mysql-monitor_slave_lag_when_null)

原因分析：
proxyql的mysql_servers表中设置了主库max_replication_lag的值，但是主库中没有show slave status中的Seconds_Behind_Master字段值，因此会报主库拖后腿，其实主库不需要设置max_replication_lag的值

Monitor模块会监控后端主机组中各slave的数据是否延迟于master，这个延迟行为称为replication lag，俗称拖后腿。

如果某个slave节点上的数据比master落后很多(临界值见下文)，表示这个slave节点处理速度慢，数据较旧。ProxySQL采用一种称为自动避开(automatic shunned)的方式，临时避开这个落后的节点。当ProxySQL避开某节点后，ProxySQL不会把SQL语句路由给这个节点。

ProxySQL有几种情况可能会触发自动避开节点的行为：

• 和后端的连接断开。
• slave落后于master过多。
• 和后端建立连接时，错误次数过多。
• second_behind_master=null时，即slave的SQL线程未运行，或者slave未连接到master。(不过这种自动避开的情况是可控的，见全局变量mysql-monitor_slave_lag_when_null）

Monitor模块会每隔一段时间(mysql-monitor_replication_lag_interval)去检查一次拖后腿情况，检测的方式是获取show slave status中的Seconds_Behind_Master字段值，然后和mysql_servers表中max_replication_lag字段的值比较：

mysql> select * from global_variables where variable_name like 'mysql-monitor%lag%';
+-----------------------------------------------------+----------------+
| variable_name                                       | variable_value |
+-----------------------------------------------------+----------------+
| mysql-monitor_replication_lag_group_by_host         | false          |
| mysql-monitor_replication_lag_interval              | 10000          |
| mysql-monitor_replication_lag_timeout               | 1000           |
| mysql-monitor_replication_lag_count                 | 1              |
| mysql-monitor_replication_lag_use_percona_heartbeat |                |
| mysql-monitor_slave_lag_when_null                   | 60             |
+-----------------------------------------------------+----------------+
6 rows in set (0.01 sec)

• Seconds_Behind_Master < max_replication_lag：表示落后程度尚在允许范围内。
• Seconds_Behind_Master > max_replication_lag：表示落后太多，这样的节点应该避开。

只有传统复制结构的slave节点才需要设置max_replication_lag字段，master无需设置，组复制和galera也无需设置，因为这两种复制结构中show slave status的结果为空。例如，将读组中的所有节点都设置最多落后于master 10秒钟。

update mysql_servers set max_replication_lag=10 where hostgroup_id=20 and hostname="192.168.20.201"; # 确保条件限定从库
load mysql servers to runtime;
save mysql servers to disk;

select hostgroup_id,hostname,port,max_replication_lag from mysql_servers;

需要注意的是，Seconds_Behind_Master的值并不总是可靠的，见 https://dev.mysql.com/doc/refman/5.7/en/show-slave-status.html 。

意思是说主从数据库中，从库执行命令：show slave status的结果中有个参数：Seconds_Behind_Master

proxysql中mysql_servers表中有个字段max_replication_lag，这俩值相比较。

监控模块负责对后端进行一系列检查。它目前支持 4 种类型的检查：
1）connect ==> 它连接到所有后端 MySQL 服务，成功 / 失败将记录在表 mysql_server_connect_log 中；
2）ping ==> 它 ping 到所有后端的 MySQL 服务，并在表 mysql_server_ping_log 中记录成功 / 失败。如果丢失心跳的次数超过 mysql-monitor_ping_max_failures 值，则向 MySQL_Hostgroups_Manager 发送信号以终止所有连接；
3）replication lag ==> 它将检查配置了 max_replication_lag 大于 0 的所有后端 MySQL 的 Seconds_Behind_Master 值，并将检查结果记录在表 mysql_server_replication_lag_log 中。如果 Seconds_Behind_Master > max_replication_lag 则服务器被忽略，直到 Seconds_Behind_Master < max_replication_lag;
4）read only ==> 它检查表 mysql_replication_hostgroups 内记录的主机组中所有主机的 read_only 参数值，并将检查结果在记录表 mysql_server_read_only_log 中。

解决办法：
1.上述报错是因为主库也设置了max_replication_lag的值，更新为0。
2.修改proxysql中mysql_servers表中的从库Mmax_replication_lag的值为10.

最终效果

mysql> select hostgroup_id,hostname,port,max_replication_lag from mysql_servers;
+--------------+----------------+------+---------------------+
| hostgroup_id | hostname       | port | max_replication_lag |
+--------------+----------------+------+---------------------+
| 100          | 192.168.20.200 | 3306 | 0                   | # 主库
| 1000         | 192.168.20.201 | 3306 | 60                  | # 从库
| 1000         | 192.168.20.200 | 3306 | 0                   | # 主库
+--------------+----------------+------+---------------------+
3 rows in set (0.00 sec)