允许远程访问 MySQL、Ubuntu

2021-12-26 00:00:00 ubuntu mysql netbeans

我试图在我的 Ubuntu 服务器 (VPS) 上允许远程访问 MySQL.

Im trying to allow remote accecss to MySQL on my Ubuntu server (VPS).

我试图通过我在 Netbeans 中构建的 Java 应用程序访问数据库.尝试连接时，Netbeans 踢出以下错误消息:

Im trying to access the database through an Java application Im building in Netbeans. Netbeans kick the following error message, when trying to connect:

无法建立到jdbc:mysql://xx.xxx.xxx.xxx:3306/xxxxxx?zeroDateTimeBehavior=convertToNull使用 com.mysql.jdbc.Driver(空，来自服务器的消息:主机'xx.xxx.xxx.xxx' 不允许连接到这个 MySQL 服务器")

Cannot establish a connection to jdbc:mysql://xx.xxx.xxx.xxx:3306/xxxxxx?zeroDateTimeBehavior=convertToNull using com.mysql.jdbc.Driver (null, message from server: "Host 'xx.xxx.xxx.xxx' is not allowed to connect to this MySQL server")

在尝试通过 Netbeans 连接到我的 MySQL 服务器时，我遵循了本指南:https://www.youtube.com/watch?v=Fk2EkBs-Oq4

I have followed this guide when trying to connect to my MySQL server through Netbeans: https://www.youtube.com/watch?v=Fk2EkBs-Oq4

MySQL

MySQL 在标准端口 3306 上运行.在/etc/mysql/my.cnf 中，我注释掉了以下行:

MySQL running on standard port 3306. In /etc/mysql/my.cnf I have comment out the following row:

绑定地址 = 127.0.0.1

bind-address = 127.0.0.1

所以我的 my.cnf 文件中不存在绑定地址.

So no bind-address exist in my my.cnf -file.

UFW

我的服务器上安装了 UFW.ufw 状态编号如下所示:

I have UFW installed on my server. ufw status numbered looks like this:

     To                         Action      From
     --                         ------      ----
[ 1] 22                         ALLOW IN    Anywhere
[ 2] 80/tcp                     ALLOW IN    Anywhere
[ 3] Anywhere                   ALLOW IN    MY-IP-ADDRESS*
[ 4] 3306/tcp                   ALLOW IN    Anywhere
[ 5] 3306/tcp                   ALLOW IN    MY-IP-ADDRESS*
[ 6] 22 (v6)                    ALLOW IN    Anywhere (v6)
[ 7] 80/tcp (v6)                ALLOW IN    Anywhere (v6)
[ 8] 3306/tcp (v6)              ALLOW IN    Anywhere (v6)

*MY-IP-ADDRESS = 我出"到互联网的 IP 地址，我从以下地址获得:http://whatismyipaddress.com/

*MY-IP-ADDRESS = The ip address I have "out" to the internet, which I recive from: http://whatismyipaddress.com/

我做错了什么，为什么无法连接?

What am I doing wrong and why cant I connect?

编辑:问题是，如果我在 my.cnf 中绑定，我只能绑定一个 IP.我需要对在 vps 上运行的实时网站进行 localhost 访问，还需要从我的开发计算机(此线程的目标)进行访问.我的想法是删除 my.cnf 中的绑定以允许所有，然后通过我的防火墙 UFW 授予对本地主机和我的开发计算机的访问权限.

EDIT: The thing is that if i bind in my.cnf I can only bind one IP. I need localhost access for my live websites running on the vps and also access from my dev-computer (which this thread is aiming). My thoughts was to remove bind in my.cnf to allow all and then grant access through my firewall UFW to localhost and my dev-computer.

推荐答案

如果你想使用通过PuTTY和隧道连接
看看这个答案的底部

if you want to use Connect via PuTTY and tunnel
look at the bottom of this answer

我假设你有一个 mysql 管理工具.
以下内容将取决于使用的工具 linux 或其他东西，但工作方式相同.

I assume you have a mysql administration tool.
The following will look depending on the tool used linux or something else, but work the same.

在那里登录并进入用户管理.

Login there and go into the user administration.

如果你安装了一个普通的Mysql，那么应该只有root，没有host.

If you have installation a normal Mysql, then there should be only root without a host.

从创建本地主机的那一刻起，Mysql 就假设您要管理多个主机.

From the moment when you have created a localhost, then Mysql assume that you want to manage multiple hosts.

创建一个现有的主机.您的网络中存在计算机名称.这里root@dxxxxx-p.这应该在 root 用户下创建.

Create a existing host. A computer name exists in your network. Here root@dxxxxx-p. This should be created under the root user.

这还不是全部，现在您仍然拥有所有表授予权限.
这里 pricelist 没有分配权限

That's not all now you still have all your tables grant permissions.
Here pricelist has no assigned permissions

但是示例拥有所有权限

以多主机管理为例.

具有两个可访问名称的计算机

A computer with two accessible names

  • myComp1:IP 192.168.0.101
  • 本地主机:IP 127.0.0.1

如果您现在使用mysql -h localhost -u root ...."在同一台计算机上连接，您将获得分配给 localhost 的权限.

If you now connect on the same computer with "mysql -h localhost -u root ...." you get the permissions you have assigned to localhost.

您可能会认为因为 localhost 和 myComp1 是同一台计算机.
现在自动 myComp1 具有与 localhost 相同的权限.
但事实并非如此.所以要小心.

You might think because localhost and myComp1 is the same computer.
Now automatically myComp1 has the same permission as localhost .
But this is not so. So be careful.

当您使用 PuTTY 连接您时，上述所有内容都不是必需的.

when you connect you with PuTTY, everything described above is not necessary.

使用隧道，您可以在 ubuntu 服务器上以 root@localhost 身份连接.

With tunnel you connect as root@localhost on the ubuntu server.

Localhost 在这里有点误导，因为它与您的 Windows 计算机无关，而是与 ubuntu 服务器上的 localhost 相关.

Localhost is here somewhat misleading because it does not relate to your windows computer but on the localhost on the ubuntu server.

通过 SSH 远程访问您的 MySQL 服务器

Access Your MySQL Server Remotely Over SSH

因此，您的网络服务器上安装了 MySQL，但出于安全原因，默认情况下它仅对本地端口开放.

如果您想从 MySQL 查询浏览器 或 Netbeans 等客户端工具访问您的数据库，通常您必须从您的本地 IP 地址打开访问……但这几乎没有那么安全.

If you want to access your database from a client tool like the MySQL Query Browser or Netbeans , normally you’d have to open up access from your local IP address… but that’s not nearly as secure.

因此，我们将仅通过 SSH 隧道使用端口转发，因此您的 MySQL 客户端认为它正在连接到您的本地主机，但它实际上是通过隧道连接到另一台服务器.

So instead, we’ll just use port-forwarding through an SSH tunnel, so your MySQL client thinks it’s connecting to your localhost machine, but it’s really connecting to the other server through the tunnel.

转到 SSH->隧道

点击后保存

确保 Windows 计算机上的 MySQL 服务器已关闭.
我正在使用 MySQL System Tray Monitor.

Make sure that the MySQL server are off on the Windows computer.
I'm using MySQL System Tray Monitor.

右键单击，我会看到所有选项.

With a right click, I see all the options.

点击打开

如果您已经在 ubuntu 上完成了 SSH 的所有设置，那么它应该会出现在这里.(如果没有在网上搜索 Ubuntu SSH 和 Putty)

If you have done all the settings for SSH on ubuntu right, that should appear here. (If NOT Search for Ubuntu SSH and Putty on the web)

不要忘记:既然您已经登录了 ubuntu 服务器，您就拥有服务器本身的 root@localhost 权限，因为 root@localhost 拥有所有权限，您无需创建主机并创建架构权限

Do not forget: Now that you've logged on ubuntu server you have all the right as root@localhost on the server itself, since root@localhost has all rights, you need not create a host and create permissions to schemas

现在在您的 Windows 计算机上打开 MySql 查询浏览器

Now on your windows computer open MySql Query Browser

现在连接到本地主机(记住这个本地主机在ubuntu上是指本地主机)
您可以在 Netbeans 中使用相同的设置在 Ubuntu 上连接到 Mysql

Now connect to localhost (remember this localhost means localhost on ubuntu)
You can use in Netbeans the same settings to connect to Mysql on Ubuntu

Mysql 查询浏览器打开，您可以在 UBUNTU 上处理您的数据库

Mysql Query Browser opens you can work on your Databases on UBUNTU

关闭后新建连接向导
与 Mysql Ubuntu 上的所有数据库创建新连接.

After closing New Connection Wizard
A new connection is created with all Databases On Mysql Ubuntu.

相关文章