3

I'm trying to connect from an Ubuntu server to a RHEL6 server using mysql. I've tried telneting into the server as well as trying to connect with mysql. I've tried commenting out the bind-address but didn't have any success with that either. I don't get an error code or anything with telnet. It just fails after a minute or so. With mysql, I get this error code ERROR 2003 (HY000): Can't connect to MySQL server on 'SERVER_IP' (111). "SERVER_IP" is of course a placeholder where actual error gives that actual IP.

I've included my my.cnf as well as well as my iptables from the destination server.

On Destination Server...

my.cnf:

[mysqld]
bind-address=0.0.0.0
tmp_table_size=512M
max_heap_table_size=512M
sort_buffer_size=32M
read_buffer_size=128K
read_rnd_buffer_size=256K
table_cache=2048
key_buffer_size=512M
thread_cache_size=50
query_cache_type=1
query_cache_size=256M
query_cache_limit=24M
#query_alloc_block_size=128
#query_cache_min_res_unit=128
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table
innodb_log_files_in_group=2
innodb_buffer_pool_size=32G
innodb_log_file_size=512M
innodb_additional_mem_pool_size=20M
join_buffer_size=128K

max_allowed_packet=100M
max_connections=256
wait_timeout=28800
interactive_timeout=3600

# modify isolation method for faster inserting.
# Do not uncomment the line below unless you understand what this does.
# transaction-isolation = READ-COMMITTED
# do not reverse lookup clients
skip-name-resolve

#long_query_time=6
#log_slow_queries=/var/log/mysqld-slow.log
#log_queries_not_using_indexes=On
#log_slow_admin_statements=On

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#Added by Magento ECG
long_query_time=1
slow_query_log

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

iptables:

:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 225 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp -i eth1 --dport 11211 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT

sudo netstat -ntpl

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:3306                0.0.0.0:*                   LISTEN      -
tcp        0      0 0.0.0.0:11211               0.0.0.0:*                   LISTEN      -
tcp        0      0 0.0.0.0:2123                0.0.0.0:*                   LISTEN      -
tcp        0      0 0.0.0.0:1581                0.0.0.0:*                   LISTEN      -
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      -
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      -
tcp        0      0 :::11211                    :::*                        LISTEN      -
tcp        0      0 :::22                       :::*                        LISTEN      -
tcp        0      0 :::225                      :::*                        LISTEN      -

sshd_config:

Protocol 2
SyslogFacility AUTHPRIV
PasswordAuthentication yes
ChallengeResponseAuthentication no
GSSAPIAuthentication yes
GSSAPICleanupCredentials yes
UsePAM yes
AcceptEnv LANG LC_CTYPE LC_NUMERIC LC_TIME LC_COLLATE LC_MONETARY LC_MESSAGES
AcceptEnv LC_PAPER LC_NAME LC_ADDRESS LC_TELEPHONE LC_MEASUREMENT
AcceptEnv LC_IDENTIFICATION LC_ALL LANGUAGE
AcceptEnv XMODIFIERS
X11Forwarding yes
Subsystem       sftp    /usr/libexec/openssh/sftp-server
eisaacson
  • 525
  • 3
  • 8
  • 20
  • 2
    are you 100% sure there are no other firewalls in between? Assuming you can Ssh between the two – Doon Nov 04 '13 at 19:28
  • I am unsure of other firewalls that might be in between. One of the servers I've tried to connect from is a rackspace server. The others I'm told bypass our firewall. SSH works fine from all servers I've tried. – eisaacson Nov 04 '13 at 19:47
  • Add output of the following on the server: $ sudo netstat -ntpl – dmourati Nov 04 '13 at 19:47
  • I added that output to the question. – eisaacson Nov 04 '13 at 19:51
  • This is how I've solved this problem in the past http://stackoverflow.com/questions/8380797/enable-remote-mysql-connection – Jake H Nov 04 '13 at 20:27
  • Jake, I'm not seeing anything related here. – eisaacson Nov 04 '13 at 20:36
  • From the server, $ telnet localhost 3306. If that connects, you have a routing or firewall problem between client and server. If that doesn't connect, you still have some problem with your mysql server. – dmourati Nov 04 '13 at 21:08
  • Judicious usage of tcpdump/wireshark seems like the best approach here. Starting a capture on both the local and remote looking for port 3306. – Zoredache Nov 04 '13 at 21:21
  • try running the command tcptraceroute from the ubuntu box (you might need to install it apt-get install tcptraceroute) like this `tcptraceroute ip.of.server 3306`. This should show you if there is a firewall blocking your connection on 3306, and if so where it is. – Doon Nov 05 '13 at 06:34
  • Do you connect with ssh to the same address you use for the telnet? Can you telnet other ports than 3306 on the same address? – Guido Vaccarella Nov 04 '13 at 21:13

2 Answers2

1

Should work fine. First thing I would check is to make sure that your local machine (the one trying to connect to your MySQL box) can make outbound connections on that port to any machine.

Are these local? Or are they on separate networks? Are there any firewalls between them?

Seems like the kind of problem that requires more information.

Michael Pobega
  • 934
  • 5
  • 12
  • I've added netstat data to the question and your firewall questions are answered in the question's comments. Thank you. – eisaacson Nov 04 '13 at 19:51
  • You said you can get in via SSH, correct? Have you tried allowing SSH connections on 3306 and seeing if you can still connect? That should at least show you whether it's the service or something external that's causing a problem. **Edit:** Don't change your SSH port, but rather add another `Port #` line to your sshd_config (openssh allows a single SSH server to listen on multiple ports) – Michael Pobega Nov 04 '13 at 20:14
  • Craps! If telnet does not get any prompt, SSH won't either. The only thing we can infer from SSH connection succeeds on port 22 and telnet fails on port 3306 is that there is no routing problem. To check the service, on the server itself, one has to try mysql -H localhost -u $user -p. If mysql client is working locally, the service is working. – philippe Nov 06 '13 at 08:35
  • Derp. I goofed and made it overly complicated, thanks for pointing that out. Btw OP, did you ever figure out the root cause of the issue? – Michael Pobega Nov 07 '13 at 18:41
  • I haven't solved the problem. Holiday/Black Friday priorities took me off this for a bit. – eisaacson Dec 05 '13 at 16:20
1

You first need to make sure your own iptables is not responsible for the trouble. Try adding logging to your iptables script:

-A INPUT -m state --state NEW -m tcp -p tcp -i eth1 --dport 11211 -j ACCEPT
-A INPUT -j LOG --log-prefix "DROPPED BY MY IPTABLES"
-A INPUT -j REJECT --reject-with icmp-host-prohibited

This way, you will get logs on which packets are DROPPED and which are ACCEPTed, you will know whether or not your firewall is to blame.

EDIT: changed to double quotes to avoid errors

eisaacson
  • 525
  • 3
  • 8
  • 20
philippe
  • 2,303
  • 4
  • 32
  • 53