ProxySQL 2.6与Percona 8集群caching_sha2_password插件连接异常求助

连续4天遭遇ProxySQL+MySQL集群的异常问题：按照官方文档中导入caching_sha2_password的步骤操作后，PHP脚本通过ProxySQL连接时失败；但先通过mysql客户端连接ProxySQL后，再执行PHP脚本即可成功。更新用户密码并同步至ProxySQL后，该现象重复出现。

环境信息 #

• 操作系统：Red Hat Enterprise Linux release 9.4 (Plow) - Oracle Linux 9
• MySQL集群：3节点（1主2从），版本：Server version: 8.0.36-28 Percona Server (GPL), Release 28, Revision 47601f19
• ProxySQL版本：ProxySQL version 2.6.3-percona-1.1, codename Truls
• 全局变量：

# mysql -uadmin -p -h 0.0.0.0 -P6032 --prompt='Admin> ' -s -e 'show global variables' | grep -e mysql-default_authentication -e mysql-have_ssl
Enter password:
mysql-default_authentication_plugin caching_sha2_password
mysql-have_ssl  true

操作步骤及结果 #

1. 从MySQL获取用户密码哈希 #

mysql> select hex(authentication_string) from user where user='ftptest';
+----------------------------------------------------------------------------------------------------------------------------------------------+
| hex(authentication_string)                                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------+
|     244124303035243E257A64062E3C10397A582F1345503C6D2A66174865582E75797163574B4F4C58322E5A665A645A7A564A5476507A612F304648726E646943544334324B32 |
+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2. 将用户密码插入ProxySQL并生效 #

Admin> insert into mysql_users (username, password, active, use_ssl) values ('ftptest', UNHEX('244124303035243E257A64062E3C10397A582F1345503C6D2A66174865582E75797163574B4F4C58322E5A665A645A7A564A5476507A612F304648726E646943544334324B32'), 1, 0);
Admin> load mysql users to runtime;
Admin> save mysql users to disk;

3. 编写PHP测试脚本 #

# cat test.php
#!/usr/bin/php
<?php
    print("Connect with ftptest\n");
    if( ! $c = new PDO( "mysql:host=10.200.72.135;dbname=mysql;charset=utf8;", "ftptest", "mynewpassword" ) ) throw new Exception("Can't open MySQL");
    print(">>>>>> ftptest connected\n");

4. 执行PHP脚本失败 #

# php test.php
Connect with unix-prov-mysql
>>>>>> unix-prov-mysql connected
Connect with ftptest
PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000] [2006] MySQL server has gone away in /root/test.php:7
Stack trace:
#0 /root/test.php(7): PDO->__construct()
#1 {main}
  thrown in /root/test.php on line 7

5. ProxySQL日志报错 #

2024-07-26 11:53:12 [INFO] Received load mysql users to runtime command
2024-07-26 11:53:12 [INFO] Computed checksum for 'LOAD MYSQL USERS TO RUNTIME' was '0x3F78E03252E9AD09', with epoch '1722009192'
2024-07-26 11:53:16 [INFO] Received save mysql users to disk command
2024-07-26 11:53:27 MySQL_Protocol.cpp:1470:PPHR_1(): [ERROR] User 'ftptest'@'10.200.72.135' is disconnecting during switch auth
2024-07-26 11:53:27 MySQL_Session.cpp:5779:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE_WrongCredentials(): [ERROR] ProxySQL Error: Access denied for user 'ftptest'@'10.200.72.135' (using password: YES)

6. 使用mysql客户端连接ProxySQL成功 #

# mysql -u ftptest -p -h 10.200.72.135 -s
Enter password: mynewpassword
mysql>

7. 再次执行PHP脚本成功 #

# php test.php
Connect with ftptest
>>>>>> ftptest connected

密码更新后重复异常 #

1. 在MySQL主节点更新用户密码 #

mysql> alter user 'ftptest' identified by 'mypassword';
Query OK, 0 rows affected (0.01 sec)
mysql> select hex(authentication_string) from user where user='ftptest';
+----------------------------------------------------------------------------------------------------------------------------------------------+
| hex(authentication_string)                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| 24412430303524564D58543350161E30197553035E39140230384E624B4F527567667A6B314249333443355754362E6B567555357350376951576F566467374E625637686233 |
+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2. 更新ProxySQL中用户密码并生效 #

Admin> update mysql_users set password=UNHEX('24412430303524564D58543350161E30197553035E39140230384E624B4F527567667A6B314249333443355754362E6B567555357350376951576F566467374E625637686233') where username = 'ftptest';
Admin> load mysql users to runtime;
Admin> save mysql users to disk;

3. 异常复现 #

执行PHP脚本失败，日志报错与之前一致；使用mysql客户端连接ProxySQL后，再次执行PHP脚本成功。

请问该如何解决此异常问题？非常感谢！

内容的提问来源于stack exchange，提问作者VictorP