如何允许远程访问MySQL

・3 分钟阅读

用户尝试建立远程MySQL数据库时遇到的最常见问题之一是,他们的MySQL实例仅配置为侦听本地连接。要启用此功能,请打开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地址,*::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 8 ,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

更改该行后,保存并关闭该文件,然后重新启动MySQL服务:


sudo systemctl restart mysql

然后,尝试从另一台计算机远程访问你的数据库:


mysql -u user -h database_server_ip -p

请注意,将bind-address设置为0.0.0.0是不安全的,因为它允许从IP地址远程连接到服务器。

讨论
Yangchunyan110 profile image