Docker Compose部署MySQL 8.0.23拒绝本地及外部连接问题求助

在Ubuntu 20.04.5 LTS服务器上使用Docker 20.10.21运行mysql/mysql-server:8.0.23容器，重启后本地及外部连接均被拒绝，报错：

ERROR 1130 (HY000): Host '<ip address>' is not allowed to connect to this MySQL server

已尝试修改docker-compose配置、调整初始化SQL、删除容器卷重新初始化，容器启动无报错，数据库和用户看似已创建，但连接始终失败。

当前docker-compose配置 #

version: '3'

services:
  mysql-stuff:
    image: mysql/mysql-server:8.0.23
    restart: unless-stopped
    ports:
      - "3306:3306"
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: "yes"
      MYSQL_ROOT_PASSWORD: "rootpwd"
      MYSQL_DATABASE: "thing_one"
      MYSQL_USER: "thing_one"
      MYSQL_PASSWORD: "thing_one_pwd"
    volumes:
      - mysql-stuff-volume:/var/lib/mysql
      - ./mysql/conf/db_init:/docker-entrypoint-initdb.d
    command:
      --default-authentication-plugin=mysql_native_password
      --bind-address=0.0.0.0
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
      timeout: 10s
      retries: 10

volumes:
  mysql-stuff-volume:
    external: true

初始化SQL文件（./mysql/conf/db_init/01.sql）内容 #

/* CREATE USER 'thing_one'@'%' IDENTIFIED BY 'thing_one_pwd'; */
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'rootpwd';
FLUSH PRIVILEGES;
ALTER USER 'thing_one'@'%' IDENTIFIED WITH mysql_native_password BY 'thing_one_pwd';
CREATE DATABASE IF NOT EXISTS thing_one;
GRANT ALL PRIVILEGES ON thing_one.* TO 'thing_one'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
CREATE USER 'stuff'@'%' IDENTIFIED WITH mysql_native_password BY  'rootpwd';
GRANT ALL PRIVILEGES ON *.* TO 'stuff'@'%' WITH GRANT OPTION;
UPDATE mysql.user SET host='%' WHERE user='root';
UPDATE mysql.user SET host='%' WHERE user='thing_one';
UPDATE mysql.user SET host='%' WHERE user='stuff';
FLUSH PRIVILEGES;

错误分析与修复方案 #

核心问题点 #

1. 环境变量冲突：同时设置MYSQL_ALLOW_EMPTY_PASSWORD: "yes"和MYSQL_ROOT_PASSWORD: "rootpwd"，MySQL会优先允许空密码，导致root用户密码配置失效，后续权限修改逻辑混乱。
2. 直接修改权限表：MySQL 8.0+不推荐直接UPDATE mysql.user表修改主机属性，这会绕过权限系统的缓存与校验，导致权限数据不一致。
3. 用户主机配置错误：将root@localhost的主机改为%，导致本地root连接失效，进而使未指定密码的健康检查失败（容器可能处于不健康状态但仍运行）。
4. 健康检查配置缺失：健康检查命令未提供root密码，无法正常完成ping校验。

修复步骤 #

1. 清理旧数据卷

由于现有卷已存在错误的权限配置，需彻底清除后重新初始化：

docker-compose down
docker volume rm mysql-stuff-volume
docker-compose up -d

2. 修正docker-compose配置

• 删除MYSQL_ALLOW_EMPTY_PASSWORD变量，避免与MYSQL_ROOT_PASSWORD冲突
• 更新健康检查命令，添加root用户名和密码
  修改后的配置片段：

environment:
  MYSQL_ROOT_PASSWORD: "rootpwd"
  MYSQL_DATABASE: "thing_one"
  MYSQL_USER: "thing_one"
  MYSQL_PASSWORD: "thing_one_pwd"

healthcheck:
  test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-uroot", "-prootpwd"]
  timeout: 10s
  retries: 10

3. 修正初始化SQL文件

使用MySQL官方推荐的ALTER USER命令修改用户属性，避免直接操作权限表：

-- 配置root用户支持远程连接并设置认证插件
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'rootpwd';
CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'rootpwd';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

-- 修正thing_one用户的认证插件（由MYSQL_USER自动创建为thing_one@%）
ALTER USER 'thing_one'@'%' IDENTIFIED WITH mysql_native_password BY 'thing_one_pwd';
GRANT ALL PRIVILEGES ON thing_one.* TO 'thing_one'@'%';

-- 创建并配置stuff用户
CREATE USER IF NOT EXISTS 'stuff'@'%' IDENTIFIED WITH mysql_native_password BY 'rootpwd';
GRANT ALL PRIVILEGES ON *.* TO 'stuff'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

4. 验证连接

容器重启完成后，尝试远程连接：

mysql -h <服务器IP> -u root -prootpwd
mysql -h <服务器IP> -u thing_one -pthing_one_pwd

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