容器中MySQL服务器远程连接失败排查求助

Let’s dive into why you can connect via MySQL Workbench but get an ERROR 1045 with the mysql CLI command, even after setting up users and permissions. Here are the key areas to check:

1. Fix the Username Mismatch in Your CLI Command #

Wait a critical detail: you created the user prodigy (both prodigy@'%' and prodigy@'<my_laptops_ip>'), but your connection command uses -u user. That’s a direct mismatch! If you didn’t actually create a user named user, this is almost certainly the root cause.

Run this corrected command instead:

mysql -u prodigy -h <my-server-ip> -p

Enter the password passwd when prompted, and see if it connects.

2. Check CLI Client Compatibility with caching_sha2_password #

Looking at your user’s permissions, the plugin is set to caching_sha2_password—MySQL 8.0’s default authentication method. Older versions of the mysql CLI client (pre-8.0) don’t support this plugin natively, which can cause access denied errors even with correct credentials. Workbench likely handles this plugin fine, which explains why it works.

To test this:

• Option 1: Update your local mysql CLI client to version 8.0 or newer.
• Option 2: Temporarily switch the user’s plugin to the more compatible mysql_native_password:

  ALTER USER 'prodigy'@'%' IDENTIFIED WITH mysql_native_password BY 'passwd';
  ALTER USER 'prodigy'@'<my_laptops_ip>' IDENTIFIED WITH mysql_native_password BY 'passwd';
  FLUSH PRIVILEGES;
  

Try the CLI connection again. If this works, you can choose to keep the compatible plugin or update your client.

3. Ensure the IP-Specific User Has Full Privileges #

MySQL prioritizes exact user matches over wildcard ones. When your laptop connects from its specific IP, it uses prodigy@'<my_laptops_ip>' instead of prodigy@'%'. You granted full privileges to the wildcard user, but did you apply the same to the IP-specific user?

Run these commands in the MySQL container as root:

GRANT ALL PRIVILEGES ON *.* TO 'prodigy'@'<my_laptops_ip>';
FLUSH PRIVILEGES;

Then retry the CLI connection.

4. Verify Network and Container Configuration (Quick Check) #

Since Workbench connects successfully, this is less likely, but it’s worth confirming:

• Ensure your Docker run command correctly maps port 3306 (e.g., -p 3306:3306).
• Inside the container, check your MySQL config (my.cnf/my.ini) to make sure bind-address is set to 0.0.0.0 (not 127.0.0.1), which allows remote connections.

5. Rule Out Local Firewall/Proxy Interference #

Sometimes local firewalls, VPNs, or proxies block CLI traffic even if they let Workbench through. Try:

• Disabling your laptop’s firewall temporarily to test the connection.
• Turning off any VPN or proxy you’re using, then retrying the CLI command.

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