old pre-4.1 authentication protocol not supported-Go服务器开发

这个错误是因为 MySQL 客户端和服务器之间的认证协议不兼容。以下是几种解决方案：

1. 升级 MySQL 用户密码认证方式

-- 查看当前用户认证插件
SELECT user, host, plugin FROM mysql.user WHERE user = 'your_username';

-- 将用户密码升级为新的认证方式（MySQL 8.0+）
ALTER USER 'your_username'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password';
ALTER USER 'your_username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

-- 或者使用 caching_sha2_password（推荐用于新系统）
ALTER USER 'your_username'@'%' IDENTIFIED WITH caching_sha2_password BY 'your_password';

-- 刷新权限
FLUSH PRIVILEGES;

2. 在 OpenResty 连接配置中指定认证插件

local mysql = require "resty.mysql"

local db, err = mysql:new()
if not db then
    ngx.say("Failed to create mysql object: ", err)
    return
end

db:set_timeout(1000)

-- 连接时指定 auth_plugin
local ok, err, errcode, sqlstate = db:connect{
    host = "127.0.0.1",
    port = 3306,
    database = "your_database",
    user = "your_username",
    password = "your_password",
    charset = "utf8mb4",
    auth_plugin = "mysql_native_password"  -- 明确指定认证插件
}

if not ok then
    ngx.say("Failed to connect: ", err, " : ", errcode, " ", sqlstate)
    return
end

3. MySQL 服务器配置修改

在 my.cnf 配置文件中添加：

[mysqld]
default-authentication-plugin=mysql_native_password

# 或者对于 MySQL 8.0+
default-authentication-plugin=caching_sha2_password

# 允许旧协议（不推荐，安全性较低）
secure-auth=off

4. lua-resty-mysql 版本检查和处理

location /test-mysql {
    content_by_lua_block {
        local mysql = require "resty.mysql"
        
        -- 检查 lua-resty-mysql 版本是否支持新认证协议
        local db = mysql:new()
        
        -- 尝试不同的连接选项
        local connect_opts = {
            host = "127.0.0.1",
            port = 3306,
            database = "test_db",
            user = "test_user",
            password = "test_pass", 
            charset = "utf8mb4",
            auth_plugin = "mysql_native_password"
        }
        
        local ok, err, errcode, sqlstate = db:connect(connect_opts)
        
        if not ok then
            -- 如果仍然失败，尝试其他方法
            ngx.log(ngx.ERR, "MySQL connection failed: ", err)
            
            -- fallback：创建支持旧协议的用户
            ngx.say("Connection failed: ", err)
            return
        end
        
        ngx.say("Connected successfully")
        
        db:close()
    }
}

5. Docker MySQL的特殊处理

如果使用 Docker，启动时指定认证插件：

docker run -d \
    --name mysql \
    -e MYSQL_ROOT_PASSWORD=password \  
    -e MYSQL_ROOT_HOST=% \
    -e MYSQL_AUTHENTICATION_PLUGIN=mysql_native_password \
    -p 3306:3306 \
    mysql:8.0
    
# Or for older versions:
docker run -d \
    --name mysql \
    -e MYSQL_ROOT_PASSWORD=password \  
    -p  3306:3306 \  
     mysql:5.7 --default-authentication-plugin=mysql_native_pasword

6 .完整的错误处理和重连机制

local function create_mysql_connection()  
      local   mysql   =.require ("resty.mysql")   
      
       local   db,.err.=mysql:.new()    
       if.not.db.then         
             return.nil,"Failed.to.create.MySQL.object:"..tostring(err)     
       end          
       
       db:.set_timeout(30000)--30秒超时           
       
         local.opts.= {         
              host.=."127 .0 .01",          
              port.=33060 ,            
               database.=."my_app",             
                user.=."app_user",              
                 password.=."secure_pass",                
                  charset.=."utf8mb4",                 
                   max_packet_size.=10240*10240,--10MB                    auth._plugin=."mysql._native._password"--强制使用native.password                
                    pool=."my_conn_pool",                 
                     pool_size=.100                
                      }              
                      
           local.ok,.err,.errcode,.sqlstate=.db:.connect(opts)           
           if.not.ok.then               
            
                 return.nil,"Connection.failed:"..tostring(err).." code:"..tostring(errcode)          
           end            
           
           --验证连接有效         
           local.res,.err=.db:.query("SELECT11")         
           if.not.res.then             
                db:.close()              
                return.nil,"Connection.test.failed:"..tostring(err)          
           end            
           
          return.db 
end 

--使用函数创建连接 
local.db,.err=.create._mysql._connection() 

if.not.db.then     
      ngx.log(ngx .ERR,"MySQL.init.error:",err)     
      ngx.exit(500) 
end

💡关键点总结: