如何允许远程访问MySQL

许多网站和应用程序从托管在同一台机器上的 Web 服务器和数据库后端开始。但是，随着时间的推移，这样的设置可能会变得繁琐且难以扩展。一个常见的解决方案是通过设置远程数据库来分离这些功能，允许服务器和数据库在自己的机器上以自己的速度增长。

用户在尝试设置远程 MySQL 数据库时遇到的更常见问题之一是他们的 MySQL 实例仅配置为侦听本地连接。这是 MySQL 的默认设置，但它不适用于远程数据库设置，因为 MySQL 必须能够侦听可以访问服务器的外部 IP 地址。要启用此功能，请打开您的 mysqld.cnf 文件：

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

导航到以 bind-address 指令开头的行。它看起来像这样：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
. . .

默认情况下，此值设置为 127.0.0.1，这意味着服务器将仅查找本地连接。您需要更改此指令以引用外部 IP 地址。出于故障排除的目的，您可以将此指令设置为通配符 IP 地址，*、:: 或 0.0.0.0：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0
. . .

注意： 在某些版本的 MySQL 中，bind-address 指令可能默认不在 mysqld.cnf 文件中。在这种情况下，将以下突出显示的行添加到文件底部：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
bind-address            = 0.0.0.0

更改此行后，保存并关闭文件（CTRL + X、Y，如果使用 nano 编辑，则为 ENTER）。

然后重启 MySQL 服务，使您对 mysqld.cnf 所做的更改生效：

sudo systemctl restart mysql

如果您计划使用现有的 MySQL 用户帐户从远程主机连接到数据库，则需要重新配置该帐户以从远程服务器而不是 localhost 连接。为此，请以您的 root MySQL 用户或其他特权用户帐户打开 MySQL 客户端：

sudo mysql

如果您为 root 启用了密码验证，则需要使用以下命令来访问 MySQL shell：

mysql -u root -p

要更改用户的主机，可以使用 MySQL 的 RENAME USER 命令。运行以下命令，确保将 sammy 更改为您的 MySQL 用户帐户的名称，并将 remote_server_ip 更改为您的远程服务器的 IP 地址：

RENAME USER 'sammy'@'localhost' TO 'sammy'@'remote_server_ip';

或者，您可以使用以下命令创建一个仅从远程主机连接的新用户帐户：

CREATE USER 'sammy'@'remote_server_ip' IDENTIFIED BY 'password';

注意：此命令将创建一个使用 MySQL 的默认身份验证插件 caching_sha2_password 进行身份验证的用户。但是，某些 PHP 版本存在一个已知问题，可能会导致此插件出现问题。

如果您计划将此数据库与 PHP 应用程序（例如 phpMyAdmin）一起使用，您可能希望创建一个远程用户，该用户将使用旧的但仍然安全的 mysql_native_password 插件进行身份验证：

CREATE USER 'sammy'@'remote_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

如果您不确定，您始终可以创建一个使用 caching_sha2_plugin 进行身份验证的用户，然后稍后使用以下命令对其进行 ALTER 身份验证：

ALTER USER 'sammy'@'remote_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

然后根据您的特定需求授予新用户适当的权限。以下示例授予用户对 CREATE、ALTER 和 DROP 数据库、表和用户的全局权限，以及对 INSERT 的权限，来自服务器上任何表的 UPDATE 和 DELETE 数据。它还授予用户使用 SELECT 查询数据、使用 REFERENCES 关键字创建外键以及使用 RELOAD 权限执行 FLUSH 操作的能力。但是，您应该只授予用户他们需要的权限，因此可以根据需要随意调整您自己用户的权限。

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'remote_server_ip' WITH GRANT OPTION;

在此之后，最好运行 FLUSH PRIVILEGES 命令。这将释放服务器由于前面的 CREATE USER 和 GRANT 语句而缓存的所有内存：

FLUSH PRIVILEGES;

然后就可以退出 MySQL 客户端了：

exit

最后，假设您在数据库服务器上配置了防火墙，您还需要打开端口 3306 — MySQL 的默认端口 — 以允许到 MySQL 的流量。

如果您只计划从一台特定的机器访问数据库服务器，您可以使用以下命令授予该机器远程连接到数据库的独占权限。确保将 remote_IP_address 替换为您计划连接的机器的实际 IP 地址：

sudo ufw allow from remote_IP_address to any port 3306

如果您将来需要从其他机器访问数据库，您可以使用此命令临时授予他们访问权限。请记住包括他们各自的 IP 地址。

或者，您可以使用以下命令允许从 any IP 地址连接到 MySQL 数据库：

Warning：此命令将允许任何人访问您的 MySQL 数据库。 如果您的数据库包含任何敏感数据，请不要运行它。

sudo ufw allow 3306

在此之后，尝试从另一台机器远程访问您的数据库：

注意：如果您添加了防火墙规则以仅允许来自特定 IP 地址的连接，您必须尝试使用与该地址关联的计算机访问数据库。

mysql -u user -h database_server_ip -p

如果您能够访问您的数据库，则它确认您的配置文件中的 bind-address 指令是问题所在。但请注意，将 bind-address 设置为 0.0.0.0 是不安全的，因为它允许从任何 IP 地址连接到您的服务器。另一方面，如果您仍然无法远程访问数据库，则可能是其他原因导致了问题。在任何一种情况下，您都可能会发现按照我们关于如何设置远程数据库以在 Ubuntu 18.04 上使用 MySQL 优化站点性能的指南来设置更安全的远程数据库配置很有帮助。