如何设置远程连接 MySQL 数据库服务器

默认情况下, MySQL 服务器仅侦听来自本地主机的连接,这意味着它只能由在同一主机上运行的应用程序访问。

但是,在某些情况下,有必要从远程位置访问 MySQL 服务器。例如,当您想从本地系统连接到远程 MySQL 服务器时,或者当使用多服务器部署时,应用程序与数据库服务器不在同一台计算机上运行。一种选择是通过 SSH 隧道访问 MySQL 服务器,另一种选择是将 MySQL 服务器配置为接受远程连接。

在本指南中,我们将介绍必要的步骤,以允许与 MySQL 服务器的远程连接。相同的说明适用于 MariaDB 。

配置 MySQL 服务器

第一步是将 MySQL 服务器设置为侦听计算机上的特定 IP 地址或所有 IP 地址。

如果 MySQL 服务器和客户端可以通过专用网络相互通信,那么最好的选择是将 MySQL 服务器设置为仅在专用 IP 上侦听。否则,如果要通过公用网络连接到服务器,请将 MySQL 服务器设置为侦听计算机上的所有 IP 地址。

为此,您需要编辑 MySQL 配置文件并添加或更改 bind-address 选项的值。您可以设置一个 IP 地址和 IP 范围。如果地址为 0.0.0.0 ,则 MySQL 服务器将在所有主机 IPv4 接口上接受连接。如果您在系统上配置了 IPv6 ,请使用 ::代替 0.0.0.0

MySQL 配置文件的位置因发行版本而异。在 Ubuntu 和 Debian 中,文件位于 /etc/mysql/mysql.conf.d/mysqld.cnf ,而在基于 Red Hat 的发行版(例如 CentOS) 中,文件位于 /etc/my.cnf

使用文本编辑器打开文件:

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

搜索以bind-address 开头的行, 并将其值设置为 MySQL 服务器应侦听的 IP 地址。

默认情况下,该值设置为 127.0.0.1( 仅在 localhost 中监听)。

在此示例中,我们将值设置为 0.0.0.0,将 MySQL 服务器设置为侦听所有 IPv4 接口。 

mysqld.cnf

bind-address           = 0.0.0.0
# skip-networking

如果有包含 skip-networking 的行,请将其删除或通过 # 在行的开头添加注释来对其进行注释。

在 MySQL 8.0 和更高版本中,该 bind-address 指令可能不存在。在这种情况下,请将其添加到该 [mysqld] 部分下。

完成后,重新启动 MySQL 服务以使更改生效。只有 root 或具有 sudo 特权的用户才能重新启动服务。

要在 Debian 或 Ubuntu 上重新启动 MySQL 服务,请输入:

sudo systemctl restart mysql

在基于 RedHat 的发行版(如 CentOS) 上重新启动服务,请运行:

sudo systemctl restart mysqld

向远程计算机授予用户访问权限

下一步是允许远程用户访问数据库。

通过键入以下内容以 root 用户身份登录到 MySQL 服务器:

sudo mysql

如果您使用旧的本地 MySQL 身份验证插件以 root 用户身份登录,请运行以下命令并在出现提示时输入密码:

mysql -uroot -p

在 MySQL Shell 中,使用该 GRANT 语句为远程用户授予访问权限

GRANT ALL ON database_name.* TO [email protected]'ip_address' IDENTIFIED BY 'user_password';

说明:

  • database_name 是用户将连接到的数据库的名称。
  • user_name 是 MySQL 用户的名称。
  • ip_address 是用户所在的 IP 地址。使用 % 允许用户从任何 IP 地址进行连接。
  • user_password 是用户密码。

例如,要将访问数据库 dbname 的权限授予具有 IP 为 10.8.0.5 的客户端计算机上的名称为 foo 密码为 my_passwd 的用户,应运行:

GRANT ALL ON dbname.* TO [email protected]'10.8.0.5' IDENTIFIED BY 'my_passwd';

配置防火墙

最后一步是配置防火墙,以允许 3306 来自远程计算机的端口 (MySQL 默认端口)上的流量。

iptables

如果将 iptables 用作防火墙,则下面的命令将允许从 Internet 上的任何 IP 地址访问 MySQL 端口。这是非常不安全的。

sudo iptables -A INPUT -p tcp --destination-port 3306 -j ACCEPT

允许从特定 IP 地址进行访问:

sudo iptables -A INPUT -s 10.8.0.5 -p tcp --destination-port 3306 -j ACCEPT

UFW

UFW 是 Ubuntu 中的默认防火墙工具。要允许从 Internet 上的任何 IP 地址访问(非常不安全),请运行:

sudo ufw allow 3306/tcp

允许从特定 IP 地址进行访问:

sudo ufw allow from 10.8.0.5 to any port 3306

FirewallD

FirewallD 是 CentOS 中的默认防火墙管理工具。要允许从 Internet 上的任何 IP 地址访问(非常不安全),请输入:

sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp
sudo firewall-cmd --reload

要允许从特定端口上的特定 IP 地址进行访问,可以创建新的 FirewallD 区域,也可以使用丰富规则。下面创建创建一个新区域 mysqlzone

sudo firewall-cmd --new-zone=mysqlzone --permanent
sudo firewall-cmd --reload
sudo firewall-cmd --permanent --zone=mysqlzone --add-source=10.8.0.5/32
sudo firewall-cmd --permanent --zone=mysqlzone --add-port=3306/tcp
sudo firewall-cmd --reload

验证更改

要验证远程用户可以连接到 MySQL 服务器,请运行以下命令:

mysql -u user_name -h mysql_server_ip -p

其中 user_name ,您被授予访问权限的用户名, mysql_server_ip 是 MySQL 服务器运行所在主机的 IP 地址。

如果一切设置正确,您将能够登录到远程 MySQL 服务器。

如果出现以下错误,则说明端口 3306 未打开,或者 MySQL 服务器未在 IP 地址上进行侦听

ERROR 2003 (HY000): Can't connect to MySQL server on '10.8.0.5' (111)"

下面的错误表明您尝试登录的用户没有访问远程 MySQL 服务器的权限。

"ERROR 1130 (HY000): Host ‘10.8.0.5’ is not allowed to connect to this MySQL server" 

结论

MySQL 是最受欢迎的开源数据库服务器,默认情况下它仅在 localhost 上侦听传入的连接。

要允许到 MySQL 服务器的远程连接,您需要执行以下步骤:

  1. 配置 MySQL 服务器以侦听所有或特定接口。
  2. 向远程用户授予访问权限。
  3. 在防火墙中打开 MySQL 端口。