无法连接到MySQL服务器（ip或域名）

I'm trying to configure a web server (Debian 7). I followed this tutorial. I'm renting my server thanks to gandi.net service. And i have now apache2, mysql, php5 up and running. I connect to it using following command on terminal : ssh username@domainname.com

我正在尝试配置Web服务器（Debian 7）。我按照本教程。我正在租用我的服务器，感谢gandi.net服务。我现在已经启动了apache2，mysql，php5。我使用终端上的以下命令连接到它：ssh username@domainname.com

Next step is to create my database through sequel pro and I don't know how to do it and documentation is not so clear... Here is what I'm talking about : 无法连接到MySQL服务器（ip或域名）

下一步是通过续集专业版创建我的数据库，我不知道该怎么做，文档不是那么清楚...这就是我在说的：

I have tried to connect :

我试图连接：

Nom : some name, i found clear. (I think i can put anything in here, right ?)
Nom：有些名字，我发现很清楚。（我想我可以放任何东西，对吧？）
Host : ip or domain name
主持人：ip或域名
User : the username I'm using in my ssh connection (cf above)
用户：我在ssh连接中使用的用户名（参见上文）
Password : the password I both use for ssh connection (cf above) and mysql connection
密码：我用于ssh连接（上面的cf）和mysql连接的密码

For the rest, I left like it was and I received this error message :

其余的，我离开了，我收到了这条错误消息：

Unable to connect to host domainname.com, or the request timed out.

无法连接到主机domainname.com，或请求超时。

Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

确保地址正确并且您具有必要的权限，或尝试增加连接超时（当前为10秒）。

MySQL said: Can't connect to MySQL server on 'domainename.com' (61)' (2)

MySQL说：无法连接到'domainename.com'上的MySQL服务器（61）'（2）

Any idea how I could do that?

知道我怎么能这样做吗？

4 个解决方案

#1

If you do a normal install of MySQL on Debian, it will be configured to block external connections to the database.

如果你在Debian上正常安装MySQL，它将被配置为阻止与数据库的外部连接。

This means that you still need to tell MySQL that external access is OK. To do this, you need to update the bind address for MySQL. This is configured in my.cnf, which, on Debian based systems, is located in /etc/mysql/my.cnf.

这意味着您仍需要告诉MySQL外部访问是否正常。为此，您需要更新MySQL的绑定地址。这是在my.cnf中配置的，在基于Debian的系统上，它位于/etc/mysql/my.cnf中。

In there, find the section that says

在那里，找到说的部分

[mysqld]

In there, you must make sure that

在那里，你必须确保

the line skip-networking is either commented (comments start with a '#') or not there, and
行skip-networking要么注释（注释以'＃'开头），要么没有注释，以及
Bind-address is set to either 0.0.0.0 (which it is if there is no line bind-address) or to your server's IP-address.
绑定地址设置为0.0.0.0（如果没有行绑定地址）或者服务器的IP地址。

After doing this, you should restart your MySQL service. Then you need to create a user that is allowed remote access. This can be done with a SQL query:

执行此操作后，您应该重新启动MySQL服务。然后，您需要创建一个允许远程访问的用户。这可以通过SQL查询完成：

GRANT ALL ON yourdatabase.* TO youruser@'*' IDENTIFIED BY 'yourpassword';

You can switch out the asterisk for the IP-address you will connect from, if it's the same every time.

如果每次都相同，您可以为要连接的IP地址切换星号。

Finally , you need to open port 3306 (the port MySQL uses) on your firewall. This usually isn't neccesary as it is already open on most systems, but it can be done using the following iptables command.

最后，您需要在防火墙上打开端口3306（MySQL使用的端口）。这通常不是必需的，因为它已在大多数系统上打开，但可以使用以下iptables命令完成。

/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
service iptables save

Source: 1

资料来源：1

#2

This is the solution that worked for me: In Debian 7 look in the my.cnf under /etc/mysql/my.cnf and find the following lines:

这个解决方案对我有用：在Debian 7中查看/etc/mysql/my.cnf下的my.cnf并找到以下行：

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1

Now change the 127.0.0.1 to the IP address of the mysql server, you want to connect or 0.0.0.0 for no restriction.

现在将127.0.0.1更改为mysql服务器的IP地址，要连接或0.0.0.0为无限制。

#3

I was trying to find .cnf file hence I did the following:

我试图找到.cnf文件，因此我做了以下事情：

sudo find / -name "*.cnf"

/etc/mysql/mysql.conf.d/mysqld.cnf
/etc/mysql/my.cnf
/etc/mysql/mysql.cnf
/etc/mysql/conf.d/mysqldump.cnf
/etc/mysql/conf.d/mysql.cnf

I edited /etc/mysql/mysql.conf.d/mysqld.cnf based on

我根据/etc/mysql/mysql.conf.d/mysqld.cnf编辑了

strace mysql ";" 2>&1  | grep cnf

stat("/etc/my.cnf", 0x7ffda9472660)     = -1 ENOENT (No such file or directory)
stat("/etc/mysql/my.cnf", {st_mode=S_IFREG|0644, st_size=683, ...}) = 0
open("/etc/mysql/my.cnf", O_RDONLY)     = 3
stat("/etc/mysql/conf.d/mysql.cnf", {st_mode=S_IFREG|0644, st_size=8, ...}) = 0
open("/etc/mysql/conf.d/mysql.cnf", O_RDONLY) = 4
stat("/etc/mysql/conf.d/mysqldump.cnf", {st_mode=S_IFREG|0644, st_size=55, ...}) = 0
open("/etc/mysql/conf.d/mysqldump.cnf", O_RDONLY) = 4
stat("/etc/mysql/mysql.conf.d/mysqld.cnf", {st_mode=S_IFREG|0644, st_size=3034, ...}) = 0
open("/etc/mysql/mysql.conf.d/mysqld.cnf", O_RDONLY) = 4
stat("/etc/mysql/mysql.conf.d/mysqld_safe_syslog.cnf", {st_mode=S_IFREG|0644, st_size=21, ...}) = 0
open("/etc/mysql/mysql.conf.d/mysqld_safe_syslog.cnf", O_RDONLY) = 4
stat("/root/.my.cnf", 0x7ffda9472660)   = -1 ENOENT (No such file or directory)
stat("/root/.mylogin.cnf", 0x7ffda9472660) = -1 ENOENT (No such file or directory)

And changed bind-address to my local IP address.

并将bind-address更改为我的本地IP地址。

#4

sudo vim /etc/ssh/sshd_config

sudo vim / etc / ssh / sshd_config

set localhost in the file httpd.conf as follows

在httpd.conf文件中设置localhost，如下所示

ServerName localhost

ServerName localhost

or else the port won't accept your server request, and will show cant connect to server ip_address

否则端口将不接受您的服务器请求，并且将显示无法连接到服务器ip_address

#1