使用 MySQL 5.6+ 客戶端連接 5.5 版 Server 時,常遇 OperationalError: (2049, "Connection using old (pre-4.1.1) authentication protocol refused")。根因是Server 端用戶密碼仍為舊 16 位十六進位雜湊,而新版客戶端預設要求新 41 位雜湊(含 * 前綴)。本文提供標準修復流程。
OperationalError: (2049, "Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)")
檢查 mysql.user 表:
mysql> SELECT User, Password, Host FROM mysql.user WHERE user = 'root';
+------+------------------+-----------+
| User | Password | Host |
+------+------------------+-----------+
| root | 67457e226a1a15bd | % |
| root | 67457e226a1a15bd | 127.0.0.1 |
| root | 67457e226a1a15bd | localhost |
+------+------------------+-----------+
67457e226a1a15bd)* 開頭(如 *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B)MySQL 5.6+ 客戶端預設啟用 secure_auth,拒絕舊格式密碼。SHOW WARNINGS 也會提示:'pre-4.1 password hash' is deprecated。
skip-secure-auth 重啟)PASSWORD() 加密):UPDATE mysql.user SET Password = PASSWORD('root') WHERE User = 'root';
FLUSH PRIVILEGES;
驗證新格式:
mysql> SELECT User, Password, Host FROM mysql.user WHERE user = 'root';
+------+-------------------------------------------+-----------+
| User | Password | Host |
+------+-------------------------------------------+-----------+
| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | % |
| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | 127.0.0.1 |
| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | localhost |
+------+-------------------------------------------+-----------+
FLUSH PRIVILEGES 後重新連線測試)--skip-secure-auth(僅繞過檢查,不解決根本問題)secure_auth=OFF(降低安全性,官方已棄用)升級 Server 端密碼格式後,Python 代碼無需修改即可正常連線:
import MySQLdb
conn = MySQLdb.connect(host='localhost', user='root', passwd='root', db='athena', charset='utf8')
ERROR 2049 本質是密碼雜湊版本不匹配。標準解法是在 Server 端用 PASSWORD() 重新生成新格式雜湊,一次性解決所有新版客戶端連線問題,且符合安全最佳實踐。