如何设置 MySql 授权以允许外部连接到 MySql? [英] How to set MySql grant to allow external connection to MySql?

问题描述

我不太喜欢 DB，我有以下问题.

I am not so into DB and I have the following problem.

我在远程 Ubuntu 16.04 服务器上安装了 MySql 5.7.17，我必须从笔记本电脑上安装的客户端连接到该服务器.

I have installed a MySql 5.7.17 on a remote Ubuntu 16.04 server and I have to connect to this server from a client installed on my laptop.

所以我做的第一件事是我改变了 bind-address 指令值(到 /etc/mysql/mysql.conf.d/mysqld.cnf 文件)来自:

So The first thing that I have done is that I have changed the bind-address directive values (into the /etc/mysql/mysql.conf.d/mysqld.cnf file) from:

Bind-address=127.0.0.1

到:

bind-address = 0.0.0.0

也允许外部连接.

问题是，当我尝试从我的客户端(安装在我的笔记本电脑上)连接时，我收到此错误:

The problem is that when I try to connect from my client (installed on my laptop) I obtain this error:

Host 'XXX.YYY.ZZ.JJ' is not allowed to connect to this MySQL server

(其中 **XXX.YYY.ZZ.JJ* 是我的笔记本电脑 IP 地址).

(where **XXX.YYY.ZZ.JJ* is my laptop IP address).

据我所知，我还必须向 mysql 用户授予从任何主机进行连接的 db 权限，而不仅仅是从本地主机连接.

From what I have understood I also have to give to the mysql user w the db permissions to connect from any host and not just from localhost.

我认为它应该与授权有关:https://dev.mysql.com/doc/refman/5.5/en/grant.html

I think that it should be something related to the grant: https://dev.mysql.com/doc/refman/5.5/en/grant.html

我到底要做什么?我认为我必须从我的服务器 shell(通过 SSH)访问 MySql，然后执行一个查询，授予 root 用户(MySql 用户)一些权限以允许从外部访问.

What exactly have I to do? I think that I have to access to MySql from my server shell (via SSH) and then perform a query that grant some privileges to the root user (the MySql user) to allow to access from outside.

但我不知道我到底要做什么.如何允许 roo 用户进行此外部连接?

But I don't know what exactly I have to do. How can I allow this external connection for the roo user?

推荐答案

如您所说，您需要以 root 访问权限登录 mysql 服务器，然后在 mysql 提示符下运行以下命令

As you mentioned, you need to login to mysql server with root access and then run following command on mysql prompt

GRANT ALL PRIVILEGES ON your_db_name.* TO 'your_db_user_name'@'%' IDENTIFIED BY 'newpassword';

你也可以指定一个特定的 IP 而不是 %

you can also specify a specific IP instead of %

GRANT ALL PRIVILEGES ON your_db_name.* TO 'your_db_user_name'@'your_IP_here' IDENTIFIED BY 'newpassword';

您也可以为所有数据库指定一个

you can also specify a for all databases

GRANT ALL PRIVILEGES ON *.* TO 'your_db_user_name'@'your_IP_here' IDENTIFIED BY 'newpassword';

这篇关于如何设置 MySql 授权以允许外部连接到 MySql?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！