Can't ssh tunnel to access a remote mysql server

Question

I can't seem to figure out why I can't use ssh tunnel to connect to my remote MySQL server.

I do ssh tunnel with

[hobbes3@hobbes3] ~ $ ssh linode -L 3307:localhost:3306

Then on another terminal, I try

[hobbes3@hobbes3] ~ $ mysql -h localhost -P 3307 -u root --protocol=tcp -p
Enter password:
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 2

On the server, it shows this:

root@li534-120 ~ # channel 4: open failed: connect failed: Connection refused

Here is my my.cnf on the server:

[mysqld]
# Settings user and group are ignored when systemd is used (fedora >= 15).
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
user=mysql

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Semisynchronous Replication
# http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html
# uncomment next line on MASTER
;plugin-load=rpl_semi_sync_master=semisync_master.so
# uncomment next line on SLAVE
;plugin-load=rpl_semi_sync_slave=semisync_slave.so

# Others options for Semisynchronous Replication
;rpl_semi_sync_master_enabled=1
;rpl_semi_sync_master_timeout=10
;rpl_semi_sync_slave_enabled=1

# http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
;performance_schema


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

[mysqld]
port = 3306
socket=/var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 64M
max_allowed_packet = 128M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache = 8
max_connections = 25
query_cache_size = 16M
table_open_cache = 1024
table_definition_cache = 1024
tmp_table_size = 32M
max_heap_table_size = 32M
bind-address = 0.0.0.0

Now sure if this helps but here is the MySQL user list:

mysql> select * from mysql.user;
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host      | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost | root | *664328D3C5E263F4FB25185681AAE7E92B01B2B0 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| 127.0.0.1 | root | *664328D3C5E263F4FB25185681AAE7E92B01B2B0 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| ::1       | root | *664328D3C5E263F4FB25185681AAE7E92B01B2B0 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
3 rows in set (0.00 sec)

I read about how MySQL treats localhost vs 127.0.0.1 as connecting via a socket or TCP, respectively. But I'm starting to get confused on what's really going on or if socket vs TCP is even the issue.

Thanks in advance and I'm open for any tips and suggestions!

Some more info:

My MySQL client, running OS X 10.8.4, is

mysql  Ver 14.14 Distrib 5.6.10, for osx10.8 (x86_64) using  EditLine wrapper

My MySQL server, running on CentOS 6.4 32-bit, is

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+--------------------------------------+
| Variable_name           | Value                                |
+-------------------------+--------------------------------------+
| innodb_version          | 1.1.8                                |
| protocol_version        | 10                                   |
| slave_type_conversions  |                                      |
| version                 | 5.5.28                               |
| version_comment         | MySQL Community Server (GPL) by Remi |
| version_compile_machine | i686                                 |
| version_compile_os      | Linux                                |
+-------------------------+--------------------------------------+
7 rows in set (0.00 sec)

EDIT:

I redid the ssh tunnel with -vvv, disabled iptables, and now when I try mysql -h 127.0.0.1 -P 3307 -u root --protocol=tcp -p, I get the following debug logs on the linode server:

debug1: Connection to port 3307 forwarding to localhost port 3306 requested.
debug2: fd 11 setting TCP_NODELAY
debug3: fd 11 is O_NONBLOCK
debug3: fd 11 is O_NONBLOCK
debug1: channel 4: new [direct-tcpip]
channel 4: open failed: connect failed: Connection refused
debug2: channel 4: zombie
debug2: channel 4: garbage collecting
debug1: channel 4: free: direct-tcpip: listening port 3307 for localhost port 3306, connect from 127.0.0.1 port 49995, nchannels 5
debug3: channel 4: status: The following connections are open:
#3 client-session (t4 r0 i0/0 o0/0 fd 8/9 cc -1)

Is mysql listening on the server? try this on the server itself: `mysql -h 127.0.0.1 -P 3306 -u root --protocol=tcp -p` — toppledwagon, Jul 05 '13 at 22:55

score 2 · Answer 1 · edited Jul 05 '13 at 19:16

2

Using the name 'localhost' tries to connect using a UNIX DOMAIN socket by default, which is only posible when the client and the server are in the same machine because they need to access to local socket entry in the filesystem. (In many implementations of UNIX/Linux it's the faster way to connect and it's recommended).

127.0.0.1 tries to connect using the Internet network stack (AF_INET) and, so, adds a little overhead, but seems that in your case is necessary for communications to another system.

Try to connect using

mysql -h 127.0.0.1 -P 3307 -u root --protocol=tcp -p

edited Jul 05 '13 at 19:16

Jim Dennis

807
1
10
22

answered Jul 03 '13 at 09:29

PerroVerd

186
5

1

The client is giving a similar message: `ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0`. The server says `channel 4: open failed: connect failed: Connection refused`. – hobbes3 Jul 03 '13 at 15:13
So now it seems a network problem rather than a mysql problem. I believe CentOS has a firewall enabled by default, so try disabling it temporary with `service iptables stop` – PerroVerd Jul 04 '13 at 08:48
1

I redid the ssh tunnel with `-vvv` and disabled 'iptables'. Now I have more debug logs. See my updated question. Thanks! – hobbes3 Jul 05 '13 at 18:45

score 2 · Answer 2 · edited Apr 13 '17 at 12:14

2

I have answered this question,try this step, do not use localhost use 127.0.0.1 ip address instead Connecting to MYSQL over SSH tunnel

edited Apr 13 '17 at 12:14

Community

1

answered Jul 05 '13 at 19:00

Sharad Chhetri

129
4

Can't ssh tunnel to access a remote mysql server

2 Answers2