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

Can't connect to MySQL server on (ip or domain name)

提问by Ambroise Collon

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 [email protected]

我正在尝试配置 Web 服务器（Debian 7）。我跟着这个教程。感谢 gandi.net 服务，我正在租用我的服务器。我现在已经启动并运行了 apache2、mysql、php5。我在终端上使用以下命令连接到它：ssh [email protected]

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 :

下一步是通过 sequel pro 创建我的数据库，但我不知道该怎么做，文档也不是很清楚……这就是我在谈论的内容：

I have tried to connect :

我试图连接：

• Nom : some name, i found clear. (I think i can put anything in here, right ?)
• Host : ip or domain name
• User : the username I'm using in my ssh connection (cf above)
• Password : the password I both use for ssh connection (cf above) and mysql connection

• Nom ：一些名字，我发现很清楚。（我想我可以在这里放任何东西，对吗？）
• 主机：ip或域名
• 用户：我在 ssh 连接中使用的用户名（参见上文）
• 密码：我用于 ssh 连接（参见上文）和 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.

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

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

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

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

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

Any idea how I could do that?

知道我怎么做吗？

回答by Bert Peters

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-networkingis either commented (comments start with a '#') or not there, and
• 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.

• 该行skip-networking被注释（注释以“#”开头）或不存在，并且
• 绑定地址设置为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

回答by Trendfischer

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.1to the IP address of the mysql server, you want to connect or 0.0.0.0for no restriction.

现在改成127.0.0.1mysql服务器的IP地址，你要连接还是0.0.0.0没有限制。

回答by gogasca

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.cnfbased 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-addressto my local IP address.

并将绑定地址更改为我的本地 IP 地址。

回答by Henry Tirla

I fix the issue by simply running this line on terminal

我通过简单地在终端上运行这条线来解决这个问题

ALTER USER 'your_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

your_user = rootfor me
your_password = somethingchoose what ever you want

your_user = root为我
your_password = something选择你想要的

回答by Jitendra

1. locate my.cnf
2. vi < copy the path >
   for e.g. ==> vi /usr/local/etc/my.cnf
3. Now you see and compare below if you find difference then update

1. locate my.cnf
2. vi <复制路径>
   for eg ==>vi /usr/local/etc/my.cnf
3. 现在你看到并在下面比较，如果你发现差异然后更新

# Default Homebrew MySQL server config  
[mysqld]
# Only allow connections from localhost  
bind-address = 0.0.0.0

# Default Homebrew MySQL server config  
[mysqld]
# Only allow connections from localhost  
bind-address = 0.0.0.0

Now press button => esc and :wq (vi commands)

现在按下按钮 => esc 和 :wq（vi 命令）

Restart the MySQL =>

重启 MySQL =>

sudo /usr/local/mysql/support-files/mysql.server stop
sudo /usr/local/mysql/support-files/mysql.server start

Now good to go...

现在好去...

回答by Manu R S

sudo vim /etc/ssh/sshd_config

set localhost in the file httpd.conf as follows

ServerName localhost

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

须藤vim /etc/ssh/sshd_config

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

服务器名称本地主机

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