[译文] Understanding MySQL the Access Denied error in or outside a Docker container

MySQL基于三个数据对用户身份认证：用户名，连接的另一端的IP地址或域名和提供的密码。它在 mysql.user 表搜索这三个值的匹配项。没有匹配项，则返回一个 Access Denied 的错误。这虽然是个简单的模型，但有几个可能的错误配置可能会让调试感到沮丧。

我从上世纪90年代就开始使用MySQL，但从未深入过。例如，典型的Web宿主机提供管理控制面板，可以告诉你用户名和密码，以及使用您请求的任何数据库实例的说明。然后进入命令行并键入类似这样的内容：

1

$ mysql -u finley -h mysql.example.com -p db_name

瞧，你已经连上MySQL数据库了。关键点是你并没有创建用户账户，主机提供做了这些，你不必担心它在MySQL中是如何配置的。同样，可以使用 PHPMyAdmin 来管理MySQL数据库，同样的，它也可以处理创建和删除用户ID，这样，我们就可以在不了解它实际工作原理的情况下愉快地使用该应用程序。

但是，我当前正在更新我的书 Node.js Web开发，我正在写关于部署 Node.js 应用的章节。该章节包括一个很长的部分：展示如何使用Docker，特别是如何设置一对Docker容器，一个运行MySQL服务器，另一个运行MySQL服务器 Node.js 应用程序。这一章最初是四年前写的，我做了一个相当简单的更新，但是它不工作了，这要拜 Access denied for user (using password: YES) 的错误所赐，无论我尝试了多少办法，阅读了多少 stackoverflow 文章！

最后，在仔细阅读 MySQL 文档之后，这个神秘的主题更有意义。我从某个地方找到了MySQL的片段：

1
2
3
4
5

sudo mysql --user=root  <<EOF
CREATE DATABASE userauth;
CREATE USER 'userauth'@'localhost' IDENTIFIED BY 'userauth';
GRANT ALL PRIVILEGES ON userauth.* TO 'userauth'@'localhost' WITH GRANT OPTION;
EOF

这时发生了什么是相当的清晰，但它实际的工作，以及出现错误时如何诊断还有一点儿神秘。为了说明这一点，该代码片段执行以下操作：

  • 创建一个数据库
  • 使用用户名，主机名和密码（Identified By 意味着密码）创建一个用户
  • 赋予刚才创建的用户访问这个数据库的所有权限

有些不明显的事情是用户 'userauth'@'localhost'是否与另一个用户相关，比如 'userauth'@'example.com' ? 真的需要引号吗？授予期权(GRANT OPTION)是什么意思？我们到底要为数据库的远程用户做什么？

在这篇文章的其余部分，我想描述一些经验教训和文档指南，希望能为您节省一些时间。

常见错误-在Docker映像中未正确处理挂载卷的MySQL数据目录

在Docker中，提供一个 MySQL 服务器真正精彩的方式是：

1
2
3
4
5
6
7
8
9

docker run --detach --name db-userauth \
    --env MYSQL_USER=userauth \
    --env MYSQL_PASSWORD=userauth \
    --env MYSQL_DATABASE=userauth  \
    --mount type=bind,src=`pwd`/userauth-data,dst=/var/lib/mysql \
    --network authnet \
    --env MYSQL_ROOT_PASSWORD=w0rdw0rd \
    mysql/mysql-server:8.0 \
    --bind_address=0.0.0.0

这确保创建的容器有个有用的名字，db-SERVICE。我们设置了一个仅对命名表具有特权的用户ID，而对 root 用户的访问仅限于来自localhost的连接。一个重要的事情是使用 --mount 来确保MySQL数据目录不是隐藏在容器中，而是独立的。这样，我们可以在任何喜欢的时间删除并重建容器，而不会丢失数据库。通过将容器连接到 network，我们可以限制容器到 network 上其他容器的连接。通过不暴露MySQL端口（未映射 -p 3306:3306），可以确保数据库仅对虚拟 network 上的其他容器可见。

在运行该命令之前，确保运行了: mkdir userauth-data

错误是，在对容器进行另一次实验时，忘记重新创建此目录

在 MySQL 容器首次启动时（MariaDB容器也是如此），发生的事件是，容器中的脚本运行并初始化一个空白数据库。这些脚本根据数据目录是否为空来决定是否运行。空的数据目录意味着必须初始化数据库，否则脚本将假定存在一个数据库，并且不会初始化新的数据库。

假设您正尝试调试 Access denied for user (using password: YES) ，因此尝试一个又一个的想法？每次更改设置，然后重新创建/重新启动MySQL容器时，必须记住删除，然后创建一个空的数据目录。

如果脚本检测有数据目录，它不会运行，因此您更改用户配置的任何尝试都不会产生任何影响，因为没有重新创建用户，因为数据目录初始化由于存在数据目录而未运行。

1
2
3
4

... Edit MySQL container settings
$ rm -rf userauth-data
$ mkdir userauth-data
$ docker run ... as above

这是你必须遵循的模式。在每次使用MySQL配置的实验中，都要采取这三个步骤。

如何在创建容器时定制初始化数据库？

在容器创建中可以运行一些 SQL 文件。示例你可能有一些初始化数据需要加载，或者你希望定制用户配置，或者创建额外的数据库。在初始化容器时，可以使用SQL执行任何操作。

简单地在容器中挂载目录 /docker-entrypoint-initdb.d/。该容器必须包含 SQL 脚本。在容器初始化过程中，在该目录中的所有的 SQL 脚本都会被执行。

1
2
3

docker run --name=mysql1 \
  --mount type=bind,src=/path-on-host-machine/scripts/,dst=/docker-entrypoint-initdb.d/ \
  -d mysql/mysql-server:tag

这是一个设置目录的方式

例如，考虑一个名为 create-users.sql 包含：

1
2
3
4
5
6
7
8

CREATE DATABASE userauth;
/* CREATE USER 'userauth'@'localhost' IDENTIFIED BY 'userauth'; */
/* CREATE USER 'userauth'@'172.%.%.%' IDENTIFIED BY 'userauth'; */
CREATE USER 'userauth'@'%' IDENTIFIED BY 'userauth';
/* GRANT ALL PRIVILEGES ON userauth.* TO 'userauth'@'localhost' WITH GRANT OPTION; */
/* GRANT ALL PRIVILEGES ON userauth.* TO 'userauth'@'172.%.%.%' WITH GRANT OPTION; */
GRANT ALL PRIVILEGES ON userauth.* TO 'userauth'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

这是我的一个尝试，自定义如何创建 userauth 帐户，并尝试不同的选项。

在接下来的连接中 MySQL 如何验证用户名和密码？

再次看下这个命令：

1

$ mysql -u finley -h mysql.example.com -p db_name

这个命令我们可以运行在我们的笔记本上，在web的主机服务器上，或者是壁橱里的树莓派上。关键是我们为数据库提供用户名、密码和主机名。mysql程序连接到运行于命名服务器上的数据库。即服务器，mysql.example.com，可能和我们运行命令不在同一个机器上。

任何时候，MySQL 收到一个连接请求，他会搜集这些特征：

  • 提供的用户名和密码
  • 具有TCP/IP数据通道另一端的IP地址的套接字

换句话说，MySQL有这些数据：

a) 用户名

b) 密码

c) 连接来源的IP地址

使用这些数据来匹配表 mysql.user(数据库 mysql中的 table表)的行，但这究竟意味着什么？这些 CREATE USER 和 GRANT 命令明显提到了主机名如 localhost 和 IP 地址。但是MySQL如何解析它？

这里是检查这种情形的第一步：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

mysql> use mysql;
...
mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| userauth      | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

mysql 数据库持有 MySQL 用于自己管理的数据表。user 表列出了这个 MySQL 服务器知道的用户ID。还有其他列，如密码列，但是我们仅展示这些我们讨论关注的列。

MySQL所做的就是在这个表中找到与这三个属性相匹配的条目。

对于用户名和密码，MySQL 简单地进行字符串比较。

而对于 host列，它做一些模式匹配。因为这是一个 SQL 数据库，通配符是 %，值为 % 的主机匹配来自任何主机的连接，而值为 172.20.%.%的主机匹配 172.20.x.x 范围内的任意IP地址。

文档

例如：

  • host 的值可以是域名或IP地址。对于IP地址，它同时支持传统的 IPv4 和新兴的 IPv6 地址格式。
  • %和 _ 都是通配符，它的模式匹配和SQL的 LIKE 操作类似
  • %.com 匹配主机域名在 .com 的任意连接。
  • 198.51.100.% 匹配前三个八进制为 198,51和100的IPv4地址。
  • 'david'@'198.51.100.0/255.255.255.0' 允许掩码匹配

有点常见：用户表配置错误

显然，MySQL可能会被 mysql.user 并产生不可理解的用户认证失败。

在 user@localhost 和 user@172.% 之间有什么不同？

当使用同一个用户从不同的IP地址连接MySQL有什么不同吗？

是的。

考虑一下。MySQL 匹配 mysql.user表中的行。即一个来自 localhost 的连接并不区配置 172.% 的行。用户ID user@localhost,user@foo.com,user@'192.168.1.%' 和 user@'172.%' 在 MySQL 的概念中是完全不同的条目。

另一个因素是 mysql.user 表有一个索引，它是由 user 列和 host 列组成的多列索引。

用户名为空的用户表条目是与任何提供的用户标识相匹配的匿名用户

这可能是一个常见的配置问题，可能会产生意外的结果。

在 mysql.user 表中一个空白用户名的条目匹配任何提供的用户名，但是将连接视为匿名用户。MySQL文档中说：

如果用户的值为空，它匹配任何用户名。如果 user 表的行匹配一个接入的连接有个空白用户名，该用户被认为是没有名字的匿名用户，而不是客户端实际上指定的名字。

最后一点与访问权限 GRANT 声明有关。您的软件可能提供了所需的用户名，期望根据该用户名获得 GRANT 权限，但如果最终匹配匿名用户，则连接将具有匿名用户的 GRANT 权限。

文档：

该页详细描述了 MySQL 是如何验证连接信息的。

MySQL 排序用户表

根据文档：

  • 无论何时，服务器读取用户表到内存，都排序行。
  • 无论何时，客户端尝试连接，服务器将按排序顺序查看行。
  • 服务器使用与客户端主机名和用户名匹配的第一行。

因此 mysql.user 数据库的解释顺序与前面的 SELECT 查询不同。

在某些情况下，您可能需要考虑 user 表的排序顺序。

让MySQL告诉您帐户的访问权限

试试这个：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

mysql> show create user 'userauth'@'%';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for userauth@%                                                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'userauth'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*E42080C77C398C9F75A841BF6A26CDFE9977BD95' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for userauth@'%';
+--------------------------------------------------------------------------+
| Grants for userauth@%                                                    |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'userauth'@'%'                                     |
| GRANT ALL PRIVILEGES ON `userauth`.* TO 'userauth'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

让MySQL显示给定用户ID的配置相对容易。这意味着，您可以尝试不同的用户ID字符串，查看 MySQL 的响应。

##引号一定是必须的吗？

来自 MySQL 官方文档：

如果用户名和主机名作为未加引号的标识符合法，则不需要用引号引起来。必须用引号指定包含特殊字符的用户名字符串。

引号并不总是必须的。

使用 Docker 测试本地和远程连接

使用 Docker 来同时模拟本地连接 MySQL 和远程访问是非常容易的。mysql/mysql-server 容器包含一个 mysql 程序，我们可以用作该目的。

1
2

$ docker run -it --rm --network authnet mysql/mysql-server  mysql -u userauth -h db-userauth -p
Enter password:

这基于 mysql/mysql-server 镜像创建一个容器。容器在前端执行（-it 选项）并且当容器结束（--rm）将被删除

因此，该容器对于数据库容器显然是 remote 的。数据库的主机名在 -h db-userauth 参数指定 。在这个示例中，我们使用 -network ，将其附加到网络 network，此属性是可选的，它取决于数据库容器的配置方式。

1
2

$ docker exec -it db-userauth mysql -u root -p
Enter password:

这是另一种方式，在数据库容器中执行 mysql。这将是一个本地连接 MySQL，而之前的例子是远程连接。

你可能有个用来诊断尝试连接的服务容器。你必须首先确保 mysql 客户端程序已经安装在容器中。

1
2

$ docker exec -it svc-userauth mysql -u userauth -h db-userauth -p
Enter password:

这将是一个现有的容器，它正在运行通常连接到 db-userauth 数据库的软件。因此，您可以在该容器中运行 mysql，以诊断该容器是否可以访问数据库容器。

有时候这是最简单最愚蠢的错误

在学习了以上所有知识之后，我把问题缩小到这个问题：

2020-02-18T04:03:18.988Z users:model-users Sequelize params {
  dbname: 'userauth',
  username: 'userauth',
  password: 'userautn',
  params: { host: 'db-userauth', port: 3306, dialect: 'mysql' }
}

仔细看下密码，看到密码的问题了吗？

常用链接