I have set up a SSH tunnel between two servers A and B. B has MySQL server, and this works:
mysql -h localhost -P 3306 -u user -p
mysql -h 127.0.0.1 -P 3306 -u user -p
bind-address = 127.0.0.1
# Next addr differs slightly, but anyway
bind-address = 18.104.22.168
(A) localhost(9989) -> (B) localhost(3306)
mysql -v -h 127.0.0.1 -P 9989 -u user userdb -p
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0
mysql -v -h localhost -P 9989 -u user userdb -p
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)
There are three issues here.
1 - Forget about the SSH tunnel for now
You cannot bind MySQL to more than one specific IP.
bind-address clause is overridden (therefore, ignored) by the second one. Your server only listens to
The reason why you can connect with
-h localhost but not with
-h 127.0.0.1 is that in the first form, you do not actually connect through TCP/IP, but through a local socket.
Look in your
my.cnf for a
Remove one redundant
bind-address clause. You may want to use
bind-address=0.0.0.0, which instructs MySQL daemon to listen to all network interfaces.
2 - Let's setup your SSH tunnel
The reason for you error
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0 is not obvious to me. I suspect SSH tunnel is actually established only when it receives a connection request (in your case, when you run the
mysql client). Since your server does not listen to 127.0.0.1 (see previous paragraph), the SSH tunnel cannot be established, connection fails, and your client interprets it as a network failure.
3 - Why
mysql -v -h localhost -P 9989 -u user userdb -p fails
Please post the output of
[edit : just added
...OR host LIKE 'localhost' below, as this might be relevant for troubleshooting purposes]
mysql > SELECT user, host FROM mysql.user WHERE user LIKE 'user' OR host LIKE 'localhost';
'user', after the
LIKE clause, with the actual user name if necessary)
MySQL access control checks both the username/password (
user) and the origin of the connection (
host) to identify a user. You probably did not create a user
N.B.: mysql.com being unreachable from my location at this time, I cannot link to the relevant manual pages.