MySQL 数据库接受远程连接，但不接受来自 PHP 的连接 [英] MySQL Database accepting remote connections, but not from PHP

问题描述

所以我在专用服务器上设置了一个 MySQL 服务器.我试图用这个 PHP 脚本连接到它.

So I setup a MySQL server on a dedicated server. I attempted to connect to it with this PHP script.

<?php
$servername = "******";
$username = "******";
$password = "******";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";
?>

是的，我脚本中的 ***** 已替换为正确的信息.

Yes the ***** in my script are replaced with the proper information.

连接因此错误而被拒绝

Connection failed: Can't connect to MySQL server on '66.45.230.186' (111)

所以我从另一台主机上尝试过，我使用了 http://www.rainbowspuppiessunshine.com/tools/dbtest/index.php

So I tried it from another host, I used http://www.rainbowspuppiessunshine.com/tools/dbtest/index.php

从这里我也得到了一个错误:

From here I also got an error :

Lost connection to MySQL server at 'reading initial communication packet', system error: 111

所以我尝试从我的 VPS 连接到 MySQL.我在上面安装了 MySQL 并通过用户root"和steam"建立了连接，并且能够访问我的数据库并通过它添加表.

So I tried to connect to the MySQL from my VPS. I installed MySQL on it and made a connection through users "root" and "steam" and was able to access my database and add tables through that.

VPS 与我的专用服务器(运行我的 MySQL 服务器)由同一台主机托管，所以我想也许是因为它在同一个 LAN 上，这可能就是它允许连接的原因.所以我决定在我的本地机器上安装一个 MySQL 客户端(HeidiSQL 9.1).我能够从我的本地机器(距我的专用服务器 500 英里)与该客户端建立连接

The VPS is hosted by the same host as my Dedicated server (running my MySQL server) so I figured perhaps since it was on the same LAN that might be why it was allowing a connection. So I decided to install a MySQL client on my local machine (HeidiSQL 9.1). I was able to make a connection with that client from my local machine (500 miles away from my Dedicated Server)

所以我完全不知道为什么我能够从另一个 MySQL 客户端远程连接到 MySQL，但我无法通过任何 PHP 脚本连接到它.我真的不知道有多大区别.

So I am absolutely clueless as to why I am able to connect to the MySQL remotely from another MySQL client, but I am unable to connect to it through any PHP script. I really didn't know there was much difference.

MySQL 服务器是全新安装的(除了设置远程连接权限和添加具有两个表的数据库).它在 Ubuntu 14.04 上运行

The MySQL server is a fresh install (other than setting up permissions for remote connections and adding a database with two tables). It's running on Ubuntu 14.04

+------------------+-------------------------------------------+---------------+
| user             | password                                  | host          |
+------------------+-------------------------------------------+---------------+
| root             | *367201834FD28DD7137E8947B4E1A1F5CAC4BBB4 | localhost     |
| root             | *367201834FD28DD7137E8947B4E1A1F5CAC4BBB4 | myserver      |
| root             | *367201834FD28DD7137E8947B4E1A1F5CAC4BBB4 | 127.0.0.1     |
| root             | *367201834FD28DD7137E8947B4E1A1F5CAC4BBB4 | ::1           |
| debian-sys-maint | *AA9A40B349EDF9989A118FEFCF255353033F4A6D | localhost     |
| steam            | *367201834FD28DD7137E8947B4E1A1F5CAC4BBB4 | **.**.***.*** |
| steam            | *367201834FD28DD7137E8947B4E1A1F5CAC4BBB4 | %             |
| root             | *367201834FD28DD7137E8947B4E1A1F5CAC4BBB4 | %             |
+------------------+-------------------------------------------+---------------+

... 被编辑以隐藏 IP

... was edited to hide the IP

推荐答案

首先，请检查 php 中对 mysql 和 mysqli 的支持.在您的网站位置(/var/www/html)创建 index.php，以下是文件内容.

Firstly,please check the mysql and mysqli support in php . Create index.php at your web location (/var/www/html),Following is the file content.

<?php
 echo phpinfo();
?>

在网络浏览器中访问 index.php(http://localhost/index.php)并检查mysql 和 mysqli 项是否存在.

access index.php at the web browser(http://localhost/index.php) and check the mysql and mysqli item is exist or not .

如果没有，请重新安装php mysql支持，在你的情况下使用apt-get命令安装它们.

if not , please re-install the php mysql support , in your case use apt-get command to install them.

其次，请登录您的 mysql 并检查远程连接状态.

Secondly , please login into you mysql and check the remote connections status.

// in command line
mysql -u your user -p your password
// execute following sql
select user,password,host from mysql.user;

结果如下

然后请检查主机栏，如果此栏值为具体的ip或主机名(localhost,192.168.xxx.xxx等)，请改成任何客户端都可以连接.假设您的数据库连接信息如下

then please check the host column, if this column values are the specific ip or hostname(localhost,192.168.xxx.xxx etc),please change to any client can connect it. Assume your database connections info like following

database  demo
user  demo
password demo
//execute the sql to change
grant all on demo.* to demo@'%' identified by 'demo';
flush privileges;

完成上述步骤后，请再次尝试从php连接mysql.

after above steps done and please try to the connect mysql from php again.

这篇关于MySQL 数据库接受远程连接，但不接受来自 PHP 的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！