[SOLVED] MySQL Remote access forbidden only to a specific client

romagnolo · 01-20-2014, 09:19 PM

Hi,

I'm hit by a weird problem which is preventing a single specific instance of mysql-client to connect to a remote mysql-server. Connecting a client from other hosts to the same server works.

Details of setup:
A MySQL server is located at central.hwcharts.com; it is the master of a basic master-slave replication which replicates toward host0.hwcharts.com, located on a different physical machine which virtualizes (OpenVZ) the VM where the slave runs in.

The replication itself works flawlessly, but the problem is that a mysql-client launched from the "slave" machine cannot connect to the master:

Code:

mysql -h central.hwcharts.com -u hwcharts -pvery-long-pass hwcharts
ERROR 2003 (HY000): Can't connect to MySQL server on 'central.hwcharts.com' (111)

The same exact command executed from two other generic machines works OK and successfully connects to the master DB. Details on picture:

Code:

                               +---------------------------------------------------------------------+
    +--------------------+     |                          host0.hwcharts.com                         |
    |central.hwcharts.com|     |---------------------------------------------------------------------|
    |--------------------|     |                                                                     |
    |                    |     |   +-----+        +--------+        +-------------+                  |
    |    MySQL master    |-------->| NAT |---+--->| OpenVZ |---+--->| MySQL slave |                  |
    |                    |     |   +-----+   |    +--------+   |    +-------------+                  |
    +--------------------+     |             |                 |    +------------------------------+ |
                               |             |                 |    |      another generic VM      | |
                               |             |                 +--->| MySLQ-client works from here | |
                               |             |                      +----------------------------- + |
                               |             |                                                       |
                               |             |    +------------------------------+                   |
                               |             |    |  generic physical computer   |                   |
                               |             +--->| MySLQ-client works from here |                   |
                               |                  +------------------------------+                   |
                               |                                                                     |
                               +---------------------------------------------------------------------+

central.hwcharts.com is a rented VPS on its own; host0.hwcharts.com instead maps the IP to my home modem.

MySQL version is 5.5.31 on every machine, for both client and server software.
Operating systems are:

central.hwcharts.com: Debian 6.0.4
host0.hwcharts.com (every OpenVZ VM): Debian 7.1
host0.hwcharts.com (generic computer): Debian 6.0.3

Habitual · 01-21-2014, 08:11 AM

can they

Code:

telnet mysql-serverIP 3306

?

romagnolo · 01-21-2014, 08:37 AM

From any host, including the "slave" host, I get:

Code:

$ telnet central.hwcharts.com 3306
Trying 75.127.3.156...
Connected to central.hwcharts.com.
Escape character is '^]'.
Y
5.5.31-1~dotdeb.0-logBq3IUNf�*b2o\^DxSrd!mysql_native_password

pingu · 01-21-2014, 08:50 AM

Wild guess: MySQL-slave-server does not have proper nameserver configured. The master-slave replication is done with ip-adress not hostname.
---
Edit: Sorry didn't see your reply, obviously hostname lookup works fine.

zhjim · 01-21-2014, 09:12 AM

I'd blame it on the access rules of mysql.users. Or maybe some fancy nat rules for this particular machine.

Habitual · 01-21-2014, 09:40 AM

Code:

mysql -h central.hwcharts.com -uroot -p -e "use mysql; select User, Host from user;"

output please.

romagnolo · 01-21-2014, 09:43 AM

I believe my setup is quite plain basic w/o much fancy customizations:

Code:

select user,host,password from mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *non-null-hash-replaced-xxxxxxxxxxxxxxxxx |
| debian-sys-maint | localhost | *non-null-hash-replaced-xxxxxxxxxxxxxxxxx |
| greatturn        | localhost |                                           |
| replication      | %         | *non-null-hash-replaced-xxxxxxxxxxxxxxxxx |
| hwcharts         | %         | *non-null-hash-replaced-xxxxxxxxxxxxxxxxx |
+------------------+-----------+-------------------------------------------+

For the NAT itself, it's a basic home DSL router. The NAT table is:

Code:

Rule	Application	Prot	Port_a	port_b 	Local IP address    comment
1 	EchoLink 	ALL 	5198 	5199 	192.168.1.23        unrelated physical computer
2 	- 	        - 	0 	0 	0.0.0.0
3 	-               -       0       0       0.0.0.0
4 	-               -       0       0       0.0.0.0
5 	- 	        - 	0 	0 	0.0.0.0
6 	GTservers 	ALL 	5100 	5149 	192.168.1.102       unrelated VM
7 	civservers 	ALL 	5000 	5099 	192.168.1.101       unrelated VM
8 	FTP     	ALL 	21 	21 	192.168.1.101       unrelated VM
9 	SSH 	        ALL 	22 	22 	192.168.1.10        OpenVZ physical host
10 	web 	        ALL 	80 	80 	192.168.1.200       unrelated VM
11 	mySQL 	        ALL 	3306 	3306 	192.168.1.102       unrelated VM
12 	sync akn 	TCP 	5150 	5150 	192.168.1.102       unrelated VM

The MySQL slave runs from VM 110 (IP 192.168.1.110), which as no NAT rules set up.

romagnolo · 01-21-2014, 09:48 AM

Output of access attempts:

Executed from the slave host

Code:

$ mysql -h central.hwcharts.com -uroot -p -e "use mysql; select User, Host from user;"
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on 'central.hwcharts.com' (111)

Executed from one of the "generic" VM, from inside the same OpenVZ host:

Code:

$ mysql -h central.hwcharts.com -uroot -p -e "use mysql; select User, Host from user;"
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'87.19.245.199' (using password: YES)

Of course root access is explicitly forbidden.

pingu · 01-21-2014, 10:03 AM

Are you sure replication works?
It is not enough checking SHOW SLAVE STATUS, to be absolutely sure you have to check the records in slave's db.
Can you connect to slaves db with mysql client on itself?

romagnolo · 01-21-2014, 10:41 AM

@pingu
I've checked right now, replication works: any change to master's tables (hwcharts.*) is immediately propagated to the slave database, flawlessly.

MySQL client-server connection also works fine between the following combination of hosts, except between host0.hwcharts.com and central.hwcharts.com:

Code:

From client                           To server              Works?    Comment

host0.hwcharts.com ("slave" VM host)  central.hwcharts.com   No
host0.hwcharts.com (any other host)   central.hwcharts.com   Yes
host0.hwcharts.com                    host0.hwcharts.com     Yes       localhost connection
central.hwcharts.com                  central.hwcharts.com   Yes       localhost connection

ubungu · 01-21-2014, 10:59 AM

Get your client's IP = xxx

and try to query: show grants for hwcharts@xxx

and can you paste query's output?

romagnolo · 01-21-2014, 11:11 AM

The grants for user 'hwcharts'@'%' (the only hwcharts user) in master DB are:

Code:

show grants;
+---------------------------------------------------------------------------------------------------------+
| Grants for hwcharts@%                                                                                   |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hwcharts'@'%' IDENTIFIED BY PASSWORD '*non-null-hash-replaced-xxxxxxxxxxxxxxxxx' |
| GRANT ALL PRIVILEGES ON `hwcharts`.* TO 'hwcharts'@'%'                                                  |
+---------------------------------------------------------------------------------------------------------+

User hwcharts is not defined for any other host, as shown is post #7.

pingu · 01-21-2014, 11:20 AM

It is of course good to check the permissioms on the mysql server.
But the message you get - "Can't connect to MySQL server" - makes me believe that you don't reach the server at all.
I think you need to find out where the connection is stopped. You have 4 systems to check, taken from your diagram:
MySQL slave
OpenVZ
NAT (home DSL router)
MySQL master

I suggest you run tcpdump on all these while trying to connect, it will at least show how far the request goes. Maybe you can't on the NAT device but if so just leave it out for now.
Also please post firewall rules for all systems. ("iptables -L")

And a small note: you have posted valid url's & ip-adress in this thread, maybe not a good idea! There are always bad robots out there sniffing for places to hack.
Easy fix is to write posts in whatever text-editor you have and do a search-replace. Then post with fake ip & url, and edit your earlier posts too.

pingu · 01-21-2014, 11:29 AM

A very wild shot in the dark, should not be possible but I've seen a few really weird home "routers" before...
Could it be that you can have only one connection at a time from a client?
Check it by opening two connections to mysql server from one client. You could also stop the slave and then try to connect via terminal.
Well, as I said not very likely - but then it is a weird problem!

pingu · 01-21-2014, 12:43 PM

Come to think of it, when did you start the replication, what have you done after that?
If you started replication and then later changed firewall rules the existing connection could still be kept alive but no new connections can be made. This applies on the client side too, it doesn't have to be the central-server that blocks.
So think of it, what has been done on the servers in the communication chain after replication was set up?