Connect to Mysql Server (windows) from Linux Server

Using "mysql -hBOB -u user -pXXXXX" over SSH is making a 'localhost' connection thru the 'mysql' command line program, not an 'anyhost' connection.

I can make "mysql -hBOB -u user -pXXXXX" work from the command line on my Linux machine but I do have the host-to-IP-addresses listed in my 'hosts' file.  Using the IP address like "mysql -h10.10.10.10 -u user -pXXXXX" sometimes works better.  And it works using the MySQL or mysqli' PHP driver in a PHP program, web or command line.

Dave Baldwin. Thank you for the comment.

where "BOB" = direct IP to the mysql server

so what I meant was that when I tried  : mysql -h10.10.10.10 -u user -pXXXXX

I could not connect when using a PW but when the password was removed it connected.

The only way I know for that to happen is because there is an entry for that username that doesn't have a password.

Dave Baldwin.

The user Had a PW
Tested it and it throws the ERROR

# mysql -h192.168.3.5 -u newuser1234 -pupUya5&fr
]# ERROR 1045 (28000): Access denied for user 'newuser1234'@'192.168.0.250' (using password: YES)

Then I removed the PW for that user on the MYSQL Server and I get the following.

# mysql -h192.168.3.5 -u newuser1234 -pupUya5&fr

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3728
Server version: 5.6.15-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

SO why will it connect with no password? But not connect using a password?

Did you check to see if there is another user with the same name?

No other user of the same name.

I did try simple passwords and that works on SSH. Waiting for test results from PHP side.

Ok on the PHP Side this is what happens:

Warning: mysqli::mysqli(): (HY000/2003): Can't connect to MySQL server on '192.168.3.5' (13) in /var/www/html/gateway/gw-func-connection-scooters.php on line 16

<?php
/*
*
File Name: gateway-connection.php
Description: PHP include for connecting to mySql db for gateway
Version: 1.1
Version History:
1.1 Spec chars removed frm pw for SSL	JLM		05/08/14
1.0 Template created					JLM		04/09/14
*
*/
define("HOST", "192.168.3.5");	//not localhost (webserver) but data server
define("USER", "newuser1234");		
define("PASSWORD", "Simple1234"); 
define("DATABASE", "database");
$conn = new mysqli(HOST, USER, PASSWORD, DATABASE);
?>

Select allOpen in new window

Dave Baldwin,

For your question

So the next question is... what does work?  How are you able to access that server and from where?  

Please re-read my previous comments and posts as I do clearly indicate the WHERE.

Given that we are able to make connections to mysql via SSH.  And that the above PHP script should be ordinary and should work.

What other possibilities are there for this not connecting?

Since the user you have listed above is Not working correctly, I'm going to ignore that one.  Are any other users able to login using their correct password?

We can connect with ANY user via SSH. As soon as we try using that user in the PHP login script it fails. Checked to see if it was even sending the request and it appears as though it is not even sending the request.

I am wondering if some of the php script could be in error. Or perhaps a library of something is missing for PHP on the Web Server.

Thoughts?

ok my PHP version is 5.3.3

We tried some new things.

Moved DB to a 'Linux' box Centos 6.5 64bit. It is now in the green zone.
So we have aka WEB running Centos in the DMZ and DB running Centos in the Green. Firewall rules have been made for the mysql connections.
So Gateway01= Web
and Gateway02 = DB Server

We figured lets verify that database connections can be made and we are not going crazy. We set up a TS3 Server on Gateway01 storing its DB on Gateway02.  All systems worked and DB connection is good.

So now we moved the website to Gateway01 and moved the DB to Gateway02 for our little web project.  Again tried to connect with PHP and nothing.
Same Errors :

Warning: mysqli::mysqli(): (HY000/2003): Can't connect to MySQL server on '192.168.3.10' (13) in /var/www/html/gateway/gw-func-connection.php on line 18

Scanned with nmap:

[root@gateway01 ~]# nmap -sT 192.168.3.10/32

Starting Nmap 5.51 ( http://nmap.org ) at 2014-05-09 12:06 CDT
Note: Host seems down. If it is really up, but blocking our ping probes, try -Pn
Nmap done: 1 IP address (0 hosts up) scanned in 3.07 seconds

And then this---

[root@gateway01 ~]# nmap -Pn 192.168.3.10/32

Starting Nmap 5.51 ( http://nmap.org ) at 2014-05-09 12:09 CDT
Nmap scan report for 192.168.3.10
Host is up (0.0012s latency).
Not shown: 995 filtered ports
PORT      STATE SERVICE
21/tcp    open  ftp
22/tcp    open  ssh
53/tcp    open  domain
3306/tcp  open  mysql
10000/tcp open  snet-sensor-mgmt

Nmap done: 1 IP address (1 host up) scanned in 4.70 seconds

Ok so we can see things for sure.

So we tried some simple clean php connection test.

   <?
        $connect=mysql_connect("dbserver","dbuser","dbpassword") or die("Unable to Connect");
        mysql_select_db("dbname") or die("Could not open the db");
        $showtablequery="SHOW TABLES FROM dbname";
        $query_result=mysql_query($showtablequery);
        while($showtablerow = mysql_fetch_array($query_result))
        {
        echo $showtablerow[0]." ";
        }
        ?>

Select allOpen in new window

of course we did verify the user, and server, and password, and dbname.

And we got a blank screen.   Any thoughts?
We have tried simple passwords (no special chars, and short passwords, (or normal one is between 20 and 30 char)

That works perfectly here from both Windows and Linux.  Try it with the opening tag as '<?php' just in case 'short_open_tags' is off.

Tried that as well and no cigar

That works perfectly here from both Windows and Linux.  Try it with the opening tag as '<?php' just in case 'short_open_tags' is off.

Put an 'echo' at the beginning to make sure PHP is running and turn on all error_reporting.

<?php
error_reporting(E_ALL);
echo "MySQL Connection test<br>";
$connect=mysql_connect("dbserver","dbuser","dbpassword") or die("Unable to Connect");
mysql_select_db("dbname") or die("Could not open the db");
$showtablequery="SHOW TABLES FROM dbname";
$query_result=mysql_query($showtablequery);
while($showtablerow = mysql_fetch_array($query_result))
{
echo $showtablerow[0]."<br>\r\n";
}
?>
				

Select allOpen in new window

Run that code on the command line on the DB server and see what you get.  Use 'root' and 'localhost' if it doesn't work the first time.

I get the following.

MySQL Connection test
Warning: mysql_connect(): Can't connect to MySQL server on '192.168.3.10' (13) in /var/www/html/phptest/connect.php on line 4 Unable to Connect

And when I even try and connect to the local web server.

MySQL Connection test
Warning: mysql_connect(): Can't connect to MySQL server on '192.168.0.250' (13) in /var/www/html/phptest/connect01.php on line 5 Unable to Connect

could this be a php setting?

MySQL sometimes installs where it only connects from 'localhost'.  In '/etc/mysql/my.cnf', see if you have 'bind-address = 127.0.0.1'.  If you do, either comment it out or set it to 'bind-address = 0.0.0.0'.

Do I do this on Gateway01 or Gateway02?

MySQL sometimes installs where it only connects from 'localhost'.  In '/etc/mysql/my.cnf', see if you have 'bind-address = 127.0.0.1'.  If you do, either comment it out or set it to 'bind-address = 0.0.0.0'.

Here is the my.cnf config
Gateway01

[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
user=mysql

symbolic-links=0
default-storage-engine = MyISAM

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Select allOpen in new window

and Gateway02

[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
user=mysql

symbolic-links=0
default-storage-engine = MyISAM

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Select allOpen in new window

Ok, check the firewall (iptables) on both machines.  Also, I just noticed that your servers are on different network segments, at least they would be on my networks.   192.168.0.250 and 192.168.3.10 .  Can you ping between them?

Yes we can ping between them. At this moment Linux Firewall is off.  And proper rules have been established on the Physical Firewall.

You said 64-bit.  Are you running IPV6 on the LAN?

Yes to 64-bit.
No to IPV6

I asked you to run the PHP code on the computer where the database is using 'localhost' for the server name.  I don't see a response to that.  ??

Run this script on the SSH command line?

<?php
error_reporting(E_ALL);
echo "MySQL Connection test<br>";
$connect=mysql_connect("dbserver","dbuser","dbpassword") or die("Unable to Connect");
mysql_select_db("dbname") or die("Could not open the db");
$showtablequery="SHOW TABLES FROM dbname";
$query_result=mysql_query($showtablequery);
while($showtablerow = mysql_fetch_array($query_result))
{
echo $showtablerow[0]."<br>\r\n";
}
?>

Select allOpen in new window

Glad you got it working.

We established that a database connection could be made from the web server to the database server.  We also established that the web server could serve up web pages.  
So now we find out why in PHP that a connection could not be made. Since we were using the command 'mysqli' in our database connection. We created a php info and found that PHP was  not loading support for 'mysqli'. So after removing PHP and installing it again we found in the php info file that it now showed support for 'mysqli'.