Mysql MariaDB 无法使用远程连接

Question

摸索了一天，大概看了那么多文章，关于如何让我的MariaDB监听远程连接。很遗憾出现以下错误。

Host 'xxx.xxx.xxx.xxx' is not allowed to connect to this MariaDB server.

我还阅读了这个 StackOverflow question , anotherQuestion并成功创建新用户并使用以下 MySQL 查询授予所有权限。

CREATE USER 'ahsensaeed'@'%' IDENTIFIED BY 'my_password';
GRANT ALL PRIVILEGES ON *.* TO 'ahsensaeed'@'%' WITH GRANT OPTION;

下面是我的 ahsensaeed 用户资助。

MariaDB [mysql]> show grants for 'ahsensaeed'@'%';
+--------------------------------------------------------------------------------------------------------------------------------------+
| Grants for ahsensaeed@%                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'ahsensaeed'@'%' IDENTIFIED BY PASSWORD '*F794ABE2A12665587C6B6D8B61E2F7E987711AFA' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

最后，我只是刷新了权限，然后转到我的 MariaDB 配置文件并进行了编辑。下面是我的 MariaDB conf 文件所在的路径。

/etc/mysql/mariadb.conf.d/50-server.cnf 

下面是我的MariaDB文件 block 。

[mysqld]

#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
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
.....
.....

然后我通过/etc/init.d/mysql restart 重新启动 mysql 服务。我还在我的客户端上为 mysql 打开了 3306 端口。

当上述解决方案不起作用时，我还在/etc/mysql/conf.d/mysql.cnf 文件中添加了 bind-address = 0.0.0.0，但仍然失败并出现错误。

下面显示了我如何从我的服务器请求 MariaDB 数据库。

-> mysql -uahsensaeed -p -h hostIp

然后我得到以下错误。

ERROR 1130 (HY000): Host 'hostIp' is not allowed to connect to this MariaDB server

编辑 添加主机和用户数据。

MariaDB [(none)]> select User,Host from mysql.user;
+------------+-----------+
| User       | Host      |
+------------+-----------+
| ahsensaeed | %         |
| root       | localhost |
+------------+-----------+
2 rows in set (0.00 sec)

如有任何帮助，我们将不胜感激。

Answer 1

这对我有用(我使用 Ubuntu 18.04 作为虚拟机，使用 Windows 10 和 Vagrant):

sudo mysql -e "CREATE DATABASE {db_name};"
sudo mysql -e "CREATE USER {user_name}@localhost IDENTIFIED BY '{password}';"
sudo mysql -e "UPDATE mysql.user SET Host='%' WHERE User='{user_name}';"
sudo mysql -e "GRANT ALL PRIVILEGES ON {db_name}.* TO '{user_name}'@'%';"
sudo mysql -e "FLUSH PRIVILEGES;"
sudo systemctl restart apache2
sudo systemctl restart mariadb

之后，您应该编辑文件:

/etc/mysql/mariadb.conf.d/50-server.cnf

并将 bind-address 属性设置为您的主机 IP 地址或仅注释掉这一行。

最后，运行命令:

sudo systemctl restart apache2
sudo systemctl restart mariadb

希望这能满足你的需求