使用Pemelo连接MySQL时用户主机匹配错误的问题求助

我正在通过ASP.NET Core应用使用Pemelo连接远程MySQL数据库，连接字符串配置如下：

"ConnectionStrings": {
  "WebApiDatabase": "Server=<serverIp>;Port=3306;user='<myUser>';Pwd=<myPw>;Initial Catalog=<database>;"
},

但收到错误提示：Access denied for user '<myUser>'@'localhost' (using password: YES)。明明连接的是远程数据库，却显示从localhost发起连接。

在MySQL Workbench执行SELECT USER(),CURRENT_USER();返回'<myUser>@<windowsUserName>', '<myUser>@<Ipaddress>'，应用应匹配这两个主机之一。若将连接字符串中的用户改为<myUser>@<windowsUserName>，则报错：Access denied for user '<myUser>@<windowsUserName>'@'<windowsUserName>' (using password: YES)。

附上Program.cs中调用连接字符串的代码及完整错误栈：

Program.cs代码 #

string verbindungsString = builder.Configuration.GetConnectionString("WebApiDatabase");
builder.Services.AddDbContext<EtikettenContext>(options =>
        options.UseMySql(verbindungsString, ServerVersion.AutoDetect(verbindungsString),
                options => options.EnableRetryOnFailure(
                    maxRetryCount: 5,
                    maxRetryDelay: System.TimeSpan.FromSeconds(30),
                    errorNumbersToAdd: null))
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors());

完整错误信息 #

Access denied for user 'etikettUser'@'localhost' (using password: YES)

   at MySqlConnector.Core.ServerSession.ConnectAsync(ConnectionSettings cs, MySqlConnection connection, Int32 startTickCount, ILoadBalancer loadBalancer, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 568

   at MySqlConnector.Core.ConnectionPool.ConnectSessionAsync(MySqlConnection connection, String logMessage, Int32 startTickCount, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 403

   at MySqlConnector.Core.ConnectionPool.ConnectSessionAsync(MySqlConnection connection, String logMessage, Int32 startTickCount, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 408

   at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int32 startTickCount, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 98

   at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int32 startTickCount, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 128

   at MySqlConnector.MySqlConnection.CreateSessionAsync(ConnectionPool pool, Int32 startTickCount, Activity activity, Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 929

   at MySqlConnector.MySqlConnection.OpenAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 423

   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)

   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlRelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)

   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<>c__DisplayClass30_0`2.<ExecuteAsync>b__0d.MoveNext()

--- End of stack trace from previous location ---

   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

   at System.Text.Json.Serialization.Converters.IAsyncEnumerableOfTConverter`2.OnWriteResume(Utf8JsonWriter writer, TAsyncEnumerable value, JsonSerializerOptions options, WriteStack& state)

   at System.Text.Json.Serialization.JsonCollectionConverter`2.OnTryWrite(Utf8JsonWriter writer, TCollection value, JsonSerializerOptions options, WriteStack& state)

   at System.Text.Json.Serialization.JsonConverter`1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)

   at System.Text.Json.Serialization.JsonConverter`1.WriteCore(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)

   at System.Text.Json.Serialization.JsonConverter`1.WriteCoreAsObject(Utf8JsonWriter writer, Object value, JsonSerializerOptions options, WriteStack& state)

   at System.Text.Json.JsonSerializer.WriteCore[TValue](Utf8JsonWriter writer, TValue& value, JsonTypeInfo jsonTypeInfo, WriteStack& state)

   at System.Text.Json.JsonSerializer.WriteStreamAsync[TValue](Stream utf8Json, TValue value, JsonTypeInfo jsonTypeInfo, CancellationToken cancellationToken)

   at System.Text.Json.JsonSerializer.WriteStreamAsync[TValue](Stream utf8Json, TValue value, JsonTypeInfo jsonTypeInfo, CancellationToken cancellationToken)

   at System.Text.Json.JsonSerializer.WriteStreamAsync[TValue](Stream utf8Json, TValue value, JsonTypeInfo jsonTypeInfo, CancellationToken cancellationToken)

   at Microsoft.AspNetCore.Mvc.Formatters.SystemTextJsonOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|30_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeResultFilters>g__Awaited|28_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)

   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)

   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)

   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)

   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)

   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)


HEADERS

=======

Accept: text/plain

Host: localhost:7041

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:109.0) Gecko/20100101 Firefox/116.0

:method: GET

Accept-Encoding: gzip, deflate, br

Accept-Language: de,en-US;q=0.7,en;q=0.3

Cookie: PHPSESSID=2tr9dnf5vkacnspngoa54l7qga

Referer: https://localhost:7041/swagger/index.html

TE: trailers

sec-fetch-dest: empty

sec-fetch-mode: cors

sec-fetch-site: same-origin

1. 修正连接字符串的用户参数 #

连接字符串中user='<myUser>'的单引号是多余的，MySqlConnector会将单引号作为用户名的一部分传递给MySQL服务器，导致服务器找不到匹配的账号。修改后的连接字符串：

"ConnectionStrings": {
  "WebApiDatabase": "Server=<serverIp>;Port=3306;User=<myUser>;Pwd=<myPw>;Initial Catalog=<database>;"
}

注意：参数名不区分大小写（User或user都可以），但不要添加单引号或双引号包裹用户名。

2. 确保MySQL服务器上的账号匹配客户端来源IP #

MySQL的账号格式是用户名@主机地址，这里的主机地址是客户端的IP（即你的ASP.NET Core应用所在机器的公网/局域网IP），而不是远程服务器的IP。

• 用Workbench登录远程MySQL后，执行以下命令查看现有账号：

  SELECT user, host FROM mysql.user WHERE user = '<myUser>';
  

• 如果没有匹配你客户端IP的账号，创建一个（替换<你的客户端IP>）：

  CREATE USER '<myUser>'@'<你的客户端IP>' IDENTIFIED BY '<myPw>';
  GRANT ALL PRIVILEGES ON <database>.* TO '<myUser>'@'<你的客户端IP>';
  FLUSH PRIVILEGES;
  

• 若需要临时测试，可允许从任何主机连接（生产环境不建议）：

  CREATE USER '<myUser>'@'%' IDENTIFIED BY '<myPw>';
  GRANT ALL PRIVILEGES ON <database>.* TO '<myUser>'@'%';
  FLUSH PRIVILEGES;
  

3. 验证账号权限 #

执行以下命令确认账号拥有目标数据库的权限：

SHOW GRANTS FOR '<myUser>'@'<你的客户端IP>';

4. 确认连接字符串的Server参数正确 #

确保Server=<serverIp>中的<serverIp>确实是远程MySQL服务器的公网/局域网IP，不要误写为localhost或127.0.0.1（这会连接本地MySQL实例）。

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