为本地和远程访问配置MySQL

Question

我使用的是MySQL服务器版本：10.1.23-MariaDB-9+deb9u1 Raspbian 9.0，运行在树莓派上。

这是我的/etc/mysql/my.cnf文件：

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

#bind-address = 0.0.0.0

我尝试过这个：

我试过这个：

sudo mysql_secure_installation
Change root password: y
Password
Retyped password
Remove anonymous users: y
Disallow root login remotely: n
Remove test database: y
Reload priviledges: y

CREATE USER 'root'@'%.%.%.%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%.%.%.%' WITH GRANT OPTION;
FLUSH PRIVILEDGES;
service mysql restart

在my.cnf文件中，当bind-address被注释时，我可以从localhost访问，但无法通过SQL Workbench从远程主机访问。当bind-address取消注释时，我无法从localhost访问，但我可以通过SQL Workbench从远程主机访问，例如：

mysql -u root
mysql: unknown variable 'bind-address=0.0.0.0'

这是我的用户表格：

MariaDB [(none)]> select user, host, password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *054D119DEAD56E226D8356557796BFA72E71BA40 |
| root | %.%.%.%   | *054D119DEAD56E226D8356557796BFA72E71BA40 |
| root | %         | *054D119DEAD56E226D8356557796BFA72E71BA40 |
+------+-----------+-------------------------------------------+

我该如何配置服务器以允许来自任何IP的本地和远程root访问？

- Cristian M

---

在测试访问权限之前，您是否需要重新启动MariaDB或使用FLUSH PRIVILEGES读取新配置？ - Bernd Buffen

@BerndBuffen 是的。 - Cristian M

在你的情况下，在 [mysqld] 下添加一行 bind-address = 0.0.0.0。这样做可以让服务器和本地客户端都读取绑定地址。 - Bernd Buffen

Answer 1

在 [mysqld] 下方添加一行 bind-address = 0.0.0.0：

[mysqld]
bind-address = 0.0.0.0

在您的情况下，服务器和本地客户端都会读取绑定地址，客户端希望连接到IP 0.0.0.0

- Bernd Buffen

Answer 2

根据这个链接，似乎mysql客户端无法识别绑定地址。

为了本地连接，我必须使用这行代码：

mysql --no-defaults -u[username] -p[password] [database]

- Cristian M

Answer 3

以防万一 - 我先将绑定地址放在行之间，但是后来它没有起作用

systemctl restart mysqld

在末尾加上 bind-address 后它就可以工作了：

# The MariaDB configuration file 
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

[mysqld]
bind-address = 0.0.0.0

- Guido