0

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.

The Connection was refused with this error

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

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

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.

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)

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.

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 | %             |
+------------------+-------------------------------------------+---------------+

... was edited to hide the IP

Maineaic
  • 1
  • 2

1 Answers1

0

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();
?>

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

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

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;

following is the result enter image description here

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;

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

Cherry
  • 388
  • 1
  • 3
  • 13
  • The webhost I am using is seperate from the Dedicated Server (running the MySQL server) After checking phpinfo() I am seeing the same results that you posted here. Nothing seems to be wrong with the PHP configuration. – Maineaic Mar 18 '15 at 15:54
  • Did you can execute the "select user,password,host from mysql.user;" from mysql? – Cherry Mar 18 '15 at 16:18
  • I did, I edited my main question and put the result at the bottom – Maineaic Mar 18 '15 at 16:51
  • Did you try use steam account to connect ? please use grant command to add new account(the account must be unique in above accounts). grant all on (your database name).* to (your wanted account)@'%' identified by 'your wanted password'; flush privileges; @Maineaic – Cherry Mar 18 '15 at 23:40
  • Yes, I tried to use the user steam. I ran that command you told me to run and it still didn't help, I'm really starting to lose hope. – Maineaic Mar 19 '15 at 00:09
  • Please change the way to connect mysql , this link for your reference: http://php.net/manual/en/function.mysql-connect.php @Maineaic – Cherry Mar 19 '15 at 01:50
  • :O Is it possible for an explanation as to the reason I can't use mysqli? – Maineaic Mar 19 '15 at 02:21
  • Basically I'm not find the root cause for your case until now , mysqli and mysql-connect function are work fun on my server .But i suggest you check the mysql configuration(my.cnf). BTW,my server is also running with Ubuntu 14.04.@Maineaic – Cherry Mar 19 '15 at 04:47