Docker部署官方MariaDB镜像后CLI连接提示权限拒绝的问题

问题背景 #

使用官方MariaDB镜像创建Docker容器后，多种CLI连接方式均失败，收到权限拒绝警告，尝试镜像文档示例也无法解决。

尝试的连接命令 #

• mariadb --host=localhost --port=3306 --user=root --password
• mariadb --host=0.0.0.0 --port=3306 --user=root --password

收到的错误信息 #

2024-01-23T06:43:39.612639215Z 2024-01-23 6:43:39 3 [Warning] Access denied for user 'root'@'172.25.0.1' (using password: NO)

Docker Compose配置 #

mariadb:
    container_name: mariadb
    image: mariadb:latest
    restart: always
    ports:
        - '3306:3306'
    environment:
        MARIADB_ALLOW_EMPTY_ROOT_PASSWORD: 1

MariaDB容器日志 #

2024-01-23 06:43:30+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:11.2.2+maria~ubu2204 started.
2024-01-23T06:43:30.288290873Z 2024-01-23 06:43:30+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2024-01-23T06:43:30.292507092Z 2024-01-23 06:43:30+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:11.2.2+maria~ubu2204 started.
2024-01-23T06:43:30.496575650Z 2024-01-23 06:43:30+00:00 [Note] [Entrypoint]: MariaDB upgrade not required
2024-01-23T06:43:30.517061698Z 2024-01-23  6:43:30 0 [Note] Starting MariaDB 11.2.2-MariaDB-1:11.2.2+maria~ubu2204 source revision 929532a9426d085111c24c63de9c23cc54382259 as process 1
2024-01-23T06:43:30.526481981Z 2024-01-23  6:43:30 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2024-01-23T06:43:30.527498693Z 2024-01-23  6:43:30 0 [Note] InnoDB: Number of transaction pools: 1
2024-01-23T06:43:30.527507199Z 2024-01-23  6:43:30 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2024-01-23T06:43:30.527539363Z 2024-01-23  6:43:30 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
2024-01-23T06:43:30.527880276Z 2024-01-23  6:43:30 0 [Note] InnoDB: Using liburing
2024-01-23T06:43:30.528617763Z 2024-01-23  6:43:30 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
2024-01-23T06:43:30.536273814Z 2024-01-23  6:43:30 0 [Note] InnoDB: Completed initialization of buffer pool
2024-01-23T06:43:30.543930878Z 2024-01-23  6:43:30 0 [Note] InnoDB: File system buffers for log disabled (block size=512 bytes)
2024-01-23T06:43:30.547410093Z 2024-01-23  6:43:30 0 [Note] InnoDB: End of log at LSN=47629
2024-01-23T06:43:30.604918093Z 2024-01-23  6:43:30 0 [Note] InnoDB: Opened 3 undo tablespaces
2024-01-23T06:43:30.604933398Z 2024-01-23  6:43:30 0 [Note] InnoDB: 128 rollback segments in 3 undo tablespaces are active.
2024-01-23T06:43:30.605217501Z 2024-01-23  6:43:30 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
2024-01-23T06:43:30.605249473Z 2024-01-23  6:43:30 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
2024-01-23T06:43:30.606086442Z 2024-01-23  6:43:30 0 [Note] InnoDB: log sequence number 47629; transaction id 14
2024-01-23T06:43:30.606192280Z 2024-01-23  6:43:30 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2024-01-23T06:43:30.606235965Z 2024-01-23  6:43:30 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-01-23T06:43:30.606269115Z 2024-01-23  6:43:30 0 [Note] Plugin 'wsrep-provider' is disabled.
2024-01-23T06:43:30.608103966Z 2024-01-23  6:43:30 0 [Note] InnoDB: Buffer pool(s) load completed at 240123  6:43:30
2024-01-23T06:43:30.611498837Z 2024-01-23  6:43:30 0 [Note] Server socket created on IP: '0.0.0.0'.
2024-01-23T06:43:30.611511280Z 2024-01-23  6:43:30 0 [Note] Server socket created on IP: '::'.
2024-01-23T06:43:30.612304959Z 2024-01-23  6:43:30 0 [Warning] 'user' entry 'root@%' has a wrong 'access' value 0xffffffffff (allowed mask is 0x7fffffffff, version_id=110301)
2024-01-23T06:43:30.613773685Z 2024-01-23  6:43:30 0 [Note] mariadbd: Event Scheduler: Loaded 0 events
2024-01-23T06:43:30.614026109Z 2024-01-23  6:43:30 0 [Note] mariadbd: ready for connections.

解决方案 #

1. 修正密码输入操作 #

错误信息明确显示using password: NO，说明连接时未正确处理空密码逻辑。执行--password参数后，当提示输入密码时，直接按回车即可（不要输入任何内容），因为你已配置允许空密码。

2. 修复用户权限掩码异常 #

容器日志中的警告'user' entry 'root@%' has a wrong 'access' value是权限配置错误导致的，按以下步骤修复：

• 进入容器内部：

  docker exec -it mariadb bash
  

• 在容器内本地连接MariaDB（无需密码）：

  mariadb -u root
  

• 重置root用户权限配置：

  ALTER USER 'root'@'%' IDENTIFIED BY '';
  FLUSH PRIVILEGES;
  

3. 简化连接命令 #

完成上述步骤后，尝试使用简化命令连接：

mariadb -h localhost -P 3306 -u root -p

提示输入密码时直接回车。

4. 清理残留数据（若问题持续） #

如果是旧容器数据残留导致的问题，彻底清理后重新创建容器：

• 停止并删除容器及关联数据卷：

  docker-compose down -v
  

• 重新启动容器：

  docker-compose up -d
  

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