无法通过 MySQL Workbench 或 HeidiSQL 访问 MySQL，但可以通过 shell 访问 [英] Can't access MySQL through MySQL Workbench or HeidiSQL, but can access through shell

问题描述

我不知道这是我这边的用户错误，还是远程服务器配置不正确.

I don't know if this is a user error on my end, or if the remote server is configured incorrectly.

我得到了一些服务器的连接信息，如下所示:

I was given some connection info for a server as follows:

• 服务器IP
• 服务器用户名
• 密钥文件
• mysql 用户
• mysql 密码

我可以使用给定的 mysql 用户名和密码在 shell 中运行mysql"命令，但我无法在 MySQL Workbench 或 HeidiSQL 中设置连接.

and I can shell in and run the "mysql" command with the mysql user and password I was given, but I can't setup a connection in either MySQL Workbench or in HeidiSQL.

在 MySQL Workbench 中，基于 SSH 的标准 TCP/IP"似乎最适合我提供的信息，但是当我尝试连接时，我收到身份验证错误.请检查您的用户名和密码是否正确，然后重试."或打开 SSH 隧道时出现身份验证错误:身份验证错误.请检查您的用户名和密码是否正确，然后重试."在日志文件中.

In MySQL Workbench, the 'Standard TCP/IP over SSH' seemed to best fit the info I was given, but when I try to connect I get "Authentication error. Please check that your username and password are correct and try again." or "Authentication error opening SSH tunnel: Authentication error. Please check that your username and password are correct and try again." in the logfile.

在 HeidiSQL 中，MySQL(SSH 隧道)"似乎最适合我提供的信息，但是当我尝试连接到那里时，我收到在‘读取初始通信数据包’时与 MySQL 服务器的连接丢失，系统错误:0"

In HeidiSQL the "MySQL (SSH tunnel)" seemed to best fit the info I was given, but when I try to connect there, I get "Lost connection to MySQL server at 'reading initial communication packet', system error:0"

我查找了这两个错误，但找不到第一个错误，而第二个错误似乎与服务器配置有关.我对服务器配置不太了解，所以我在评估我是否做错了什么，或者是否需要在服务器上更改某些内容时有些困难.我很感激一些建议.谢谢！

I looked up both these errors and couldn't find anything for the first one, and the second one seemed to be more about server configuration. I'm not too savvy with server config, so I'm a bit stuck evaluating whether or not I'm doing something wrong on my end, or if something needs to be changed on the server. I'd appreciate some advice. Thanks!

推荐答案

在执行远程 mysql 连接之前，您必须检查几个配置步骤:

You have to check a couple configuration steps before you can perform a remote mysql connection:

1. 首先，您必须检查 mysql 默认port(3306) 是否正在侦听正确的接口.您可以在远程服务器中使用 netstat 命令来检查:

1. First of all you must check that mysql default port(3306) is listening on the correct interface. You can check this out using netstat command in the remote server:

# netstat -tulnptcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1111/mysqld

http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html

然后您必须检查是否允许远程主机对 mysql 引擎执行查询(您必须检查用户和主机对您的数据库的权限).

Then you must check that the remote host is allowed to perform querys into mysql engine (you must check user and host permissions over your database).

连接到终端并在 mysql 提示符下运行以下命令:

Connect into terminal and run the following command in mysql prompt:

<代码>mysql>select user,host from mysql.user;

查看以下链接可深入了解 mysql 用户和主机系统:https://dev.mysql.com/doc/refman/5.0/en/adding-users.html

Check the following link to deep insight mysql user and hosts system: https://dev.mysql.com/doc/refman/5.0/en/adding-users.html

检查用户、主机对数据库的权限:

Check user,host permissions over your database:

<代码>mysql>显示对 root@localhost 的授权；

https://dev.mysql.com/doc/refman/5.1/en/grant.html

您必须检查远程主机(客户端)是否存在，例如:user@192.168.1.2

You must check that remote host (client) exist for example: user@192.168.1.2

最后，如果您使用 putty 通过 ssh 加密您的连接，请检查您是否添加了 rsa 密钥指纹，您只需使用 putty 客户端登录 ssh 即可执行此操作.

Finally if you are using putty to encrypt over ssh you connection please check that you rsa key fingerprint is added you can perform this just logging ssh using you putty client.

这篇关于无法通过 MySQL Workbench 或 HeidiSQL 访问 MySQL，但可以通过 shell 访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！