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:

root@192.168.100.1 /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

root@192.168.100.1 /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:

root@192.168.100.1 /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:

root@192.168.100.2 /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.

  • When you try to connect from .100.2 via command line what are the errors you are seeing? – user87664 Dec 12 '14 at 20:39
  • Nothing really, it's like it's the connection isn't being responded too. – Xeoncross Dec 12 '14 at 20:46
  • 1
    Did you check your GRANT's? They may be scoped to localhost. – thinice Dec 12 '14 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 '14 at 23:03
  • Thanks @Navern, I checked and double-checked the password and that wasn't the problem. See updated post. – Xeoncross Dec 13 '14 at 0:27
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

| improve this answer | |
  • 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 '14 at 21:02
  • 1
    "ticket", that made me laugh. Welcome to the ServerFault Help Desk ;) – user87664 Dec 12 '14 at 21:05
  • Good idea, I added a catch-all grant and I still can't connect. – Xeoncross Dec 12 '14 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 '14 at 21:41
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.