LinuxQuestions.org - [SOLVED] MySQL Remote access forbidden only to a specific client

- Linux - Server (https://www.linuxquestions.org/questions/linux-server-73/)

- - MySQL Remote access forbidden only to a specific client (https://www.linuxquestions.org/questions/linux-server-73/mysql-remote-access-forbidden-only-to-a-specific-client-4175491964/)

MySQL Remote access forbidden only to a specific client

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

can they

Code:

telnet mysql-serverIP 3306

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

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.

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

Code:

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

output please.

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.

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.

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?

@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

Get your client's IP = xxx

and try to query: show grants for hwcharts@xxx

and can you paste query's output?

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.

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.

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!

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?