ER_HOST_NOT_PRIVILEGED - docker 容器无法连接到 mariadb

2022-01-15 00:00:00 docker docker-compose mariadb mysql

我正在尝试让 docker 容器与 mariadb 和 node.js 图像一起使用.容器将使用 /home/mysql 中的现有数据库.但是，当容器启动时，我在 node.js 中收到此连接失败"错误:

I'm trying to get a docker container to work with mariadb and node.js images. The container will use an existing database in /home/mysql. However, when the container is launched, I'm getting this "failed to connect" error in node.js:

Error: ER_HOST_NOT_PRIVILEGED: 
Host '172.18.0.5' is not allowed to connect to this MariaDB server

这是我的 docker-compose.yml:

Here's my docker-compose.yml:

version: '3'
services:
  mariadb:
    image: mariadb
    restart: always
    volumes:
     - /home/mysql:/var/lib/mysql
    user: "mysql"
    ports:
      - "3306:3306"
  watch:
    build: .
    restart: always
    links:
      - mariadb:mysql
    environment:
      - DOCKER_IP=172.18.0.2
    depends_on: ['mariadb']
    ports:
      - "3000:3000"

看完这个线程，发现mysql其实是在运行的，但它无法让其他服务连接:

After reading this thread, I found that mysql is actually running, but it fails to let other services connect:

这些是我检查过的一些步骤.可以看到，我可以登录mysql实例了:

These are some of the steps I have checked. As you can see, I can log in to the mysql instance:

    $ docker exec -it 552aae9ea09c bash

    mysql@552aae9ea09c:/$ mysql -u root -p
    Enter password: *******

    MariaDB [(none)]> SELECT host, user FROM mysql.user;
    +-------------+------------------+
    | host        | user             |
    +-------------+------------------+
    | 127.0.0.1   | root             |
    | ::1         | root             |
    | someusername|                  |
    | someusername| root             |
    | localhost   |                  |
    | localhost   | dbusername       |
    | localhost   | databasename     |
    | localhost   | root             |
    +-------------+------------------+
    8 rows in set (0.00 sec)


mysql@552aae9ea09c:/$ mysqld --verbose --help | grep bind-address

2017-11-13 17:35:40 139825857279872 [Note] Plugin 'FEEDBACK' is disabled.
  --bind-address=name IP address to bind to.

bind-address                                       (No default value)

需要注意的一点是，即使我在yml文件中明确设置了用户为mysql，/home/mysql中的这三个文件:ib_logfile0，ib_logfile1、ib_buffer_pool 还在 systemd-journal-remote 组下，我怀疑这与连接失败有关.(参考)

One thing to note is that even though I've explicitly set the user to mysql in the yml file, these three files in /home/mysql: ib_logfile0,ib_logfile1, ib_buffer_pool are still under the group of systemd-journal-remote, which I suspect has something to do with the connection failure.(reference)

推荐答案

您收到的错误是由于 MariaDB 认为您无权连接到服务器造成的.这意味着您尚未为 Node.js 应用创建数据库用户，或者该用户的授权不正确.

The error you are receiving is caused by the fact that MariaDB thinks you are not authorized to connect to the server. This means that you haven't created a database user for the Node.js app or the grants for that user are incorrect.

解决这个问题的一个简单的方法是为 Node.js 应用程序创建一个单独的用户.您可以通过将以下 SQL 写入文件并将卷挂载到 /docker-entrypoint-initdb.d 来自动执行此操作.

A fool-proof way to solve this is to create a separate user for the Node.js application. You can automate this by writing the following SQL into a file and mounting the volume into /docker-entrypoint-initdb.d.

CREATE USER 'my-app-user'@'%' IDENTIFIED BY 'my-app-password';
GRANT ALL ON *.* TO 'my-app-user'@'%';

相应地更改用户名和密码，并从 ALL 权限中减少给定的权限.您还可以将通配符主机名 % 更改为特定的 IP 地址或主机名.

Change the username and password accordingly and reduce the given privileges from the ALL privilege. You can also change the wildcard hostname % to a specific IP address or hostname.

相关文章