Connecting to MySQL via SSH ERROR 2013 (HY000)

Question

The Setup

I am currently using the Premium Wordpress Hosting provided by MediaTemple. I have a very large data-set to import and I was hoping to get direct access to the database via an SSH tunnel.

---------------                      -------------------               ------------
| My Machine  | ---- SSH TUNNEL -----| Hosting Server  | -- -- ? -- -- | Database |
---------------                      -------------------               ------------

What Works

If I ssh into the Hosting Server and from the shell on the Hosting Provider, connect to mysql like this, I am able to get into MySQL.

mysql -uuser -ppassword -h123.456.789.1 -P3308

What Does Not Work

However, if I try to connect to MySQL using the -L flag with SSH to create a tunnel, I am unable to connect to the server.

 ssh me@hostingserver.net  -L 7002:123.456.789.1:3308

From a shell on My Machine:

 mysql -uuser -ppassword -h127.0.0.1 -P7002

I get the following error:

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

From reading other answers (StackOverflow , StackOverflow), I have reasoned that the issue stems from the IP address with which MySQL client tries to bind. I think that the ip address attach to the request to connect, when executed on my machine, is not on the white-list of the Database Server.

Is there anyway to get direct access to the MySQL Database from My Machine. From a system administration perspective, I obiviously have enough access to connect to the MySQL database from the shell but I cannot run the client on My Machine. I have a very large dataset that I would like to transfer from My Machine to Database. I would also like to be able access the database and exicute SQL whenever I need to. This and the large dataset kind of eliminates the possibility of just using a the source command from the MySQL Client on Hosting Server. What is the best workaround to give me something close to the ability to run SQL on the Database from My Machine?

Answer 1

I encountered roughly the same issue. That is, I simply could not connect to the MySQL server, even though I had successfully tunneled to the remote host.

TLDR: it was an iptables issue involving the loopback interface

In my situation, mysqld was running on the same VPS as sshd. However, the MySQL instance was bound only to 127.0.0.1 and listening on the default port. As you did, I confirmed that I could connect to the mysqld instance on the remote machine using the credentials used locally.

Here is the tunnel:

ssh -v -N -L 33306:127.0.0.1:3306 sshuser@sshanddbvps.org

Here is the connection string to the mysqld instance using the mysql client:

mysql -umysqluser -h127.0.0.1 -P 33306 -p

Even though ssh indicated that the connection was successful...

debug1: Connection to port 33306 forwarding to 127.0.0.1 port 3306 requested.

...the mysql client connection would error out after accepting the correct password with the message you mentioned:

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet'...

To check that data was flowing across the loopback interface, I logged into the remote server and ran three commands in three separate shells:

1. while true; do echo -e "HTTP/1.1 200 OK\n\n $(date)" | nc -l 127.0.0.1 1234; done
2. tcpdump -i lo src 127.0.0.1 -or dst 127.0.0.1
3. nc 127.0.0.1 1234

After running the third, output from the second command appeared:

13:59:14.474552 IP localhost.36146 > localhost.1234: Flags [S], seq 1149798272, win 43690, options [mss 65495,sackOK,TS val 48523264 ecr 0,nop,wscale 7], length 0

But nothing indicating that packets were flowing in the reverse direction.

Inserting a rule in the INPUT chain of the firewall that allowed traffic from the default loopback address solved the issue:

iptables -I INPUT 4 -i lo -s 127.0.0.1 -j ACCEPT