2

I have two servers on the same subnet.

  • 192.168.100.1 (mysql)
  • 192.168.100.2 (php)

MySQL seems to be running fine on it's box but I can't seem to connect to it. These are both ubuntu boxes and iptables and ufw are not running. The following commands are all from the MySQL box itself. None of these work from the .100.2 (php) box.

my.cnf

#bind-address           = 127.0.0.1
bind-address = 192.168.100.1

netstat says this is true:

[email protected] /etc/mysql $ netstat -plutn
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      8583/sshd       
tcp        0      0 192.168.100.1:3306      0.0.0.0:*               LISTEN      30654/mysqld    
tcp        0      0 0.0.0.0:2828            0.0.0.0:*               LISTEN      29946/monit     
tcp        0      0 0.0.0.0:1167            0.0.0.0:*               LISTEN      23175/cdp       
tcp6       0      0 :::22                   :::*                    LISTEN      8583/sshd  

telnet says it's good

[email protected] /etc/mysql $ telnet 192.168.100.1 3306
Trying 192.168.100.1...
Connected to 192.168.100.1.
Escape character is '^]'.
[
5.5.40-0ubuntu0.12.04.1&KM~Se$!u??L\hw=gp{<Uvrmysql_native_password

!#08S01Got packets out of orderConnection closed by foreign host.

mysql client says it's good:

[email protected] /etc/mysql $ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

When I try to connect from .100.2 I get nothing back from the connection until it timeouts:

[email protected] /etc/php5/fpm $ mysql -uroot -p -h 192.168.100.1 --connect-timeout 4
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.100.1' (4)

I can ping .100.1 from the .100.2 box though with sub ms response times.

Here is the MySQL Grant settings:

mysql> SELECT user,host from mysql.user;
+------------------+--------------+
| user             | host         |
+------------------+--------------+
| root             | 127.0.0.1    |
| root             | 192.168.1.%  |
| root             | 192.168.100.%|
| root             | 192.168.200.%|
| root             | ::1          |
| debian-sys-maint | localhost    |
| example          | localhost    |
| o3backup         | localhost    |
| root             | localhost    |
| root             | voice-mysql  |
+------------------+--------------+
10 rows in set (0.00 sec)

I added a new grant for everywhere and the problem persists.

mysql> show grants for 'root'@'%';
+--------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '...' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Update

Turns out the system was somehow locked-down by changes made to system files I was unaware of. Apparently it was blocking certain network operations that aren't standard. This is honestly frustrating that the same people that gave me this box forgot all about this. I honestly have no idea what they did to it and even they cannot remember. Nevertheless, the answers listed here are the most thorough trouble-shooting guide for normal people though. It should have been one of these problems.

5
  • When you try to connect from .100.2 via command line what are the errors you are seeing?
    – user87664
    Dec 12, 2014 at 20:39
  • Nothing really, it's like it's the connection isn't being responded too.
    – Xeoncross
    Dec 12, 2014 at 20:46
  • 1
    Did you check your GRANT's? They may be scoped to localhost.
    – thinice
    Dec 12, 2014 at 20:51
  • Create new user 'root'@'192.168.100.2' with simple password like 12345. I believe you are mistaken passswords. User 'root'@'192.168.200.%' and user 'root'@'localhost' are different users with different passwords.
    – Navern
    Dec 12, 2014 at 23:03
  • Thanks @Navern, I checked and double-checked the password and that wasn't the problem. See updated post.
    – Xeoncross
    Dec 13, 2014 at 0:27

2 Answers 2

2

It looks like you are getting the Enter password: prompt when connecting to your mysql server from your php server but nothing after that. If so then the mysql user account settings for the root user are probably denying the connection. Have a look at your root mysql user account and make sure the Host field is set to allow the connection.

From (MySQL) Causes of Access-Denied Errors:

If you do not know the IP address or host name of the machine from which you are connecting, you should put a row with '%' as the Host column value in the user table.

After trying to connect from the client machine, use a SELECT USER() query to see how you really did connect. Then change the '%' in the user table row to the actual host name that shows up in the log. Otherwise, your system is left insecure because it permits connections from any host for the given user name.

I suspect the way the tables sort when you connect are causing you problems. You may want to review Access Control, Stage 1: Connection Verification

4
  • Sorry, I forgot to put the user permissions into the ticket. I updated the post. As you can see, I have .100.% permissions which should let me connect.
    – Xeoncross
    Dec 12, 2014 at 21:02
  • 1
    "ticket", that made me laugh. Welcome to the ServerFault Help Desk ;)
    – user87664
    Dec 12, 2014 at 21:05
  • Good idea, I added a catch-all grant and I still can't connect.
    – Xeoncross
    Dec 12, 2014 at 21:32
  • It would seem that you are not entering the password that mysql is expecting for the user/host pair you are connecting as/from.
    – user87664
    Dec 12, 2014 at 21:41
0

Read the fine manual for error 2003 at http://dev.mysql.com/doc/refman/5.0/en/access-denied.html

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .