如何允许远程访问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编辑它)。
然后重启MySQL服务，这样对mysqld.cnf的修改才能够生效:

sudo systemctl restart mysql

如果你有一个已经存在的MySQL用户帐户，你打算用它从远程主机连接数据库，你需要重新配置该帐户以从远程服务器连接，而不是从localhost连接。用你的MySQL root用户或者其他有特权的用户账号打开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地址。

或者，你可以使用以下命令允许从* 任意 * IP地址连接到MySQL数据库:

警告：此命令将允许任何人访问您的MySQL数据库。如果你的数据库中有任何敏感数据，不要运行它。联系:

sudo ufw allow 3306

接下来，尝试从另一台机器远程访问数据库:

注意:如果您添加了一个防火墙规则，只允许来自特定IP地址的连接，则必须尝试访问与该地址相关联的机器的数据库。

mysql -u user -h database_server_ip -p

如果你能够访问数据库，这就确认了配置文件中的bind-address指令出了问题。请注意，将bind-address设置为0.0.0.0是不安全的，因为它允许从任何IP地址连接到你的服务器。另一方面，如果你仍然无法远程访问数据库，那么可能是其他原因导致了问题。