Can't ssh tunnel to access a remote mysql server
- by hobbes3
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)
