How to Allow Remote Connections to MySQL Database Server

2022-08-17 08:40:51

By default, the MySQL server listens for connections only from localhost, which means it can be accessed only by applications running on the same host.、

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

However, in some situations, it is necessary to access the MySQL server from remote location. For example, when you want to connect to the remote MySQL server from your local system, or when using a multi-server deployment where the application is running on a different machine from the database server. One option would be to access the MySQL server through SSH Tunnel and another is to configure the MySQL server to accept remote connections.

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

In this guide, we will go through the steps necessary to allow remote connections to a MySQL server. The same instructions apply for MariaDB.

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

Configuring MySQL Server

The first step is to set the MySQL server to listen on a specific IP address or all IP addresses on the machine.

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

If the MySQL server and clients can communicate with each other over a private network, then the best option is to set the MySQL server to listen only on the private IP. Otherwise, if you want to connect to the server over a public network set the MySQL server to listen on all IP addresses on the machine.

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

To do so, you need to edit the MySQL configuration file and add or change the value of the bind-address option. You can set a single IP address and IP ranges. If the address is 0.0.0.0, the MySQL server accepts connections on all host IPv4 interfaces. If you have IPv6 configured on your system, then instead of 0.0.0.0, use ::.

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

The location of the MySQL configuration file differs depending on the distribution. In Ubuntu and Debian the file is located at /etc/mysql/mysql.conf.d/mysqld.cnf, while in Red Hat based distributions such as CentOS, the file is located at /etc/my.cnf.

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

Open the file with your text editor:

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

Search for a line that begins with bind-address and set its value to the IP address on which a MySQL server should listen.

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

By default, the value is set to 127.0.0.1 (listens only in localhost).

默认情况下，该值设置为127.0.0.1（仅在localhost中监听）

In this example, we’ll set the MySQL server to listen on all IPv4 interfaces by changing the value to 0.0.0.0

在此示例中，我们将值更改为0.0.0.0，从而将MySQL服务器设置为在所有IPv4接口上进行侦听

mysqld.cnf

bind-address           = 0.0.0.0
# skip-networking

If there is a line containing skip-networking, delete it or comment it out by adding # at the beginning of the line.

如果存在包含跳过skip-networking的行，请删除该行或通过在行的开头添加＃来对其进行注释

In MySQL 8.0 and higher, the bind-address directive may not be present. In this case, add it under the [mysqld] section.

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

Once done, restart the MySQL service for changes to take effect. Only root or users with sudo privileges can restart services.

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

To restart the MySQL service on Debian or Ubuntu, type:

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

sudo systemctl restart mysql

On RedHat based distributions like CentOS to restart the service run:

在基于RedHat的发行版（如CentOS）上，以重新启动服务运行：

sudo systemctl restart mysqld

Granting Access to a User from a Remote Machine

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

The next step is to allow access to the database to the remote user.

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

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

sudo mysql

If you are using the old, native MySQL authentication plugin to log in as root run the command below and enter the password when prompted:

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

mysql -u root -p

From inside the MySQL shell, use the GRANT statement to grant access for the remote user.

在MySQL Shell内，使用GRANT语句为远程用户授予访问权限

GRANT ALL ON database_name.* TO user_name@‘ip_address‘ IDENTIFIED BY ‘user_password‘;

Where:

database_name is the name of the database that the user will connect to.

database_name是用户将连接到的数据库的名称
user_name is the name od the MySQL user.

user_name是MySQL用户的名称
ip_address is the IP address from which the user will connect. Use % to allow the user to connect from any IP address.

ip_address是用户将要连接的IP地址。使用％允许用户从任何IP地址进行连接
user_password is the user password.

user_password是用户密码

For example, to grant access to a database dbname to a user named foo with password my_passwd from a client machine with IP 10.8.0.5, you would run:

例如，要从IP 10.8.0.5的客户机授予密码为my_passwd的名为foo的用户对数据库dbname的访问，应运行：

GRANT ALL ON dbname.* TO foo@‘10.8.0.5‘ IDENTIFIED BY ‘my_passwd‘;

Configuring Firewall

The last step is to configure your firewall to allow traffic on port 3306 (MySQL default port) from the remote machines.

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

Iptables

If you are using iptables as your firewall, the command bellow will allow access from any IP address on the Internet to the MySQL port. This is very insecure.

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

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

Allow access from a specific IP address:

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

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

UFW

UFW is the default firewall tool in Ubuntu. To allow access from any IP address on the Internet (very insecure) run:

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

sudo ufw allow 3306/tcp

Allow access from a specific IP address:

sudo ufw allow from 10.8.0.5 to any port 3306

FirewallD

FirewallD is the default firewall management tool in CentOS. To allow access from any IP address on the Internet (very insecure) type:

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

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

To allow access from a specific IP address on a specific port, you can either create a new FirewallD zone or use a rich rule. Well create a new zone named mysqlzone:

要允许从特定端口上的特定IP地址进行访问，可以创建新的FirewallD区域，使用规则丰富好创建一个名为mysqlzone的新区域：

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

Verifying the Changes

To verify that the remote user can connect to the MySQL server run the following command:

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

mysql -u user_name -h mysql_server_ip -p

Where user_name is the name of the user you granted access to and mysql_server_ip is the IP address of the host where the MySQL server runs.

其中user_name是您授予访问权限的用户的名称，mysql_server_ip是运行MySQL服务器的主机的IP地址

If everything is setup up correctly, you will be able to login to the remote MySQL server.

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

If you get an error like below, then either the port 3306 is not open, or the MySQL server is not listening on the IP address.

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

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

The error below is indicating that the user you are trying to log in doesn’t have permissions to access the remote MySQL server.

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

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

Conclusion

MySQL, the most popular open-source database server by default, listens for incoming connections only on localhost.

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

To allow remote connections to a MySQL server, you need to perform the following steps:

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

Configure the MySQL server to listen on all or a specific interface.

配置MySQL服务器以侦听所有或特定接口
Grant access to the remote user.

向远程用户授予访问权限
Open the MySQL port in your firewall.

在防火墙中打开MySQL端口

Enter the mysql database:

use mysql;

select user, host from user;

Set to allow remote user access:

update user set host = ‘%‘ where user = ‘root‘ and host=‘localhost‘;

Authorize the user root again

GRANT ALL ON *.* TO ‘root‘@‘%‘;
 
flush privileges;
 
select user, host from user;

At this point, using navicat connection or error: Client does not support authentication protocol requested by server;

The reason is that the default encryption method of mysql8 is caching_sha2_password and the encryption method mysql_native_password of mysql5 is different.

Solution - Update user encryption method:

ALTER USER ‘root‘@‘%‘ IDENTIFIED WITH mysql_native_password BY ‘password‘;

Query the results of the modification

select host, user, plugin from user;